PDA

View Full Version : Solved: How to use VBA to write a bat file and execute it?



snoopies
07-17-2005, 05:00 AM
Hi all,

If I need to edit the bat.file oftenly, just wonder if it will be more convenient to write a marco helping this...

The contents of the bat.file are sentences like:
Call XXXX
Call YYYY

How to save it as a bat.file and execute it automatically?

Thanks in advance :)

xCav8r
07-17-2005, 05:30 AM
There is a sample here: http://www.vbaexpress.com/forum/showthread.php?t=3470

Bob Phillips
07-17-2005, 05:35 AM
Hi all,

If I need to edit the bat.file oftenly, just wonder if it will be more convenient to write a marco helping this...

The contents of the bat.file are sentences like:
Call XXXX
Call YYYY

How to save it as a bat.file and execute it automatically?

Thanks in advance :)

If you are going to execute the bat file automatically, why not just do the whole work in VBA? That is, forget the .bat file.

xCav8r
07-17-2005, 05:37 AM
If you are going to execute the bat file automatically, why not just do the whole work in VBA? That is, forget the .bat file.

VBS is another alternative.

Bob Phillips
07-17-2005, 06:11 AM
VBS is another alternative.

Indeed.

snoopies
07-17-2005, 11:24 PM
Hi all,

Please allow me to ask another stupid question..

I have read the article suugested above, it's really useful...Thank you:)

I have successfully created a bat file in excel.. but I can't invoke the batch file... e.g.. if the file name is XXX.bat , which is saved in C:\

Shell ("C:\XXX.bat"), vbHide '', vbMinimizedNoFocus <--- Is it Correct? It shows no response... but when I go to C drive and click XXX.bat manually...It works!

What's missing? :dunno

Thanks a lot!

Bob Phillips
07-18-2005, 02:03 AM
The syntax is

Shell(pathname[,windowstyle])

so you should use

Shell "C:\XXX.bat", vbHide

snoopies
07-18-2005, 06:02 AM
Thx for reply :)

if I use...

set exportpath="C:\XXX.bat"
Shell (exportpath), vbHide

Anything's wrong..?

Bob Phillips
07-18-2005, 06:22 AM
Thx for reply :)

if I use...

set exportpath="C:\XXX.bat"
Shell (exportpath), vbHide

Anything's wrong..?

exportpath = "C:\XXX.bat"
Shell exportpath, vbHide

snoopies
07-18-2005, 09:59 PM
exportpath = "C:\XXX.bat"
Shell exportpath, vbHide

It's still not working.... pls help..

Bob Phillips
07-19-2005, 02:28 AM
exportpath = "C:\XXX.bat"
Shell exportpath, vbHide

It's still not working.... pls help..

Post the bat file code and let's see.

snoopies
07-19-2005, 03:32 AM
okay..pls take a look of this...

Sub shell_bat()

Dim ExportPath As String
Dim TempFileName As String
Dim iFileNum As Long



' Create Batch Program

Set wsActive = ActiveSheet

With wsActive

ExportPath = "C:\studies\"
TempFileName = ExportPath & Trim(.Name) & ".bat"

iFileNum = FreeFile

Open sTempFileName For Append As #iFileNum

Print #iFileNum, "@Echo off"
Print #iFileNum, "CLS"
Print #iFileNum, "If exist dic.log del dic.log"
Print #iFileNum, "If exist err.log del err.log"
Print #iFileNum, "call dic.bat" & " " & Trim(.Name) & " " & "dic32" & " " & "c" & " " & "studies" & " " & "c" _& " " & "studies"

End With


Close #iFileNum

'Invoke Batch Program"

Shell sTempFileName, vbHide


End sub

Bob Phillips
07-19-2005, 04:08 AM
Snoopies,

SET OPTION EXPLICIT IN YOUR CODE, IT WILL SAVE YOU PROBLEMS.


Sub shell_bat()
Dim ExportPath As String
Dim iFileNum As Long
Dim wsActive As Worksheet
Dim sTempFileName As String

' Create Batch Program
Set wsActive = ActiveSheet

With wsActive
ExportPath = "C:\"
sTempFileName = ExportPath & Trim(.Name) & ".bat"
iFileNum = FreeFile
Open sTempFileName For Output As #iFileNum
Print #iFileNum, "@Echo off"
Print #iFileNum, "CLS"
Print #iFileNum, "If exist dic.log del dic.log"
Print #iFileNum, "If exist err.log del err.log"
Print #iFileNum, "call dic.bat" & " " & Trim(.Name) & " " & "dic32" & " " & "c" & " " & "studies" & " " & "c" _
& " " & "studies"
End With

Close #iFileNum

'Invoke Batch Program"
Shell sTempFileName, vbHide

End Sub

snoopies
07-19-2005, 07:38 PM
still not working......

Bob Phillips
07-20-2005, 03:23 AM
still not working......

In what way? It worked for me.

snoopies
07-20-2005, 09:53 AM
hello, my problem..

I can create a new bat file called XXX.bat sucessfully, being put in c:\studies
however, if it works properly, if I invoke XXX.bat, it will call another bat file called dic.bat.. that's my problem.. dic.bat can not be invoked automatically...
But when I open folder c:\studies, find XXX.bat, click and execute it, the dic.dat can work properly.. It's very strange..

JKwan
07-20-2005, 10:25 AM
Here try this

With wsActive
ExportPath = "C:\"
sTempFileName = ExportPath & Trim(.Name) & ".bat"
iFileNum = FreeFile
Open sTempFileName For Output As #iFileNum
Print #iFileNum, "@Echo off"
Print #iFileNum, "CLS"
Print #iFileNum, "c:"
Print #iFileNum, "cd\"
Print #iFileNum, "If exist dic.log del dic.log"
Print #iFileNum, "If exist err.log del err.log"
Print #iFileNum, "call dic.bat" & " " & Trim(.Name) & " " & "dic32" & " " & "c" & " " & "studies" & " " & "c" _
& " " & "studies"
End With



The reason it is not executing.... Your current directory is not set. I am forcing your current directory to be root of C, by adding to your batch file (change to suit your needs).

snoopies
07-20-2005, 10:47 PM
YES, you are right!

It works properly now..

Thank you so much for your help!
Thanks for teaching this..:)