Shell command and Wait

[Simon Goodman]

I am developing a macro, contained within an Excel workbook (call it WB1). Via an auto-open process, it starts another Excel workbook (WB2). The auto-open process of WB2 then runs. At the end, it writes a value to the Registry.
When completed, control returns to WB1. This reads the Registry value, and acts on that result.
However, I have some kind of timing issue, and somewhere in the process WB1 appears to be 'hanging', waiting for a value to be returned. I have time-out code, which is eventually executed.
If I place a MsgBox command immediately before checking the Registry value, the process completes correctly. As this is to be a process started by Task Scheduler via a batch job, this will not work in production.
I have tried all manner of sleeps, waits, etc. Nothing works apart from the MsgBox. So I'm looking for suggestions to either run the MsgBox virtually, with no real input required, or some way of running the Shell command in WB1, so that it waits for WB2 to complete before checking the Registry value for further action.

 

[shamimarita]

I am developing a macro, contained within an Excel workbook (call it WB1). Via an auto-open process, it starts another Excel workbook (WB2). The auto-open process of WB2 then runs. At the end, it writes a value to the Registry.
When completed, control returns to WB1. This reads the Registry value, and acts on that result.
However, I have some kind of timing issue, and somewhere in the process WB1 appears to be 'hanging', waiting for a value to be returned. I have time-out code, which is eventually executed.
If I place a MsgBox command immediately before checking the Registry value, the process completes correctly. As this is to be a process started by Task Scheduler via a batch job, this will not work in production.
I have tried all manner of sleeps, waits, etc. Nothing works apart from the MsgBox. So I'm looking for suggestions to either run the MsgBox virtually, with no real input required, or some way of running the Shell command in WB1, so that it waits for WB2 to complete before checking the Registry value for further action.

You are using the Shell function to start WB2 from WB1. But in VBA it starts a program asynchronously, which means it doesn't wait for the program (or in your case, the workbook) to finish executing before it continues with the next lines of code in WB1. It may be causing WB1 to read the registry before WB2 has written to it.

Please try this approaches to overcome the problem.

Use the WScript.Shell for Synchronous Execution
You can use the WScript.Shell object to run WB2 synchronously.

Ex:

Dim wsh As Object
Set wsh = CreateObject("WScript.Shell")

' The Run method on WScript.Shell allows setting the window style and waiting for completion
' 0 - Hides the window,
' 1 - Activates and displays the window
' True - It waits for the command to complete before moving on
Dim exitCode As Integer
exitCode = wsh.Run("excel.exe ""C:\Path\To\WB2.xlsx""", 0, True)
' Check exitCode if needed, then proceed to read the registry

It will ensure that the VB script waits for WB2 to finish before the next line of VBA in WB1 is executed.

Reading and Writing the Registry
Ensure your Registry read/write logic is robust. You might already have it correctly set up, but ensure you're handling any potential read before the write occurs properly.

Error Handling and Timeouts
In case there are errors or delays in WB2 that are not managed, ensure you have proper error handling in both WB1 and WB2. In WB1, especially, after you try to read from the Registry, make sure you have error handling to manage cases where the expected value might not be set yet:

Alternative to MsgBox: DoEvents
Sometimes, using MsgBox allows VBA to process all remaining events in the queue, which could be why your code works when it's included. You can use DoEvents to process events without showing a message box.
Replace MsgBox with DoEvents where you previously had used it, to see if it allows the application to process everything in the background.

 

[Simon Goodman]

Thank you! I'll try that tomorrow, when I'm back at work.

 

[Simon Goodman]

Can I ask? What is the reason for using CreateObject, rather than simply running the Shell command directly?
What is the wsh.Run command?
What is the expected value of exitCode?
Thank you very much for your assistance.

 

[shamimarita]

Can I ask? What is the reason for using CreateObject, rather than simply running the Shell command directly?
What is the wsh.Run command?
What is the expected value of exitCode?
Thank you very much for your assistance.

Why use CreateObject instead of Shell?: Imagine you're waiting for your friend to finish a task before you can start yours. The normal Shell command in VBA is like starting the task and then not waiting for your friend to finish then you just go ahead with your task. This can cause issues if you need something from your friend first. Using CreateObject to access WScript.Shell lets your code wait until your friend (in this case, the other workbook) is completely done before moving on.
What does wsh.Run do?: It's like giving a specific instruction to start an application or script and then choosing whether to wait around to see the result or not. For your scenario, you tell it to start the second Excel file and to wait there until everything's done before it reports back to you.
What’s with the exitCode?: This is a report card from the operation you just ran. If it says 0, everything went perfectly. If it gives another number, something went wrong, and you might need to look into it.
Here, 0 means no errors; any non-zero value indicates an error.

 

[Simon Goodman]

Again, thanks. I'll see if this sorts my process out.

 

[Simon Goodman]

I have tried this, and wsh.Run does not start WB2 at all.
If I use a straight Shell command, it works but asynchronously.
If I place a MsgBox after the Shell command, the process is effectively correct.
What might be going wrong?

 

[shamimarita]

I have tried this, and wsh.Run does not start WB2 at all.
If I use a straight Shell command, it works but asynchronously.
If I place a MsgBox after the Shell command, the process is effectively correct.
What might be going wrong?

If possible you can submit your sample code so that we can review it to find out the causes of not working.

 

[Simon Goodman]

Will review on Wednesday, thanks.