VBA running batch file

[doubledaffy]

I'm trying to run a batch file from VBA and the batch file works fine when run independently. As a side note, the batch file uses FTP and gets a text file from a server which is later used by excel. The text file is "INVRPT"

Here is the code I have:

WBlocation = ThisWorkbook.FullName 'Generate file directory
WBname = ThisWorkbook.Name  'Current File Name
Truncnum = InStr(WBlocation, WBname) - 1 'Where to cut the directory

Dim BatchL As String
BatchL = Left(WBlocation, Truncnum) & "getrpt.bat" 'Batch file path location
Statusnum = Shell(BatchL)
DoEvents

I do not get any errors, and a command prompt does pop up shortly, however the text file does not get updated. When the batch file runs on its own, INVRPT gets updated, but through the shell command it does not. Any thoughts?

Thanks

 

[John_w]

The reason might be due to the way the Shell function works. From the VB help:

"By default, the Shell function runs other programs asynchronously. This means that a program started with Shell might not finish executing before the statements following the Shell function are executed."

In your case, Shell is running your batch file, but executing the following VBA statements immediately, without waiting for the batch file to complete.

Two possible solutions are:

1. Call a 'Shell and Wait' function which runs the Shell command but waits for it to finish before continuing. Google VBA Shell Wait for several examples.

2. Run the batch file with the Windows Script Host Run method, which has a WaitOnReturn argument - http://msdn.microsoft.com/en-us/library/d5fk67ky(v=VS.85).aspx. The code for this would be:

    Dim wsh As Object
    Set wsh = CreateObject("WScript.Shell")
    Statusnum = wsh.Run(Command:="%comspec% /c " & BatchL, WindowStyle:=5, waitonreturn:=True)

 

[doubledaffy]

Thanks for the reply John,

Looking through, it seems that I have to write my own Shell and Wait function, which can be pretty extensive.

Is there any reason I should use this over the WSH method?

Thanks

 

[John_w]

There's no need to write your own, as you could copy the 'Shell and Wait' code from the web, for example http://vb-tec.de/xshell.htm (the ShellX function).

The WSH method does exactly the same, and in only 3 statements, so I would try that. You also have greater control over the opened command window with the intWindowStyle parameter.

 

[Norie]

Have you tried copying what's in BatchL, opening a command window, pasting it there and running it?

You might have something wrong there and the command you've sent to the shell isn't quite right.

That might be why you see the window but nothing seems to happen.

Trying to run it manually might give you more idea what the problem is, if there is one.

 

[diddi]

also worth using Sleep api to insert delay between commands. has worked for me in the past

 

[doubledaffy]

Thanks for the replies, I'll give them a shot soon and see how they work.

 

[doubledaffy]

So ages later I finally got a chance to look at this problem again and I finally figured out what is wrong. The batch files run properly, however when the command prompt is called it goes to the default directory, while the batch files are in a different directory (that will be moving around). How can I make it so that the command prompt opens in or changes to whatever directory the batch files are going to be run from (which will be the same directory the excel sheet is in)?

I'll try to puzzle through it, but any more help you can provide would be great.

Thanks

 

[xenou]

You could edit the batch file to change directories as needed -- (you'd have to do this on the fly by (re)writing the batch file.

If it's not a complicated batch file (or even if it is) you could also write the whole batch file itself to the current directory when you execute your vba code in the workbook - writing text files isn't hard.

Alternatively, you can copy the workbook to where the batch file is, just as easily. This amounts to having a folder that doesn't move around - your batch file is there, and when you need to run the code, the workbook is put there too.

 

[doubledaffy]

Thanks for the reply xenou...I actually came to the same conclusion myself. VBA updates the batch file based on the location, so as long as I keep the two files in the same directory I'll be fine. I might make it more sophisticated and have VBA create the batch files so that if they do get separated later it won't be a problem.

Anyway thanks for all the help, and its all set. A simple shell function was all that ended up being needed.