Pass a string to a DOS batch file

[tmort`]

I am using shell to open a DOS batch file to run a program.

I can hard code the path to use in the batch file, but, I would prefer to get it from VBA which is getting the Activeworkook.path "\filename". That way the program can be installed differently in at different workstations.

Is there a way to do this?

Thanks

[dannymking]

You can also output a text based file with the .BAT extension in which you place the information you need..

What exactly are you wanting the batch to do??

[tmort`]

I have a set of Excel workbooks that I am using to perform a number of calculations with a wizard for data entry. Some of the data that needs to be entered comes from other sources including web links and other programs.

I can't just use the shell command alone because the other programs don't work properly that way. I have to first open the DOS emulator and then enter the command line argument to open the program I want.

I'd like to specify a dynamic path for the file to open rather than a static one.

So how do I go about creating and outputting a batch file?

Thanks

[dannymking]

You would open the file though File I/O (look up Write, Print or Line Input in vba help)

Dim FFile As Integer
  FFile = FreeFile
  Open "C:\MyBatch.Bat" For Output As #FFile
  Print #FFile, "D:\"
  Print #FFile, "CD MyFolder"
  Print #FFile, 'other dos commands
  Close #FFile
  'now shell out to the batch file

[tmort`]

Thanks. It took me a while to respond, by username had a typo and I had trouble logging back in.

That works great. I have another problem though. Since I am using this to create a batch file that will run an application wherever it is installed I am having problems. The structure of the batch file has to be different if the app is installed on a network drive or the c: drive.

If it's on the network drive the batch file would be:
[drive]:
appname.exe

If it's on the c: drive however, the command prompt from the shell command is at c:\...My Documents as that is the homedrive. The only way I can get it to work is to have a batch file that is:
cd c:\
apppath
appname.exe

I think maybe the answer lies in creating a path statement for appname.exe.

I can see how to do it manually, but don't know how to do it with code.

Does anyone have any ideas?

Thanks

[dannymking]

Your answers in your first post.. get excel to writeout the ActiveWorbook.Path followed by the application Name

[tmort`]

That works on a network drive but not if it's on the c:\ drive.

For some reason, even if I have the full file name and path, it doesn't work from the c:\...My Documents prompt.

I have to do cd: c:\
then I can enter the path

???

[dannymking]

Ok so then if it is not the c drive leave it as it is.. if it is the c drive then place the first line in to move to the C:\

If Left(ActiveWorkbook.Path,1) = "C" Then 
    'Print out the C Drive Version
  Else
    'print the other one
  End If
  Close #FFile

[tmort`]

Thanks, I've got it working for the c: drive and the network drives.

It gets curioser and curioser though as I threw a flash drive into the mix and it behaved differently in this situation???

You'd think the if not Left() = c routine would work on this as well, however, I'm not going to worry about it as I'm more concerned with how it behaves in normal installations.

[tmort`]

I think I spoke too soon.

I took it home and tried it on a couple of other computers with different profiles and had to be changed to work on thos which I suppose means it won't work on the orginal.

I stumbled on the setlocal command and thought I had the answer. I put together a batch file:

setlocal
path=pathtoapp; %path%
app.bat

and ran it and it worked.

Then I made my dynamic batch file the way you described to create the file (only at first I left off the %path%) and the dos prompt got stuck in a loop until I got a message saying that the setlocal recursive limit had been reached.

I put the %path% in and tried the original batch file and also tried app.exe at the DOS prompt and got the same results.

I didn't use endlocal so I ran a batch file that had just that command and tried again with no luck.

[dannymking]

Where exactly will the program that needs to be shelled out to be residing??

You could take it away from the batch file process and hardcode the same commands into the xls file..

Add the reference to "Microsoft Shell Controls And Automation" in the Tools, References in the VBE (Alt+F11)

Then

Dim obsh As Shell
  Dim strPathFile As String
  strPathFile = "set this to where the file is going to be placed through code"
  Set obsh = New Shell
  obsh.ShellExecute strPathFile, , , , True
  Set obsh = Nothing

[tmort`]

It will be residing in [activeworkbook.path] which could vary from installation to installation.

Tell me more please

[dannymking]

then change the code to

Dim obsh As Shell
  Dim strPathFile As String
  strPathFile = ActiveWorkbook.Path & "\appname.exe"
  Set obsh = New Shell
  obsh.ShellExecute strPathFile, , , , True
  Set obsh = Nothing

[tmort`]

That opens the program and it behaves the same as if I just select it. It opens but doesn't work properly.

If I go to the command prompt and manually navigate to the directory and then type the file it runs properly.

When opening the command prompt I see it likes to go the the ...\My Documents directory which is a user profile setting of some sort.

I was thinking that by usin the setlocal command in the batch file I could overide. I did sort of get that sorted out but I think as soon as the program starts the setlocal value is lost. I then think elsewhere in the program that I opened it is needed to still be there???

[tmort`]

Thanks,

That code worked great. I got some help from the group alt.msdos.batch.nt as to how to construct the batch file:

@ECHO OFF
pushd "f:\discharge request screener"
main.exe
popd

Anyway for anyone else wishing to open a program that behaves similarly to the ones I had, here is the code in the command button that creates the batch file and then calls it (the batch file creation code comes from help from a VBForums thread):

Private Sub cmbRREL_Click()

Dim FFile As Integer
Dim strpathmain As String
Dim strshortpath As String
Dim line1 As String
Dim line2 As String
Dim line3 As String
Dim line4 As String


Dim RetVal

strshortpath = ActiveWorkbook.Path
strpathmain = ActiveWorkbook.Path & "\treat.bat"

line1 = "@ECHO OFF"
line2 = "pushd " & strshortpath
line3 = "main.exe"
line4 = "popd"

FFile = FreeFile
Open strpathmain For Output As #FFile

Print #FFile, line1
Print #FFile, line2
Print #FFile, line3
Print #FFile, line4
Print #FFile, line5
Print #FFile, line6

Close #FFile


'shell out to the batch file


RetVal = Shell(Environ("comspec") & " /c " & Chr(34) & strpathmain & Chr(34), 1)


End Sub