Results 1 to 2 of 2

Thread: How to tell when shell script is done.

  1. #1
    Acolyte gsnidow's Avatar
    Join Date
    Aug 2011

    How to tell when shell script is done.

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.


  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Nanaimo, BC, Canada
    Blog Entries
    Excel Version
    Excel Office 365 Insider
    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
    bReady = True
    End If
    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?
    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: -||- Blog: -||- 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts