Results 1 to 13 of 13

Export Query as Batch File

  1. #1
    shank is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    86

    Export Query as Batch File

    I'm trying to export a query as a text batch file but getting the attached error. I assume the OS or Access is preventing it from happening because of it being a batch file. I can change the extension to ".txt" and it exports without issue.

    My end goal is generating a custom text string, export using the SavedExport into a batch file, then running the batch file. It will be manipulating files like merging, deleting, copying, etc. The string will constantly change, hence the need to create a batch file each time.



    Is there a way around this?

    Thanks!
    Attached Thumbnails Attached Thumbnails S1.jpg  

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,726
    Why not have your code export it as a Text file, and then rename it with a .bat extension?
    If it is currently a Macro, you can convert the Macro to VBA code (using the "Convert Macros to Visual Basic" functionality), and then add a line of code to it to rename it.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    shank is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    86
    I Googled Convert Macros to Visual Basic and I cannot find that option available in my MS Access 2007.

    Aside from that, I don't understand your suggestion to export as text, then rename. I can write the text file, then execute a batch file to rename that text file. But that seems a bit clumsy. If that's my only option, I guess it'll work. I was thinking it'd be more straight forward to export as the ".bat" file.

    Thanks!

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,726
    I Googled Convert Macros to Visual Basic and I cannot find that option available in my MS Access 2007.
    It has been around for ever. It is on the Macro menu/ribbon.

    How are you currently running this Export?
    If you create a Macro and use the command to run it there, then you can select this Macro and convert the code to Visual Basic, and make those changes I talked about.

    I can write the text file, then execute a batch file to rename that text file. But that seems a bit clumsy. If that's my only option, I guess it'll work. I was thinking it'd be more straight forward to export as the ".bat" file.
    You wouldn't have to do any of this manually. We can have VBA do it all with a simple click of a button.

    If you need help doing this, then try setting up the Macro like I said, converting to Visual Basic, and posting the code that it produced here.
    If you are not able to even get that far, please explain exactly how you currently run this, in detail.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  5. #5
    shank is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    86
    Ugh! Found it! Convert Macros to Visual Basic is grayed out. I opened a few other macros to see if it would become available. No joy. Googling...

    thanks!

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,726
    Usually, if you bring up the Macros object, select the Macro that you want to apply it to, then the button becomes "un-grayed" out.

    If it does not, can go into Design View of your Macro and copy/paste the image here so we can see how you filled it out (specifically, all the arguments for your actions)?
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  7. #7
    shank is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    86
    Success! I pasted below a test conversion with a test macro just to see what was actually happening. It roughly shows the present process.
    1) Export filenames into a text file. Resulting text file: pdftk "PL1 061219 B.pdf" "PL2 061219 B.pdf" cat output "PL Batch 061219 B.pdf"
    2) Rename that text file to a bat file
    3) Run that bat file
    4) Delete that bat file
    5) Done

    Re: DoCmd.RunSavedImportExport "Export CA PDF Filenames"
    I was hoping this would have been expanded into VBA so I could see how to actually make a text file and change it to making a bat file instead. This is my hang up. I could get rid of a lot of steps if I could use VBA to write to a bat file. Is there sample code that would replace this step where I can specify the filename being exported?

    Code:
    Function xTest()
    On Error GoTo xTest_Err
    
    
        ' frmCountFilesCA
        DoCmd.OpenForm "frmCountFilesCA", acNormal, "", "", , acNormal
        ' frmCountFilesFL
        DoCmd.OpenForm "frmCountFilesFL", acNormal, "", "", , acNormal
        If (Forms!frmCountFilesCA!FileCount > 0) Then
            ' Export CA PDF Filenames
            DoCmd.RunSavedImportExport "Export CA PDF Filenames"
        End If
        If (Forms!frmCountFilesCA!FileCount > 0) Then
            ' Delay
            Call Wait(1)
        End If
        If (Forms!frmCountFilesCA!FileCount > 0) Then
            ' S:\Orders\CA\RenameTextFile.bat
            Call Shell("""\\SVR1\Shared\Orders\CA\RenameTextFile.bat""", 1)
        End If
        If (Forms!frmCountFilesCA!FileCount > 0) Then
            ' S:\Orders\CA\CH_PackingList_Rename.bat
            Call Shell("""\\SVR1\Shared\Orders\CA\CH_PackingList_Rename.bat""", 1)
        End If
        If (Forms!frmCountFilesCA!FileCount > 0) Then
            ' S:\Orders\CA\DeleteTextFile.bat
            Call Shell("""\\SVR1\Shared\Orders\CA\DeleteTextFile.bat""", 1)
        End If
        If (Forms!frmCountFilesFL!FileCount > 0) Then
            ' Export FL PDF Filenames
            DoCmd.RunSavedImportExport "Export FL PDF Filenames"
        End If
        If (Forms!frmCountFilesFL!FileCount > 0) Then
            ' Delay
            Call Wait(1)
        End If
        If (Forms!frmCountFilesFL!FileCount > 0) Then
            ' "S:\Orders\Commerce Hub FL Oubound Orders\RenameTextFile.bat"
            Call Shell("""\\SVR1\Shared\Orders\Commerce Hub FL Oubound Orders\RenameTextFile.bat""", 1)
        End If
        If (Forms!frmCountFilesFL!FileCount > 0) Then
            ' "S:\Orders\Commerce Hub FL Oubound Orders\CH_PackingList_Rename.bat"
            Call Shell("""\\SVR1\Shared\Orders\Commerce Hub FL Oubound Orders\CH_PackingList_Rename.bat""", 1)
        End If
        If (Forms!frmCountFilesFL!FileCount > 0) Then
            ' "S:\Orders\Commerce Hub FL Oubound Orders\DeleteTextFile.bat"
            Call Shell("""\\SVR1\Shared\Orders\Commerce Hub FL Oubound Orders\DeleteTextFile.bat""", 1)
        End If
        If (Forms!frmCountFilesCA!FileCount > 0) Then
            ' frmCountFilesCA
            DoCmd.Close acForm, "frmCountFilesCA"
        End If
        If (Forms!frmCountFilesFL!FileCount > 0) Then
            ' frmCountFilesFL
            DoCmd.Close acForm, "frmCountFilesFL"
        End If
    
    
    
    
    xTest_Exit:
        Exit Function
    
    
    xTest_Err:
        MsgBox Error$
        Resume xTest_Exit
    
    
    End Function
    thanks!

  8. #8
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,726
    I think after this line:
    Code:
    DoCmd.RunSavedImportExport "Export CA PDF Filenames"
    you could add a line to rename the file at that point, something like:
    Code:
    Name "C:\File.txt" As "C:\File.bat"
    which renames the file with a "bat" extension (just change the file path and name to match the path/name you are exporting it to).
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  9. #9
    shank is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    86
    Very much appreciated! However, there's no way to write direct to a bat file through VBA, correct?

    It appears I'd still be doing the same basic steps:
    1) Write to text file
    2) Rename text file to bat
    3) Run bat
    4) Delete bat

    Thanks!

  10. #10
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,726
    I don't think therre is an option to export to a batch file. Is there a way to export to a text file, but use a "bat" extension? Possibly (I have never tried it), but I don't think it would be much easier than simply renaming the file with that one simple line of code I gave you.

    Its not like you are having to do it manually every time. Once you set it up in VBA, it is good to go.

    Note that if you are repeating the same steps/block of code in VBA many times, you can simplify things by creating a Procedure or User Defined Function that does performs that task, and simply call that every time that you want to use it (as opposed to repeating your code a bunch of times).

    For example, you could create a Procedure/Function that does steps 2-4 for you, just by feeding in the name of the file that gets created. Then you would just call that procedure each time, instead of having complete blocks of code that do all of that each time.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  11. #11
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,726
    So, the procedure I mentioned up above might look something like this:
    Code:
    Private Sub ExecuteFile(fname As String)
    '   Takes incoming file name and path and converts it to batch file, runs it, and deletes it
    
        Dim batName As String
        
    '   Build batch file name
        batName = Left(fname, InStrRev(fname, ".")) & "bat"
    '   Rename file to batch
        Name fname As batName
    '   Run batch file
        Call Shell(batName, 1)
    '   Delete batch file
        Kill batName
    
    End Sub
    And then in your "main code", every time you create/export a file, all you have to do is call this procedure and pass it the file name, i.e.
    Code:
        Call ExecuteFile(your_full_file_path_and_name)
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  12. #12
    shank is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    86
    Interesting! I will look this over. Very much appreciated!

  13. #13
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,726
    You are welcome.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Creating Batch File Running Access Query
    By tcheck in forum Access
    Replies: 1
    Last Post: 12-20-2012, 01:50 PM
  2. Replies: 4
    Last Post: 01-26-2012, 01:34 PM
  3. Batch export problem
    By OPOWELL in forum Import/Export Data
    Replies: 6
    Last Post: 05-23-2011, 12:48 PM
  4. Run batch file
    By shay in forum Access
    Replies: 3
    Last Post: 12-15-2010, 09:47 AM
  5. Running Batch File
    By NoiCe in forum Programming
    Replies: 2
    Last Post: 03-21-2010, 07:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums