VBScript for Excel "objWorkbook.RefreshAll" doesn't refresh data

2
votes

I have an Excel workbook that uses power query to retrieve data from another workbook. When I open the workbook manually, it updates automatically, but when I call it from a VBScript (that some of you helped me solve for a different workbook recently), it doesn't update.

I am using the same code that some of you helped me with the other day on a different workbook - but it doesn't work for this one. I have also tried putting autoexec macro into my workbook and also have tried some VBA code in the workbook that refreshes the data. These both work when i open the workbook manually, but neither updates the workbook when I open it from VBScript. I have also tried calling a macro from within the VBScript but that hasn't worked either. Here's my code:

Dim s
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = 
objExcel.Workbooks.Open("\\FilePath\Daily Snapshot.xlsm")

s = objExcel.ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")

objExcel.Application.Visible = True
objExcel.DisplayAlerts = False
objExcel.AlertBeforeOverwriting = False
objWorkbook.WorkSheets(1).Activate
objWorkbook.RefreshAll 

objExcel.ActiveWorkbook.Save
Do Until objExcel.ActiveWorkbook.BuiltinDocumentProperties("Last Save 
Time") > s
Loop
'objExcel.ActiveWorkbook.Close

I have also tried using DoEvents (which I was told previously that was not needed), but the strange thing is that when I include DoEvents - I get an VBScript error message about it, but when the error message pops up - then my data does refresh while the error message is onscreen, but then of course the rest of the script doesn't finish (which is the save and close parts)? This seems to me that maybe the code needs a break of some kind to allow it to run the refresh, but I understand that is the purpose of what is already there in the difference between "Last Save Time" and current save time. It seems to be failing on the RefreshAll part (not just because it doesn't refresh, but it also does not save my workbook which is the next part after the RefreshAll). However if I don't comment out the ActiveWorkbook.Close portion - it does close the workbook. Thank you for your help!

2
excelvbscriptrefresh

2 Answers

1
votes

This is what I figured out that had to be done in order for this to work properly. I had to go edit my Excel query (connection) properties - I deselected everything in Refresh Control area (deselect refresh on open and deselect enable background refresh and all others except for "Refresh this connection on Refresh All" - I left this one selected and my original code as I posted it previously now works as expected.

0
votes

Does the parameter updateLinks of the open command help?

Workbooks.Open Filename:="\\FilePath\Daily Snapshot.xlsm", updatelinks:=1

Or the Application property?

Application.UpdateLinks = True


'Variant 1:
Dim s
Set objExcel = CreateObject("Excel.Application")
objExcel.UpdateLinks = True
Set objWorkbook = 
objExcel.Workbooks.Open("\\FilePath\Daily Snapshot.xlsm")
..,


'Variant 2:
Dim s
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open FileName:="\\FilePath\Daily Snapshot.xlsm", UpdateLinks:=1

Set objWorkbook = objExcel.activeworkbook
...