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ᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Nicholas
  • 2,560
  • 2
  • 31
  • 58

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
  • 530
  • 2
  • 8
  • 23
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
  • 23,043
  • 3
  • 18
  • 34
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,906
  • 2
  • 18
  • 22