Results 1 to 5 of 5
  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. Or 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
    Oct 2009
    Posts
    340
    probably better taken to a VB forum.... though someone may have an answer here....

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Simply use a Shell command from your VBA code:
    Code:
    Dim strBatchName as String
    
    strBatchName = "C:\SomeFile.bat"
    Shell strBatchName
    Have a nice day!

  4. #4
    Join Date
    Oct 2009
    Posts
    24
    Sinndho -

    When the batch file is executed in VBA it creates the new file, but it's empty(no records). If I run the batch file manually from DOS it writes out the file with records. Any ideas? Mayb access is running too fast and the bat file isn't done yet?
    Last edited by lbgto; 12-16-09 at 14:14.

  5. #5
    Join Date
    Oct 2009
    Posts
    24
    Issue resolved... I added a pause function.

Posting Permissions

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