Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code it on macro ?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code it on macro ?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code it on macro ?
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code it on macro ?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code it on macro ?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code it on macro ?
....compileD...
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code it on macro ?
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 . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code it on macro ?
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code it on macro ?
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 "Dave Peterson" wrote: 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 . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code it on macro ?
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 "Eric" wrote: 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 "Dave Peterson" wrote: 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 . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code it on macro ?
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. Eric wrote: 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 "Dave Peterson" wrote: 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 . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro code to dll and vba code trouble shoot. | Excel Programming | |||
Slow code when used as VBA code instead of macro (copying visible columns) | Excel Programming | |||
Can I use code/macro to change code/macro in an existing file? | Excel Programming | |||
read macro code by vb code | Excel Programming | |||
do anybody have a sample code for executing excel macro from vb code?<eom | Excel Programming |