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 . |
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 |