Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unanswered: Export table to Text file using VBA

    Hi, I am trying to export a table to a simple text file that looks like this using VBA code under a button.

    field1|field2|field3|field4
    Type something anything|Checked|1|Western lowland gorilla
    Type something anything|Checked|2|Greater flamingo
    Type something anything|Checked|3|Lesser Antillean iguana
    Type something anything|Checked|4|Trinidad stream frog

    I have used this code that I got from another thread

    DoCmd.TransferText acExportFixed, "text file", "database1", "c:\database.txt", True

    I set the the advanced options of a table export to create me a new import/export file specification called "text file" which set the field delimeter to | and text qualifier to None. This works great on a manual export but when I try and use the VBA code I get comma's seperating the field headers not "pipes" and no delimeters between the data just massive gaps.

    Please help this is driving me banana's!!!!!

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    If you use the export assitant, you can determine which separator do you want, and store this specification under any name, then use this specification's name in your statement

  3. #3
    Join Date
    Mar 2004
    Posts
    3
    Thanks for your reply - if you mean export assistant as in when you right hand click on a table and choose export then click advanced and create your own file specifications, thats how I created the "text file" export file spec which is referenced in the VBA code.

    Is there a way in VBA that you can write how you want the text to be formatted and seperated in the code.

    Thanks again!

  4. #4
    Join Date
    Mar 2004
    Location
    Atlanta, GA
    Posts
    7
    looks like your original option may be wrong... here's a list of the options for that:

    AcTextTransferType can be one of these AcTextTransferType constants.
    acExportDelim
    acExportFixed
    acExportHTML
    acExportMerge
    acImportDelim default
    acImportFixed
    acImportHTML
    acLinkDelim
    acLinkFixed
    acLinkHTML


    if you want to use a delimiter, you'll have to choose acExportDelim. acExportFixed sets up a fixed-width text file, regardless of delimiter settings you chose in your specifications file, hence the spaces between your fields...

    i'm guessing that if you checked, they'd correspond to the field widths in your table.

    for syntax of the rest of the arguments, search on "transfer text" in VBA.

    hope that helps!

  5. #5
    Join Date
    Mar 2004
    Posts
    3
    Thankyou - I have now regained my sanity!!!!

Posting Permissions

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