VBA - Controlling the Shell function
Posted on 2000-02-25
I use VBA code in an Excel workbook to pull in data from unix boxes, which is then reformated/recalculated and emailed to various people.
To pull the data in I use the shell function to run a Perl script, which basically runs an ftp.
My problem is that because the shell function runs asynchronously I need some way of telling the Perl script has completed before allowing my code to continue.
I have been using a work around using a msgbox to halt the code, but I'm sure there must be a better way.
He's an excerpt from my code as an example of what I'm doing.
day_num = Sheets("WORK").Range("B1")
Dim RetVal, Style, Response
RetVal = Shell("J:\autorec\perl.exe J:\autorec\myperlscript.pl", 1)
Msg = "Was File Transfer Successful ?"
Style = vbYesNo + vbQuestion
Response = MsgBox(Msg, Style)
If Response = vbNo Then
Sheets("DAY" & day_num).Select
What I would like is the code to run the shell script and then wait until it completes before carrying on, without having the user to intervene in some way.