I am having trouble opening a .bat file in a VBA Excel Macro

Try this

Shell "c:\.......\yourfilename.txt", vbMaximizedFocus ' open a txt document

try this...

RetVal = Shell("cmd /C C:\MyMonitor\Monitor\startMonitorLinux.bat", 1)

I tried it in Execel VBA macro with my bat file. It works fine  finally
But it need to seperate cmd with bat file name by "cmd /c " & " C:\.......\example.bat   "
 
Sub OpenFile()

Dim RetVal
RetVal = Shell("cmd /c " & "c:\mydir\example.bat", 1)
 
End Sub

Hope you understand

I tried the my previouse one  it  also work too so now  you can use either one  

'MEthod-1
Sub OpenFile()

Dim RetVal
RetVal = Shell("cmd /c " & "C:\MyMonitor\Monitor\startMonitorLinux.bat", 1)

End Sub


'Method-2

Sub OpenFile()

Shell "C:\MyMonitor\Monitor\startMonitorLinux.bat", vbMaximizedFocus ' open a txt document

End Sub

If you want to open or edit your bat file in editor instead of   command running the bat file, you can
try this  and it works fine also.

Sub OpenFile()

Dim RetVal
RetVal = Shell("NOTEPAD.EXE" & "C:\MyMonitor\Monitor\startMonitorLinux.bat", 1)

End Sub

I have tried all of the above solutions and no luck. After futher research it looks like the batch files do run however the program that they are suppose to open doesn't open. See command window info attached

'\\us\home\d\joe.smith'
CMD.EXE was started with the above path as the current directory.
UNC paths are not supported.  Defaulting to Windows directory.

C:\WINDOWS>set JP=C:\CameronMonitor\Monitor\lib\fix

C:\WINDOWS>lib\jre\bin\java -mx256M -classpath "C:\CameronMonitor\Monitor\lib\fi
x\fixFilePersistence.jar;C:\Monitor\Monitor\lib\fix\fixCore.jar;C:\
Monitor\Monitor\lib\fix\fixMonitorGuiNew.jar;C:\Monitor\Monitor\lib\fix\
fixMonitoring.jar;C:\Monitor\Monitor\lib\fix\fixUniversalServer.jar;" com
.systems.fix.monitor.MonitorApplication -monitor //s0432cdc.cdc.schwab.co
m:3000/MonitorServer
The system cannot find the path specified.

C:\WINDOWS>pause
Press any key to continue . . .

Select allOpen in new window

Actually, your preivous question is passed for the running/opening bat file in VBA using shell ? NOw the new
question is in bat file script command inside  doesn't work and windows can't find the path  This is other
issue/queustion , RIght ? You should seperate the question, otherwise we get confused

I apologize for the confusion, the  vba code:

RetVal = Shell("cmd /C" & "C:\CameronMonitor\Monitor\startMonitorLinux.bat", 1)

returns the output that was attached I am not really sure if the batch is actually running. When I run C:\CameronMonitor\Monitor\startMonitorLinux.bat  from RUN it opens the program fine

Actually, it is running, I did it at my side there is no issue. Try to separate two question, You cann other  new bat file
with just one simple comand such as notepad.exe without other commands. IF notepad window is active, there  should be  no problem, VBA code with shell comand coding , but  the new problem might be  the java command in your orignal bat file. That is other new issue

Sorry Typing mistake for previous email


 To narrow down the proble, try to replace the orginal bat file with new bat file with just notepad.exe include
Actually, it is running, I did it at my side there is no issue. Try to separate two question, You can replace  other  new bat file
with just one simple comand such as notepad.exe without other commands. If notepad window is active, there  should be  no problem, VBA code with shell comand coding , but  the new problem might be  the java command in your orignal bat file. That is other new issue

when I run:

RetVal = Shell("cmd /C" & "notepad.exe", 1)

I get a similar message

'\\us\home\d\joe.smith'
CMD.EXE was started with the above path as the current directory.
UNC paths are not supported.  Defaulting to Windows directory.

I hope this is what you are asking?

Adding C:\ works

RetVal = Shell("cmd /C" & "C:\notepad.exe", 1)

Actually, you want to run bat file or open bat file in Shell compand ?

If running bat file ,using method-1. If opening bat file, using method-2 with notepad open file command  
'MEthod-1
Sub OpenFile()

Dim RetVal
RetVal = Shell("cmd /c " & "C:\MyMonitor\Monitor\startMonitorLinux.bat", 1)

End Sub


'Method-2

Sub OpenFile()

Shell "C:\MyMonitor\Monitor\startMonitorLinux.bat", vbMaximizedFocus ' open a txt document

End Sub

If there is no issue, the new issue it is  the commands inside your bat file not the shell  and VBD coding problem
Please clarify this before going  next step  

Sorry again, cut and paste mistake. Now, it is corret  Method-1, opeing bat file, method-2 is opeing bat file using notepad

MEthod-1
Sub OpenFile()

Dim RetVal
RetVal = Shell("cmd /c " & "C:\MyMonitor\Monitor\startMonitorLinux.bat", 1)

End Sub


'Method-2
Sub OpenFile()

Dim RetVal
RetVal = Shell("NOTEPAD.EXE" & "C:\MyMonitor\Monitor\startMonitorLinux.bat", 1)


End Sub

What I am not understanding is why the batch file opens correctly when double clicking on it or running it directly from Run but does open properly when opening it from the Shell? Doesn't that suggect that the shell is not opening the file the same way as simply double clicking on it?

Sorry Sorry again, cut and paste mistake and typing too fast . Now, it is corret  Method-1, running  bat file , method-2 is opening bat file using notepad

Method-1(running bat file and its command inside the bat file)
Sub OpenFile()

Dim RetVal
RetVal = Shell("cmd /c " & "C:\MyMonitor\Monitor\startMonitorLinux.bat", 1)

End Sub


'Method-2(opening bat file  by notepad editor)
Sub OpenFile()

Dim RetVal
RetVal = Shell("NOTEPAD.EXE" & "C:\MyMonitor\Monitor\startMonitorLinux.bat", 1)


End Sub

When  I run

RetVal = Shell("NOTEPAD.EXE" & "C:\MyMonitor\Monitor\startMonitorLinux.bat", 1)

it returns runtime error 53. file not found

it might be different version of VB/Excel issue  

Please try this

RetVal = Shell("NOTEPAD.EXE  C:\MyMonitor\Monitor\startMonitorLinux.bat", 1)

On Notepad it returns...

@echo On

set JP=C:\MyMonitor\Monitor\lib\fix

lib\jre\bin\java -mx256M -classpath "%JP%\fixFilePersistence.jar;%JP%\fixCore.jar;%JP%\fixMonitorGuiNew.jar;%JP%\fixMonitoring.jar;%JP%\fixUniversalServer.jar;" com.systems.fix.monitor.MonitorApplication -monitor //s0432cdc.cdc.schwab.com:3000/MonitorServer

pause
exit

What you mean ? Okay or not okay ?

It didn't start the program it just displayed the Notepad as seen in my previous comment. The only thing different it that it didn't pause for me to see the cmd box

Actually, you want editing the abt file or running file ?
At the begining of your question, you mention "I am having trouble opening a .bat file in a VBA Excel Macro"
Openign mean Editing, Right ?


And you say start the program, start what program ? please clarify, otherwise I don't see you problem

OK, I am trying to start a batch job that executes a series of instructions that result in running a program called MyMonitor. I do not want to edit the file.

No different that using a Shell to launch a .EXE file

I really apologize if I am not communicating clearly

So, you should use method-1,  using bat file to run program command inside  that is not related to Notepad.exe
Notepad.exe is just a editor only.


Method-1(running bat file and its command inside the bat file)
Sub OpenFile()

Dim RetVal
RetVal = Shell("cmd /c " & "C:\MyMonitor\Monitor\startMonitorLinux.bat", 1)

ANd you could send me your bat.file to check if you like  and the  error message from Method-1

End Sub

Here is the error message.

'\\us\home\d\joe.smith'
CMD.EXE was started with the above path as the current directory.
UNC paths are not supported.  Defaulting to Windows directory.

C:\WINDOWS>set JP=C:\MyMonitor\Monitor\lib\fix

C:\WINDOWS>lib\jre\bin\java -mx256M -classpath "C:\MyMonitor\Monitor\lib\fi
x\fixFilePersistence.jar;C:\MyMonitor\Monitor\lib\fix\fixCore.jar;C:\
MyMonitor\Monitor\lib\fix\fixMonitorGuiNew.jar;C:\MyMonitor\Monitor\lib\fix\
fixMonitoring.jar;C:\MyMonitor\Monitor\lib\fix\fixUniversalServer.jar;" com
.systems.fix.monitor.MonitorApplication -monitor //s0432cdc.cdc.comp.co
m:3000/MonitorServer
The system cannot find the path specified.

C:\WINDOWS>pause
Press any key to continue . . .

DId you set correct path in your directory to link and run your java program
lib\jre\bin\java that  dosen't seem the exact location

Step-1, Find out where is the location of your java program ,which directory
Step-2,  set path=C:\program files\java\jre\bin\;C:\otherdire; C:\other otherdir that is helping window to look for java
Step-3, change to java -mx256M -classpath "C:\MyMonitor\Monitor\lib\fi
x\fixFilePersistence.jar;C:\MyMonitor\Monitor\lib\fix\fixCore.jar;C:\
MyMonitor\Monitor\lib\fix\fixMonitorGuiNew.jar;C:\MyMonitor\Monitor\lib\fix\
fixMonitoring.jar;C:\MyMonitor\Monitor\lib\fix\fixUniversalServer.jar;" com
.systems.fix.monitor.MonitorApplication -monitor //s0432cdc.cdc.comp.co
m:3000/MonitorServer in bat file instead of lib\jre\bin\java mx256M -classpath "C:\MyMonitor\Monitor\lib\fi............

Or  you can change path in computer or document  system option/property, you will see how to change path variable

Just set path directory issue, windows could not find where is java ...

At command line C:
Just type as follows
C:\ set path

You will see all path you include in your system

You might just need to add a line to your bat file to change to the directory where the lib folder can be found...

 between the lines

Set JP= etc......  and
lib\jre\bin\java  etc.....

make it:

Set JP= etc......  
cd c:\MyMonitor\Monitor
lib\jre\bin\java  etc.....

I tried:
Set JP= etc......  
cd c:\MyMonitor\Monitor
lib\jreC:\CameronMonitor\Monitor\\bin\java  etc.....

But it doesn't change the directly:

'\\us\home\d\joe.smith
CMD.EXE was started with the above path as the current directory.
UNC paths are not supported.  Defaulting to Windows directory.

C:\WINDOWS>set JP=C:\MyMonitor\Monitor\lib\fix

C:\WINDOWS>cd C:\MyMonitor\Monitor\lib\jre\bin\java -mx256M -classpath "C:\
MyMonitor\Monitor\lib\fix\fixFilePersistence.jar;C:\MyMonitor\Monitor\
lib\fix\fixCore.jar;C:\MyMonitor\Monitor\lib\fix\fixMonitorGuiNew.jar;C:\
MyMonitor\Monitor\lib\fix\fixMonitoring.jar;C:\MyMonitor\Monitor\lib\fix
\fixUniversalServer.jar;" com.systems.fix.monitor.MonitorApplication -mon
itor //s0432cdc.cdc.comp.com:3000/MonitorServer
The system cannot find the path specified.

C:\WINDOWS>pause
Press any key to continue . . .


This is what the instructions look like when the bat file is just double clicked on:

C:\MyMonitor\Monitor>set JP=C:\MyMonitor\Monitor\lib\fix

C:\MyMonitor\Monitor>lib\jre\bin\java -mx256M -classpath "C:\MyMonitor
\Monitor\lib\fix\fixFilePersistence.jar;C:\MyMonitor\Monitor\lib\fix\fixCor
e.jar;C:\MyMonitor\Monitor\lib\fix\fixMonitorGuiNew.jar;C:\MyMonitor\M
onitor\lib\fix\fixMonitoring.jar;C:\MyMonitor\Monitor\lib\fix\fixUniversalS
erver.jar;" com.systems.fix.monitor.MonitorApplication -monitor //s0432cd
c.cdc.comp.com:3000/MonitorServer

Notice that:    

\\us\home\d\joe.smith
CMD.EXE was started with the above path as the current directory.
UNC paths are not supported.  Defaulting to Windows directory.

doesn't appear when the  file is just opened directly. Could that be where the problem is.

I got it.. I just used chdir.. instead of CD

set JP=C:\MyMonitor\Monitor\lib\fix

chdir..

C:\MyMonitor\Monitor\lib\jre\bin\java