How to tell when shell script is done.

[gsnidow]

Greetings all. Excel 2007, XP Pro. In a macro, I have a WScript.Shell object which creates some spreadsheets on a network drive. I need to access the newly created spreadsheets after the VBScript is finished. The problem seems to be that the macro wants to proceed before the VBScript lets go of the spreadsheets. Rather than insert some kind of delay, is there a way for me to determine when the script shell is complete? The gist is like this...

Dim variables
...
Dim last variable

application.screenupdating = false

'Run VBScript to create three spreadsheets on a network drive.  
Set wshell = CreateObject("WScript.Shell")
wshell.Run ("wscript ""\\filepath\VBScript.vbs""")

'ADO recordset here to access the files created by the VBScript.

When the macro tries to access the first one, I get an error telling me it can't access the file. Within the VBScript, I am closing each spreadsheet as the last line of the script, so I know they are closing. I sure would appreciate any insight. Thanks.

Greg

[Ken Puls]

The issue is you've farmed it out to another program, which Excel loses control of.

You could try adding a Do Events line in, by testing something in your ADO code you expect will fail if the script hasn't completed, and that may help:

Do Until bReady = True
If sometest = False Then
Do Events
Else
bReady = True
End If
Loop

If you can't test anything in the ADO code, maybe you could port the vbs to VBA, or add a line to create a file at the end of the VBS, or some other event you can use to monitor if it has completed?