How to code it on macro ?

[Eric]

Does anyone have any suggestions on how to code it on macro?
I would like to add mroe coding for this master.xls file
1) opening file1.xls
2) refreshing all from external links within file1.xls
3) close file1.xls
4) open file2.xls
5) running specific macro under this worksheet, which will be closed
automatically
6) close this master file

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

 

[Dave Peterson]

I'd start by recording a macro when I did it manually.

 

[Eric]

Do you have any suggestions on how to stop pop up any message for
confirmation? which will interrupt the rest of process.
Do you have any suggestions?
Thanks in advance for any suggestions
Eric

Public Sub Test_Menu_Item_Run()
Dim cmdBarItem As CommandBarButton

On Error Resume Next

Workbooks.Open Filename:="D:\Documents\file1.xls", UpdateLinks:=3
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
ActiveWindow.Close
\\ pop up a message here after closing file1.xls
Workbooks.Open Filename:="D:\Documents\file2.xls", _
UpdateLinks:=3
Application.Run "'Warrants Sorted Lists.xls'!UpdatingALL"
ActiveWorkbook.Save
ActiveWindow.Close

End Sub

 

[Dave Peterson]

Just looking at your code and I don't see anything that should be giving you a
confirmation message. If you were using .SaveAs, then I'd understand...

But I'd use something like:

Option Explicit
Public Sub Test_Menu_Item_Run()

Dim WkbkName As String
Dim wkbk As Workbook

WkbkName = "D:\documents\file1.xls"

Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks.Open(Filename:=WkbkName, UpdateLinks:=3)
On Error GoTo 0

If wkbk Is Nothing Then
MsgBox WkbkName & vbLf & "Could not be opened"
Exit Sub
End If

With wkbk
.RefreshAll
.Close savechanges:=True
End With

'get ready for the next time
WkbkName = "d:\documents\file2.xls"

Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks.Open(Filename:=WkbkName, UpdateLinks:=3)
On Error GoTo 0

If wkbk Is Nothing Then
MsgBox WkbkName & vbLf & "Could not be opened"
Exit Sub
End If

With wkbk
'if this UpdatingAll procedure is in the same workbook
'as this test_menu_item_run macro, then just use Call
Call UpdatingAll
'otherwise use

Application.Run "'Warrants Sorted Lists.xls'!UpdatingALL"

.Close savechanges:=True
End With

End Sub


And if you still get a warning message, try changing this line (both spots)
from:

.Close savechanges:=True

To:
application.displayalerts = false
.Close savechanges:=True
application.displayalerts = true

End Sub

ps. If those filenames can change and you want the user to select them, you can
use application.getopenfilename:

Option Explicit
Public Sub Test_Menu_Item_Run()

Dim WkbkName As Variant
Dim wkbk As Workbook

WkbkName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")
If WkbkName = False Then
'user hit cancel
Exit Sub
End If

Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks.Open(Filename:=WkbkName, UpdateLinks:=3)
On Error GoTo 0

If wkbk Is Nothing Then
MsgBox WkbkName & vbLf & "Could not be opened"
Exit Sub
End If

With wkbk
.RefreshAll

Application.DisplayAlerts = False
.Close savechanges:=True
Application.DisplayAlerts = True

End With

'get ready for the next time
WkbkName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")
If WkbkName = False Then
'user hit cancel
Exit Sub
End If

Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks.Open(Filename:=WkbkName, UpdateLinks:=3)
On Error GoTo 0

If wkbk Is Nothing Then
MsgBox WkbkName & vbLf & "Could not be opened"
Exit Sub
End If

With wkbk
'if this UpdatingAll procedure is in the same workbook
'as this test_menu_item_run macro, then just use Call
Call UpdatingAll
'otherwise use

Application.Run "'Warrants Sorted Lists.xls'!UpdatingALL"

Application.DisplayAlerts = False
.Close savechanges:=True
Application.DisplayAlerts = True
End With

End Sub

 

[Dave Peterson]

ps. I didn't test this, but it compile ok.

Just looking at your code and I don't see anything that should be giving you a
confirmation message. If you were using .SaveAs, then I'd understand...

But I'd use something like:

Option Explicit
Public Sub Test_Menu_Item_Run()

Dim WkbkName As String
Dim wkbk As Workbook

WkbkName = "D:\documents\file1.xls"

Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks.Open(Filename:=WkbkName, UpdateLinks:=3)
On Error GoTo 0

If wkbk Is Nothing Then
MsgBox WkbkName & vbLf & "Could not be opened"
Exit Sub
End If

With wkbk
.RefreshAll
.Close savechanges:=True
End With

'get ready for the next time
WkbkName = "d:\documents\file2.xls"

Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks.Open(Filename:=WkbkName, UpdateLinks:=3)
On Error GoTo 0

If wkbk Is Nothing Then
MsgBox WkbkName & vbLf & "Could not be opened"
Exit Sub
End If

With wkbk
'if this UpdatingAll procedure is in the same workbook
'as this test_menu_item_run macro, then just use Call
Call UpdatingAll
'otherwise use

Application.Run "'Warrants Sorted Lists.xls'!UpdatingALL"

.Close savechanges:=True
End With

End Sub

And if you still get a warning message, try changing this line (both spots)
from:

.Close savechanges:=True

To:
application.displayalerts = false
.Close savechanges:=True
application.displayalerts = true

End Sub

ps. If those filenames can change and you want the user to select them, you can
use application.getopenfilename:

Option Explicit
Public Sub Test_Menu_Item_Run()

Dim WkbkName As Variant
Dim wkbk As Workbook

WkbkName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")
If WkbkName = False Then
'user hit cancel
Exit Sub
End If

Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks.Open(Filename:=WkbkName, UpdateLinks:=3)
On Error GoTo 0

If wkbk Is Nothing Then
MsgBox WkbkName & vbLf & "Could not be opened"
Exit Sub
End If

With wkbk
.RefreshAll

Application.DisplayAlerts = False
.Close savechanges:=True
Application.DisplayAlerts = True

End With

'get ready for the next time
WkbkName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")
If WkbkName = False Then
'user hit cancel
Exit Sub
End If

Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks.Open(Filename:=WkbkName, UpdateLinks:=3)
On Error GoTo 0

If wkbk Is Nothing Then
MsgBox WkbkName & vbLf & "Could not be opened"
Exit Sub
End If

With wkbk
'if this UpdatingAll procedure is in the same workbook
'as this test_menu_item_run macro, then just use Call
Call UpdatingAll
'otherwise use

Application.Run "'Warrants Sorted Lists.xls'!UpdatingALL"

Application.DisplayAlerts = False
.Close savechanges:=True
Application.DisplayAlerts = True
End With

End Sub

 

[Dave Peterson]

....compileD...

 

[Eric]

The message is about "This action will cancel the update command ... "
Something like that.
Some worksheet will automatically update the link, once it is opened, will
it be the cause?
Do you have any suggestions?
Thanks in advance for any suggestions
Eric

ps. I didn't test this, but it compile ok.

 

[Dave Peterson]

Ahh, I think it's the .refreshall command that hasn't finished.

Is it a query that you're refreshing?

If yes, then right click on it and change the backgroundquery property to
false--so that excel will wait while the query refreshes.

You can do it in code with something like:
Activesheet.QueryTables(1).Refresh Backgroundquery:=False

(It can apply to pivottables, too.)

========
ps. Remove those .displayalert lines from the code.

The message is about "This action will cancel the update command ... "
Something like that.
Some worksheet will automatically update the link, once it is opened, will
it be the cause?
Do you have any suggestions?
Thanks in advance for any suggestions
Eric

 

[Eric]

Could you please tell me where I should add this statement?
Thank you very much for any suggestions
Eric

Option Explicit
Public Sub Test_Menu_Item_Run()

Dim WkbkName As String
Dim wkbk As Workbook

WkbkName = "D:\documents\file1.xls"

Set wkbk = Nothing
On Error Resume Next
Activesheet.QueryTables(1).Refresh Backgroundquery:=False // Should I
add it here?
Set wkbk = Workbooks.Open(Filename:=WkbkName, UpdateLinks:=3)
On Error GoTo 0

If wkbk Is Nothing Then
MsgBox WkbkName & vbLf & "Could not be opened"
Exit Sub
End If

With wkbk
.RefreshAll
.Close savechanges:=True
End With

'get ready for the next time
WkbkName = "d:\documents\file2.xls"

Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks.Open(Filename:=WkbkName, UpdateLinks:=3)
Activesheet.QueryTables(1).Refresh Backgroundquery:=False // Should I
add it here?
On Error GoTo 0

If wkbk Is Nothing Then
MsgBox WkbkName & vbLf & "Could not be opened"
Exit Sub
End If

With wkbk
'if this UpdatingAll procedure is in the same workbook
'as this test_menu_item_run macro, then just use Call
Call UpdatingAll
'otherwise use

Application.Run "'Warrants Sorted Lists.xls'!UpdatingALL"

.Close savechanges:=True
End With

End Sub

Ahh, I think it's the .refreshall command that hasn't finished.

Is it a query that you're refreshing?

If yes, then right click on it and change the backgroundquery property to
false--so that excel will wait while the query refreshes.

You can do it in code with something like:
Activesheet.QueryTables(1).Refresh Backgroundquery:=False

(It can apply to pivottables, too.)

========
ps. Remove those .displayalert lines from the code.

 

[Eric]

I think the problem is here, there are more than 10 sheets required for
refresh their links. Do you have any suggestions on how to stop processing on
close savechanges:=True command until all sheets are refreshed?
Thanks in advance for any suggestions
Eric

With wkbk
.RefreshAll
.Close savechanges:=True
End With

Option Explicit
Public Sub Test_Menu_Item_Run()

Dim WkbkName As String
Dim wkbk As Workbook

WkbkName = "D:\documents\file1.xls"

Set wkbk = Nothing
On Error Resume Next
Activesheet.QueryTables(1).Refresh Backgroundquery:=False // Should I
add it here?
Set wkbk = Workbooks.Open(Filename:=WkbkName, UpdateLinks:=3)
On Error GoTo 0

If wkbk Is Nothing Then
MsgBox WkbkName & vbLf & "Could not be opened"
Exit Sub
End If

With wkbk
.RefreshAll
.Close savechanges:=True
End With

'get ready for the next time
WkbkName = "d:\documents\file2.xls"

Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks.Open(Filename:=WkbkName, UpdateLinks:=3)
Activesheet.QueryTables(1).Refresh Backgroundquery:=False // Should I
add it here?
On Error GoTo 0

If wkbk Is Nothing Then
MsgBox WkbkName & vbLf & "Could not be opened"
Exit Sub
End If

With wkbk
'if this UpdatingAll procedure is in the same workbook
'as this test_menu_item_run macro, then just use Call
Call UpdatingAll
'otherwise use

Application.Run "'Warrants Sorted Lists.xls'!UpdatingALL"

.Close savechanges:=True
End With

End Sub

 

[Dave Peterson]

You could run a macro changing that setting and then you don't have to worry
about it again.

Option Explicit
Sub RunOnce()
'open your workbook First!!!
Dim wkbk As Workbook
Dim wks As Worksheet
Dim QT As QueryTable

Set wkbk = Workbooks("file1.xls")

For Each wks In wkbk.Worksheets
For Each QT In wks.QueryTables
QT.BackgroundQuery = False
Next QT
Next wks

wkbk.save

msgbox "Backgroundquery changed to false!"

End Sub

(Or you could do it manually, too)


...............


Or you could modify the code that does the work if you still want to be able
refresh those queries in the background:

Option Explicit
Public Sub Test_Menu_Item_Run()

Dim WkbkName As String
Dim wkbk As Workbook
Dim QT As QueryTable
Dim wks As Worksheet

WkbkName = "D:\documents\file1.xls"

Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks.Open(Filename:=WkbkName, UpdateLinks:=3)
On Error GoTo 0

If wkbk Is Nothing Then
MsgBox WkbkName & vbLf & "Could not be opened"
Exit Sub
End If

With wkbk
For Each wks In .Worksheets
For Each QT In wks.QueryTables
QT.Refresh BackgroundQuery:=False
Next QT
Next wks
'.RefreshAll
.Close savechanges:=True
End With

'get ready for the next time
WkbkName = "d:\documents\file2.xls"

Set wkbk = Nothing
On Error Resume Next
Set wkbk = Workbooks.Open(Filename:=WkbkName, UpdateLinks:=3)
On Error GoTo 0

If wkbk Is Nothing Then
MsgBox WkbkName & vbLf & "Could not be opened"
Exit Sub
End If

With wkbk
'if this UpdatingAll procedure is in the same workbook
'as this test_menu_item_run macro, then just use Call
Call UpdatingAll
'otherwise use

Application.Run "'Warrants Sorted Lists.xls'!UpdatingALL"

.Close savechanges:=True
End With

End Sub


And be careful with that last section of code. You should use either the
"Call UpdatingAll" line or the "application.run" line--but not both.

And depending on what that updatingall procedure does, you may need to change
that, too.