Originally Posted by
Gicu
Try:
Code:
Dim appExcel As Excel.Application
Dim myWorkbook As Excel.Workbook
Set appExcel = CreateObject("Excel.Application")
appExcel.Visible = False
Set myWorkbook= appExcel.Workbooks.Open("C:\Users\xxxx\OneDrive\Documents\document_name.xlsx")
myWorkbook.UpdateLink
myWorkbook.Save
myWorkbook.Close
'next file
Set myWorkbook= appExcel.Workbooks.Open("C:\Users\xxxx\OneDrive\Documents\document_name2.xlsx")
myWorkbook.UpdateLink
myWorkbook.Save
myWorkbook.Close
'third file
Set myWorkbook= appExcel.Workbooks.Open("C:\Users\xxxx\OneDrive\Documents\document_name3.xlsx")
myWorkbook.UpdateLink
myWorkbook.Save
myWorkbook.Close
appExcel.Quit
Set myWorkbook = Nothing
Set appExcel = Nothing
Replaced
Code:
Dim appExcel As Excel.Application
Dim myWorkbook As Excel.Workbook
With
Code:
Dim appExcel As Object
Dim myWorkbook As Object
this gets rid of the type error.
Code:
myWorkbook.UpdateLink
does not do what I want but
Code:
myWorkbook.RefreshAll
works.
However, the next sequence creates a problem because it does not allow for the data refresh to complete and create a warning message in Excel which prevents the code from continuing.
Is there a way to either tell Excel to complete the previous command before running the next ones, Save, then Close?
Or, is there a way to put a timer between each command to allow them to complete before going to the next?
I tried
Code:
Application.OnTime Now() + TimeValue("0:00:10"), "myWorkbook.Save"
but I get a "method or data member not found" error, seems to be for Excel only.