I have attempted this 2 different ways and cannot get either to work. I am currently on version 9.8.5, which I believe is the most up-to-date.
I have a simple macro on an xlsm file that I am trying to use to update my power queries:
Public Sub UpdatePowerQueries()
' Macro to update my Power Query script(s)
Dim lTest As Long, cn As WorkbookConnection
On Error Resume Next
For Each cn In ThisWorkbook.Connections
lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)
If Err.Number <> 0 Then
Err.Clear
Exit For
End If
If lTest > 0 Then cn.Refresh
Next cn
ActiveWorkbook.Save
End Sub
This works just fine within Excel, but I cannot get this to work with the Office Macro task. I can see the file gets opened in background processes and the file gets saved each time. I have tried to play with the settings, using local login credentials, run visible, both 32 and 64 bit, but no luck.
So, I wrote a VB script that executes this macro because that is what this was telling me to do:
Office Macro Task
Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Run "'C:\ROOT\DATA\VisualCron\Development\Hotel Revenue Recap\Hotel Revenue Recap.xlsm'!Module2.UpdatePowerQueries"
objExcel.DisplayAlerts = False
objExcel.Application.Quit
Set objExcel = Nothing
This also works by just running the script manually, but I cannot get it to work when using the Execute script task.
They both say success and the file gets autosaved each time (the last step of the macro), but when I open the file, none of the data is refreshed.
Anyone have any ideas?