put batch file command into VBA Excel

Question

Here is batch file code:

@echo off >summary.txt (
    for %%F in (*chkpackage.log) do findstr /l %1 "%%F" nul||echo %%  F:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A)

and here is the code in VBA Excel calling the batch file:

FileSet = Sheet1.Range("C13")
txtFpath = Sheet1.Range("C7").Value
FilePath = txtFpath & "\res.bat"

ChDrive "D"
RSP = Shell(Environ$("COMSPEC"), vbNormalFocus)
Application.Wait Now + TimeValue("00:00:03")
SendKeys "CD " & txtFpath & "{ENTER}", True
Application.Wait Now + TimeValue("00:00:03")
SendKeys "start " & FilePath & " " & FileSet & "{ENTER}", True
Application.Wait Now + TimeValue("00:00:03")
SendKeys "exit " & "{ENTER}", True
Application.Wait Now + TimeValue("00:00:03")
SendKeys "exit " & "{ENTER}", True

But I don't want to use a batch file. I want to change it into command to use on VBA. So i can use only VBA and run command line instead of using VBA to call batch and command line.

Easy explanation is I want to put that command in batch file into Excel-VBA and run it by using VBA call cmd and auto input that command to cmd like that Sendkeys code.

i have try but it's alway seem to be something wrong in my syntax i'm about to try your solution :) But I'm not good with programing so i have to take sometime thank you — eathapeking

Zev Spitz Zev Spitz · Accepted Answer · 2012-12-11T07:43:16

You can add a reference to the Microsoft Scripting Runtime (Tools -> References from the VBA IDE), which provides the FileSystemObject and allows you to do the following:

Dim fso As New FileSystemObject
Dim fle As Variant
For Each fle In fso.GetFolder(txtFpath).Files
    'processing here
Next

You can limit the files to a particular pattern using the Like operator:

For Each fle In fso.GetFolder(txtFpath).Files
    If fle.Name Like "*chkpackage.log" Then
        'processing here
    End If
Next

You can use the OpenAsTextStream method to get a TextStream object, and the ReadAll method to read the file contents:

For Each fle In fso.GetFolder(txtFpath).Files
    If fle.Name Like "*chkpackage.log" Then
        Dim txt As TextStream, contents As String
        Set txt = fle.OpenAsTextStream(ForReading)
        contents = txt.ReadAll
        txt.Close

        'process contents of file here
    End If
Next

You can use Split(contents, vbCrLf) to split the contents into an array of lines before parsing (use vbLf or vbCr if the line delimiter is Unix/Mac and not Windows).

Alternatively, you can use the ReadLine method to read the file line by line. You need to check the AtEndOfStream property to ensure that you're not trying to read past the end of the file:

'Within the For Each loop
Dim txt As TextStream, currentLine As String
Set txt = fle.OpenAsTextStream(ForReading)
Do While Not txt.AtEndOfStream
    currentLine = txt.ReadLine

    'process current line here
Loop
txt.Close

put batch file command into VBA Excel

1 Answers