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...
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.Code: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.
Greg
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:
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?Code:Do Until bReady = True If sometest = False Then Do Events Else bReady = True End If Loop
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
Bookmarks