Getting VB to wait until Shell finishes 1

[proteome]

How to I stop vb code from finishing before my shell script finishes.

I am trying to run a perl script from VBA and I think the the VB code finishes before the perl script finishes. Remedies???

 

[PHV]

Try something like this:
Set sh = CreateObject("WScript.Shell")
rc = sh.Run("Your Shell Command Here", 1, True)
The 3rd argument (True) is for waiting completion of the command.
rc will be the return code of the command.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[proteome]

Private Sub create_tab_Click()
Dim var As Variant
Dim path As String
Dim theFile As String

InputMsg = "Please specify the name of the file that you wish to create a .tab file for."
theFile = InputBox(InputMsg)
path = "c:\dropBox\dfile\" & theFile
MsgBox (path)
Set sh = CreateObject("WScript.Shell")
var = sh.Run("c:\perl\bin\perl.exe c:\perl\bin\test.pl path", 1, True)

I get a file test.tab created in My documents but the size is zero same problem as before.

 

[PHV]

Instead of this:
var = sh.Run("c:\perl\bin\perl.exe c:\perl\bin\test.pl path", 1, True)
Try this:
var = sh.Run("c:\perl\bin\perl.exe c:\perl\bin\test.pl " & path, 1, True)


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[proteome]

Thanks a lot works now with the & ,my bad, it is amazing how the littlest things can take hours to figure out

 

[deborahb]

I need to do the same thing. I am runnning Excel 2000.

My code looks like this:

Set sh = CreateObject("WScript.Shell")
rc = sh.Run("c:\""program files""\""fff systems""\runabout\runabout.exe -O Export -U dufflive -P somepass -D mydata -S Deborahb2 -DT 1 -T c:\voyager5.0.5\reports\CA_Files\Scripts\rs_ca_xml_get_new_tenant.xml -C -R c:\voyager5.0.5\reports\CA_Files\Output Files\xml_ca_get_new_tenant.xml -W True -A -WU

https://www.fffaspcn.com/fff/wydars/wdydars.asmx

-AW True -F ""<Params><SACode>DUFFERIN</SACode></Params>"" -L False", 1, True)

as you can see my shell command is long and complicated. I've been working on the quote marks and think that what I have is correct. This command works fine in a command box removing the first and last sets of qoutes and changing all the double double quotes to single double quotes.

The error I am getting back is:

Method 'Run' of Object 'IWshShell3' failed.

Any help would be most appreciated.

 

[PHV]

Just to be sure, try this and post back the result:[tt]
Set sh = CreateObject("WScript.Shell")
rc = sh.Run("%COMSPEC%", 1, True)[/tt]
This should open a console window you can leave by typing [tt]exit[/b]

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[deborahb]

In the interim, I wrote my long command into a bat file and changed the code to

rc = sh.Run(mybat, 1, True)

This turned out to work great.

Now I would like to suppress seeing the command window. I assume it is the second parameter. I am in the process of looking this up now.

Thanks for this code. I had been working with all sorts of other options that were not working and this was by far the simplest.

 

[PHV]

From my help file: intWindowStyle Description
0 Hides the window and activates another window.
1 Activates and displays a window. If the window is minimized or maximized, the system restores it to its original size and position. An application should specify this flag when displaying the window for the first time.
2 Activates the window and displays it as a minimized window.
3 Activates the window and displays it as a maximized window.
4 Displays a window in its most recent size and position. The active window remains active.
5 Activates the window and displays it in its current size and position.
6 Minimizes the specified window and activates the next top-level window in the Z order.
7 Displays the window as a minimized window. The active window remains active.
8 Displays the window in its current state. The active window remains active.
9 Activates and displays the window. If the window is minimized or maximized, the system restores it to its original size and position. An application should specify this flag when restoring a minimized window.
10 Sets the show-state based on the state of the program that started the application

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[deborahb]

Thanks again. You have been very helpful.