How to use VBA to write a bat file and execute it?

[snoopies]

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]

There is a sample here: http://www.vbaexpress.com/forum/showthread.php?t=3470

[Bob Phillips]

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]

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]

VBS is another alternative.

Indeed.

[snoopies]

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?

Thanks a lot!

[Bob Phillips]

The syntax is

Shell(pathname[,windowstyle])

so you should use

Shell  "C:\XXX.bat", vbHide

[snoopies]

Thx for reply

if I use...

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

Anything's wrong..?

[Bob Phillips]

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]

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

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

[Bob Phillips]

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

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

Post the bat file code and let's see.

[snoopies]

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]

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]

still not working......

[Bob Phillips]

still not working......

In what way? It worked for me.

[snoopies]

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]

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]

YES, you are right!

It works properly now..

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