0

I worte a VBA snippet that calls a macro in another workbook written by my collegue. That macro actually calls python to do something else and create a new workbook. After that I need to open that new workbook and do some other things. However, the python code takes some time to run and if I simply use

Application.Run "'CollegueWorkbook.xlsm'!pythonmacro"
Set wb = Workbooks.Open("NewWorkbook.xlsx")

I will get Run-time error '9': Subscript out of range beacause by the time Set wb = Workbooks.Open("NewWorkbook.xlsx") is executed, the python code has not created a new workbook yet I guess (it looks like VBA code won't wait for python thread)

I wonder how I can let VBA to sleep and continue to next line of code only when the new workbook is produced. Or if there is any other workarounds?

Pᴇʜ
  • 53,845
  • 9
  • 46
  • 68
Nicholas
  • 2,380
  • 2
  • 29
  • 54

3 Answers3

1

There are two ways to pause a script.

1) Application.Wait This is for continuing at a set time.

2) Sleep This one is for pausing for a set duration.

Quint
  • 462
  • 2
  • 7
  • 21
0

You are looking for Application.Wait This waits until a specific time, so if you want to wait for 10 seconds, use Application.Wait Now + TimeValue("0:00:10") You could think about a loop so you don't have to wait longer then neccessary: 

Dim retry As Long
Set wb = Nothing
For retry = 1 To 10
    Application.Wait Now + TimeValue("0:00:10")
    If Dir("NewWorkbook.xlsx") <> "" Then
        Set wb = Workbooks.Open("NewWorkbook.xlsx")
        Exit For
    End If
Next retry
If wb Is Nothing Then
    MsgBox "Didn't work..."
Else
    ...
End If
FunThomas
  • 16,008
  • 1
  • 15
  • 32
0

One way could be this. (not tested)

On Error Resume Next
Application.Run "'CollegueWorkbook.xlsm'!pythonmacro"
Do While wb Is Nothing
    Set wb = Workbooks.Open("NewWorkbook.xlsx")
Loop
Subodh Tiwari sktneer
  • 9,746
  • 2
  • 17
  • 22