Run .bat file from VBA

lbgto · #1 (**permalink**) 12-16-09, 11:09

I'd like to run a batch file (below) after an export file is written out via the VBA code(txtexport_click). Or better yet, how can I turn the convert the batch file code into VBA code?

--------------------
Private Sub txtexport_Click()
Dim strPTH As String
Dim strA5AFNM As String

' Build strings
strPTH = "Q:\Downloads\"
strA5AFNM = "A5A_" & Format(Date, "mmddyyyy")
strA5AFNM = strA5AFNM & Format(Time, "hhmmss") & ".txt"

' Export commands
DoCmd.TransferText acExportFixed, "A5AExportSpec", "qryA5A_A51_A2A", strPTH & strA5AFNM, False

MsgBox "Export Completed. Check Q:\Downloads directory."

End Sub
-------------------

Batch file: code is taking a exported file (A5A_'datetimestamp'.txt), copying positions 29-43 and inserting three new lines prefixed with XX1, XX2, XX3.

@echo off > SCAORDER.DAT & setLocal enableDELAYedexpansion

for /f "tokens=* delims= " %%a in (A5A.txt) do (
>> SCAORDER.DAT echo %%a
set S=%%a
set S=!S:~29,14!
>> SCAORDER.DAT echo XX1!S!
>> SCAORDER.DAT echo XX2!S!
>> SCAORDER.DAT echo XX3!S!
)

Export file written out example:
A51SGXS6250008387916 EA00008Z1141093470108 YEEM ASNJ9BEEM050292ASMSAA 0002565
A51SGXF8455015312074 EA00020W90Z2Z93480171 W9046WCZ9V 9GF02999 SMSAA 0000055

Export file after batch file code example:
A51SGXS6250008387916 EA00008Z1141093470108 YEEM ASNJ9BEEM050292ASMSAA 0002565
XX1Z1141093470108
XX2Z1141093470108
XX3Z1141093470108
A51SGXF8455015312074 EA00020W90Z2Z93480171 W9046WCZ9V 9GF02999 SMSAA 0000055
XX1W90Z2Z93480171
XX2W90Z2Z93480171
XX3W90Z2Z93480171

Thanks for any help!

scooby_at_work · #2 (**permalink**) 12-17-09, 14:21

Quote:

Or better yet, how can I turn the convert the batch file code into VBA code?

That is a better option.

Wow, Windows batch files are even more horrific to read than bash.

Basically, it's stepping through the lines of A5A.txt, splitting on spaces, and assigning each token to %a. Yuck. Here's a reasonably straightforward translation:

Code:

    Dim infile As Integer, outfile As Integer, readline As String, tokens As Variant
    Dim token As Variant
    outfile = VBA.FreeFile
    'echo off > SCAORDER.DAT
    Open "SCAORDER.DAT" For Output As #outfile
    Print #outfile, "off"
    infile = VBA.FreeFile
    Open "A5A.txt" For Input As #infile
    Line Input #filenum, readline
    'for /f "tokens=* delims= " %%a in (A5A.txt) do (
    While Not VBA.EOF(infile)
        tokens = Split(readline, " ")
        For Each token In tokens
            '>> SCAORDER.DAT echo %%a
            Print #outfile, token
            'set S=%%a
            'set S=!S:~29,14!
            '>> SCAORDER.DAT echo XX1!S!
            Print #outfile, "XX1"; Mid$(token, 29, 14)
            '>> SCAORDER.DAT echo XX2!S!
            Print #outfile, "XX2"; Mid$(token, 29, 14)
            '>> SCAORDER.DAT echo XX3!S!
            Print #outfile, "XX3"; Mid$(token, 29, 14)
        Next token
    Wend
    Close #infile
    Close #outfile

(Oddly, VBA still uses the old old syntax for file I/O.)

lbgto · #3 (**permalink**) 12-17-09, 14:29

Awesome...thanks Scooby.

scooby_at_work · #4 (**permalink**) 12-17-09, 14:49

No problem. Split returns an array in a variant and I'm not positive For Each will work with that. If it doesn't, for i = 1 to ubound(tokens); token = tokens[i] would achieve the same thing.

The VB is a bit longer, but hopefully it's clearer what's going on. I'm fairly familiar with Windows batch files, but I've never seen putting the redirection before the command. But it works... you learn something new every day.

lbgto · #5 (**permalink**) 12-17-09, 14:54

It's failing at the 'Line Input #filenum, readline' with run-time error 52 bad filename or number. It is reading the correct A5A file (A5A_121720091250.txt)

Code:

Dim strA5Arelease As String, infile As Integer, outfile As Integer, readline As String, tokens As Variant
Dim token As Variant
outfile = VBA.FreeFile
strA5Arelease = strPTH & strA5AFNM
'echo off > SCAORDER.DAT
Open "SCAORDER.DAT" For Output As #outfile
Print #outfile, "off"
infile = VBA.FreeFile
Open strA5Arelease For Input As #infile
Line Input #filenum, readline
'for /f "tokens=* delims= " &#37;%a in (A5A.txt) do (
While Not VBA.EOF(infile)
    tokens = Split(readline, " ")
    For Each token In tokens
        '>> SCAORDER.DAT echo %%a
        Print #outfile, token
        'set S=%%a
        'set S=!S:~29,14!
        '>> SCAORDER.DAT echo XX1!S!
        Print #outfile, "XX1"; Mid$(token, 29, 14)
        '>> SCAORDER.DAT echo XX2!S!
        Print #outfile, "XX2"; Mid$(token, 29, 14)
        '>> SCAORDER.DAT echo XX3!S!
        Print #outfile, "XX3"; Mid$(token, 29, 14)
    Next token
Wend
Close #infile
Close #outfile

scooby_at_work · #6 (**permalink**) 12-18-09, 13:40

Should be infile, not filenum. It also helps to add Option Explicit at the top of any VB errors, as that can help catch errors like that.

Internet Services

Web Development

Digital Marketing

Consumer Tech