Results 1 to 7 of 7
  1. #1
    sam.eade is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    29

    Exporting Multiple Queries to a single Text File

    Hi all,



    I'm looking to export three separate queries into a single text file.

    Currently I have three of them exporting separately (see code below) into the same folder. I was wondering if they can be put one line after another in a text file.

    Code:
    DoCmd.TransferText acExportFixed, "Xtrakter Export Header Export Specification", _    "Xtrakter Export Header", "W:\Markets & Investments\Sam's Folder\XtrakterHeader" & Format(Date, "DDMMYY") & ".txt", False
    
    
    DoCmd.TransferText acExportFixed, "Xtrakter Export Body Specification", _
        "Xtrakter Export Body1", "W:\Markets & Investments\Sam's Folder\XtrakterBody" & Format(Date, "DDMMYY") & ".txt", False
        
    DoCmd.TransferText acExportFixed, "Xtrakter Export Footer Specification", _
        "Xtrakter Export Footer", "W:\Markets & Investments\Sam's Folder\XtrakterFooter" & Format(Date, "DDMMYY") & ".txt", False
    '

    Any help would be appreciated.

    Many Thanks

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    You can export the first query using DoCmd but, I believe subsequent queries to the same file will require the use of the filesystemobject
    CreateObject("scripting.filesystemobject")

    Once you create a the filesystem object you would need to use the .opentextfile method using the full path to the text file.
    http://msdn.microsoft.com/en-us/libr...ffice.14).aspx

    From there you would probably have to Enumerate the existing lines of text (there may be a method to determine last line in text. I don't know) to create an index to then .Writline
    http://msdn.microsoft.com/en-us/libr...ffice.14).aspx

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I do just what you did..3 exports , then last step runs a shell command on a .BAT file (remember those?) that assembles all 3.
    x= shell("path\COPY3.BAT")


    (COPY3.BAT contents)
    copy "path\HEADER.TXT" + "path\BODY.TXT" + "path\footer.TXT" "path\FINAL.TXT"

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    That should do the trick. Much more eloquent.

  5. #5
    sam.eade is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    29
    Quote Originally Posted by ranman256 View Post
    I do just what you did..3 exports , then last step runs a shell command on a .BAT file (remember those?) that assembles all 3.
    x= shell("path\COPY3.BAT")


    (COPY3.BAT contents)
    copy "path\HEADER.TXT" + "path\BODY.TXT" + "path\footer.TXT" "path\FINAL.TXT"
    Hi RanMan, this sounds like the perfect solution. Is it as simple as pasting this code into the command underneath where I had the exports?

    It is showing a run time error and I'm a bit of a novice with VBA

    Thanks

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    You need to create a batch file with the following code

    copy "path\HEADER.TXT" + "path\BODY.TXT" + "path\footer.TXT" "path\FINAL.TXT"

    where path is the full path to the directory where the text files reside.

    Use notepad to create a new text file and paste your code for the batch file into your new text file. Save it. Then change the extension to .bat

  7. #7
    sam.eade is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Sep 2013
    Posts
    29
    Thankyou for all of your help everyone. Really helpful!

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

Similar Threads

  1. Replies: 28
    Last Post: 05-10-2013, 11:59 AM
  2. Replies: 1
    Last Post: 01-10-2012, 10:12 PM
  3. Replies: 5
    Last Post: 12-01-2011, 05:38 PM
  4. Replies: 7
    Last Post: 08-05-2011, 10:59 AM
  5. Exporting to Text File
    By blandow in forum Import/Export Data
    Replies: 2
    Last Post: 08-06-2010, 06:02 PM

Tags for this Thread

Posting Permissions

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