Excel VBA: Wait Until a Process Completes

VBA provides us with a built-in method called Wait. We’ll use this method to wait until the process completes. The problem is the method Wait takes a fixed time as the argument and then makes the code wait for that fixed time. We’ll use a Timer inside our code. We’ll activate the Timer once our desired process starts being executed, and we’ll note the starting time. Once the process is completed, we’ll see the time associated with the Timer. Now we’ll start from this time to get the time that the process took to complete.

Use the VBA wait method to make the code for this time, to allow the process to finish completely.

The final structure of our VBA code will look something like this:

⧭ VBA Code:

Sub Wait_Until_a_Process_Completes()
Starting_Time = Timer
'Code of the Process
Total_Time = Timer - Starting_Time
Application.Wait (Total_Time)
End Sub

An Example to Wait Until a Process Completes in Excel VBA

⧭ VBA Code:

Sub Extract_Data_from_PDF()

Set MyWorksheet = ActiveWorkbook.Worksheets("Sheet1")
Application_Path = "C:\Program Files\Adobe\Acrobat DC\Acrobat\Acrobat.exe"
PDF_Path = "E:\ExcelDemy\standardnormaltable.pdf"
Shell_Path = Application_Path & " """ & PDF_Path & """"
Call Shell(pathname:=Shell_Path, windowstyle:=vbNormalFocus)

SendKeys "%vpc"
SendKeys "^a"
SendKeys "^c"

Range("A1").PasteSpecial Paste:=xlPasteAll

Call Shell("TaskKill /F /IM Acrobat.exe", vbHide)

End Sub

The code contains a number of processes within it.

It takes the necessary inputs and opens the PDF.
Sends some short keys to copy data from the PDF.
It pastes the copied data into our worksheet.
It kills the process.

Note that for the code to run successfully, each process has to be executed successfully first. We can’t afford to send the short keys until the PDF file opens completely.

Or we can’t afford to use the Paste method until all the data is copied successfully.

VBA tends to overlap processes. That is, it sometimes starts running a block of code before successfully finishing the previous block of code. As a result, codes involving complex processes often encounter unknown errors.

As with this example, if you run this code 10 times, it’ll show an error 2 to 3 times.

If you allow the code to wait for each of the processes to complete successfully, and then start the next process, you’ll never encounter an error.

Each time you run the code, it’ll run smoothly.

Here is the modified code. We’ve allocated a Timer with each process and made the code wait until they complete successfully.

⧭ VBA Code:

Sub Extract_Data_from_PDF()

Starting_Time = Timer
Set MyWorksheet = ActiveWorkbook.Worksheets("Sheet1")
Application_Path = "C:\Program Files\Adobe\Acrobat DC\Acrobat\Acrobat.exe"
PDF_Path = "E:\ExcelDemy\standardnormaltable.pdf"
Shell_Path = Application_Path & " """ & PDF_Path & """"
Call Shell(pathname:=Shell_Path, windowstyle:=vbNormalFocus)
Total_Time = Timer - Starting_Time
Application.Wait (Total_Time)

Starting_Time = Timer
SendKeys "%vpc"
SendKeys "^a"
SendKeys "^c"
Total_Time = Timer - Starting_Time
Application.Wait (Total_Time)

Starting_Time = Timer
Range("A1").PasteSpecial Paste:=xlPasteAll
Total_Time = Timer - Starting_Time
Application.Wait (Total_Time)

Starting_Time = Timer
Call Shell("TaskKill /F /IM Acrobat.exe", vbHide)
Total_Time = Timer - Starting_Time
Application.Wait (Total_Time)

End Sub

Run the code any number of times, it’ll never show an error. Each time you run it, it’ll be executed smoothly.

Things to Remember

Waiting is a good thing unless you use it unnecessarily. It’s only wise to use the VBA Wait method for codes with long complex processes. If it’s a small code with simple processes, no need to use it. It’ll only lengthen the running time, and as a result, will waste your time. So be a bit wise to use it.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Wait Until a Process Completes.xlsm

Get FREE Advanced Excel Exercises with Solutions!

Sub RunPythonScript() Dim exe As String, pth As String Dim fileToOpen As Variant Dim wsMaster As Worksheet Dim wbTextImport As Workbook Dim myDir As String Dim startTime As Doubleks: Dim endTime As Double Dim totalTime As Double Dim shell As Object Dim command As String myDir = "C:\Users\uie34719\OneDrive – Continental AG\Documents\CONTINENTAL FILES\PERSONAL FILES\REVIEWER-1\DEVNET\OTHERS\PROJECT\SHOWOFF" exe = "C:\Users\uie34719\AppData\Local\Programs\Python\Python39\python.exe" pth = "C:\Users\uie34719\OneDrive – Continental AG\Documents\CONTINENTAL FILES\PERSONAL FILES\REVIEWER-1\DEVNET\OTHERS\PROJECT\SHOWOFF\Main_Network_Script-WithCSV.py" startTime = Now Set shell = CreateObject("WScript.Shell") command = """" & exe & """ """ & pth & """" shell.Run command, 1, True ' The last parameter True makes the execution synchronous endTime = Now totalTime = (endTime - startTime) * 24 * 60 * 60 ' Convert time difference to seconds MsgBox totalTime & " seconds" ' Schedule OkGo to run after the total time Application.OnTime Now + TimeValue("0:00:" & Round(totalTime)), "OkGo", schedule:=True End Sub Sub OkGo() Dim fileToOpen As String Dim wsMaster As Worksheet Dim wbTextImport As Workbook Range("E2:H1048576").ClearContents fileToOpen = "C:\Users\uie34719\OneDrive – Continental AG\Documents\CONTINENTAL FILES\PERSONAL FILES\REVIEWER-1\DEVNET\OTHERS\PROJECT\SHOWOFF\1CSV_OUTPUT.csv" Workbooks.OpenText fileToOpen Set wbTextImport = ActiveWorkbook Set wsMaster = ThisWorkbook.Worksheets("Main_Template") wbTextImport.Worksheets(1).Range("A1").CurrentRegion.Copy wsMaster.Range("E2") wbTextImport.Close False End Sub

6 Comments

Jesse Bates Jun 28, 2022 at 12:24 AM

What declaration format do you use for the timer… The variables are undefined.
- Reply
  Mursalin Aug 30, 2022 at 12:27 PM
  
  Dear JESSE BATES,
  Thanks for your comment. Declaring variable types is not mandatory in VBA. VBA by default assigns the necessary data type to any variable, you just need to put the values. In the given code, the timer variable contains the data type Double.
  Please let us know if you have any other queries.
  Thanks!
Alberto Jan 20, 2023 at 5:46 PM

Thanks, I try to use here:
Starting_Time = Timer
ActiveWorkbook.RefreshAll
Total_Time = Timer – Starting_Time
Application.Wait (Total_Time)
…..other lines of code
but it doesn’t work. As Queries are updated in background, the Total_Time is not enough to end the process. And other lines of code are executed before ending the refresh process. Any idea?
I know that I can uncheck the flag where any query has “Enable background refresh”, but I’d like not to use it for many reasons.
- Reply
  Rafiul Haq Jan 24, 2023 at 11:49 AM
  
  Thank you, Alberto, for your question. I am replying on behalf of ExcelDemy. You can manually set the waiting time by using-
  ActiveWorkbook.RefreshAll
  Application.Wait (Now + TimeValue(“0:00:05”))
  …..other lines of code
  
  Here, it will wait for five seconds. You modify it to suit your needs.
Kitonski May 21, 2024 at 4:03 PM

I have this code. but the timer is not working

Sub RunPythonScript()

Starting_Time = Timer

Dim exe, pth
Dim fileToOpen As Variant
Dim wsMaster As Worksheet
Dim wbTextImport As Workbook
Dim myDir As String

myDir = “C:\Users\uie34719\OneDrive – Continental AG\Documents\CONTINENTAL FILES\PERSONAL FILES\REVIEWER-1\DEVNET\OTHERS\PROJECT\SHOWOFF”

exe = “C:\Users\uie34719\AppData\Local\Programs\Python\Python39\python.exe”
pth = “C:\Users\uie34719\OneDrive – Continental AG\Documents\CONTINENTAL FILES\PERSONAL FILES\REVIEWER-1\DEVNET\OTHERS\PROJECT\SHOWOFF\Main_Network_Script-WithCSV.py”

ChDir “C:\Users\uie34719\OneDrive – Continental AG\Documents\CONTINENTAL FILES\PERSONAL FILES\REVIEWER-1\DEVNET\OTHERS\PROJECT\SHOWOFF”

Shell “cmd.exe /c “”””” & exe & “”” “”” & pth & “”””””, vbNormalFocus

Total_Time = Timer – Starting_Time

MsgBox Total_Time

Application.OnTime (Total_Time), “OkGo”, schedule:=True

End Sub

Sub OkGo()

Range(“E2:H1048576”).ClearContents

fileToOpen = “C:\Users\uie34719\OneDrive – Continental AG\Documents\CONTINENTAL FILES\PERSONAL FILES\REVIEWER-1\DEVNET\OTHERS\PROJECT\SHOWOFF\1CSV_OUTPUT.csv”

Workbooks.OpenText fileToOpen

Set wbTextImport = ActiveWorkbook

Set wsMaster = ThisWorkbook.Worksheets(“Main_Template”)

wbTextImport.Worksheets(1).Range(“A1”).CurrentRegion.Copy wsMaster.Range(“E2”)

wbTextImport.Close False

End Sub
- Reply
  Shamima Sultana May 23, 2024 at 9:07 AM
  Hello Kitonski,
  
  Here, I made some fixes. Please try this updated code:
  Used the shell.Run command which is synchronous (True as the last parameter), ensuring the Python script completes before the VBA code proceeds.
  Using the Now function to calculate the start and end times accurately.
  To be safe used the Shell execution uses the correct quotation marks to avoid syntax errors.
```
Sub RunPythonScript()

    Dim exe As String, pth As String
    Dim fileToOpen As Variant
    Dim wsMaster As Worksheet
    Dim wbTextImport As Workbook
    Dim myDir As String
    Dim startTime As Doubleks:
    Dim endTime As Double
    Dim totalTime As Double
    Dim shell As Object
    Dim command As String

    myDir = "C:\Users\uie34719\OneDrive – Continental AG\Documents\CONTINENTAL FILES\PERSONAL FILES\REVIEWER-1\DEVNET\OTHERS\PROJECT\SHOWOFF"

    exe = "C:\Users\uie34719\AppData\Local\Programs\Python\Python39\python.exe"
    pth = "C:\Users\uie34719\OneDrive – Continental AG\Documents\CONTINENTAL FILES\PERSONAL FILES\REVIEWER-1\DEVNET\OTHERS\PROJECT\SHOWOFF\Main_Network_Script-WithCSV.py"

    startTime = Now

    Set shell = CreateObject("WScript.Shell")
    command = """" & exe & """ """ & pth & """"
    shell.Run command, 1, True ' The last parameter True makes the execution synchronous

    endTime = Now
    totalTime = (endTime - startTime) * 24 * 60 * 60 ' Convert time difference to seconds

    MsgBox totalTime & " seconds"

    ' Schedule OkGo to run after the total time
    Application.OnTime Now + TimeValue("0:00:" & Round(totalTime)), "OkGo", schedule:=True

End Sub

Sub OkGo()

    Dim fileToOpen As String
    Dim wsMaster As Worksheet
    Dim wbTextImport As Workbook

    Range("E2:H1048576").ClearContents

    fileToOpen = "C:\Users\uie34719\OneDrive – Continental AG\Documents\CONTINENTAL FILES\PERSONAL FILES\REVIEWER-1\DEVNET\OTHERS\PROJECT\SHOWOFF\1CSV_OUTPUT.csv"

    Workbooks.OpenText fileToOpen

    Set wbTextImport = ActiveWorkbook

    Set wsMaster = ThisWorkbook.Worksheets("Main_Template")

    wbTextImport.Worksheets(1).Range("A1").CurrentRegion.Copy wsMaster.Range("E2")

    wbTextImport.Close False

End Sub
```
  Regards
  ExcelDemy

Excel VBA: Wait Until a Process Completes

An Example to Wait Until a Process Completes in Excel VBA

Things to Remember

Related Articles

Leave a reply Cancel reply