How to run macro from another spreadsheet and wait for execution?-CodePudding

I have a spreadsheet (let's name it Spr1) that I need to refresh periodically. I don't want to refresh it every time I open it, because it takes time.

I created another "launcher spreadsheet" (Spr2) to start Spr1 with macro in argument.

Spr2 is opening with:

Private Sub Workbook_Open()
Application.Visible = True
Application.Run "'\\path\Spr1.xlsm'!Refresh_data"
Workbooks("Spr1.xlsm").Close SaveChanges:=True
Application.Quit
End Sub

Macro in Spr1:

Sub Refresh_data()
ThisWorkbook.RefreshAll
End Sub

The first macro is not waiting for second one to finish the refresh. It is terminating Excel right after opening Spr1.

How can I wait to finish "Refresh_data"?

CodePudding user response：

It depends on type of default you have set.

Find your Query Properties, and set 'Enable background refresh' to False (Microsoft use True as default).

Then in your code you need to call for RefreshAll and wait for the data to load with the DoEvents. If you want your update of data to run on open you can use this in 'ThisWorkbook' Object.

Private Sub Workbook_Open()
For Each q In ThisWorkbook.Connections
q.Refresh
DoEvents
Next
End Sub

CodePudding user response：

If you like to launch your update from a VBScript you can call your macro without even se Excel doing the work in the background. Use Notepad and past this code in and save as MyStarter.vbs where you can start it with a double click.

Dim xlApp
Dim xlWkb
Dim MyParam 
MyParam = InputBox("Input your Parameter:","Enter parameter to the service")
if MyParam <> false then
Set xlApp = CreateObject("excel.application")
Set xlWkb = xlApp.Workbooks.Open("\\path\Spr1.xlsm",true,true)
xlApp.Run "Spr1.xlsm!Refresh_data", CStr(MyParam)
Set xlWkb = Nothing
Set xlApp = Nothing
end if

Then you can use your input parameter sent to the instance of your workbook like this :

Sub Refresh_data(MsgFromVBScript As String)
MsgBox ("This is your parameter from VBScript:" & MsgFromVBScript)
ThisWorkbook.RefreshAll
End Sub