Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2009
    Posts
    24

    Unanswered: Run .bat file from VBA

    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!

  2. #2
    Join Date
    Sep 2009
    Posts
    44
    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.)

  3. #3
    Join Date
    Oct 2009
    Posts
    24
    Awesome...thanks Scooby.

  4. #4
    Join Date
    Sep 2009
    Posts
    44
    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.

  5. #5
    Join Date
    Oct 2009
    Posts
    24
    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= " %%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
    Last edited by lbgto; 12-17-09 at 16:00.

  6. #6
    Join Date
    Sep 2009
    Posts
    44
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •