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 wrote:

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

"Dave Peterson" wrote:

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

Dave Peterson wrote:

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

Eric wrote:

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" wrote:

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

Eric wrote:

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
.


--

Dave Peterson

--

Dave Peterson
.


--

Dave Peterson