Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    123

    Cool Unanswered: Writing data to a TXT file

    Since Access doesn't export into CSV format, I want to create a procedure to create a CSV file. I'm thinking of doing either of two ways:

    1. write to a text file and place a "," in between each column of data or
    2. export to Excel and manipulate Excel through VBA to save the file as CSV.

    For #1, I would simply be adding a row to the text file for each row of data by using a string like Column1 & "," & Column2 & "," & Column3. The question here is how do I open/create a text file and then keep adding lines to it?

    Or is #2 a better way to go?

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I am not sure why you say "Access does not export into CSV format", I do it almost everyday. It is just a variation of a text file.

    You will have to set up a File Speciication then use that file spec with the TransferText method of the DoCmd object.

  3. #3
    Join Date
    Aug 2003
    Posts
    123
    Right, but it doesn't create a .csv file which I can both load into another app and open automatically in Excel. But a .txt with commas is fine for now.

    The problem now is that using the File...Export wizard works fine, but trying to use TransferText and OutputTo doesn't work. Here's the TransferText attempt:

    DoCmd.TransferText acExportDelim, "FutrouteExportSpec", "qryXprt_Futroute", "C:\Documents and Settings\All Users\Desktop\Filename_" & Format(Date, "YYYYMMDD"), True

    Either I get "object is not found" or "object is read-only" errors. The file will have a different name each day since the current date is appended each time.

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    To export to a CSV just use a filename that specifies the extension as .csv. If you do that then Excel will know what to do with it. In your example change:

    "C:\Documents and Settings\All Users\Desktop\Filename_" & Format(Date, "YYYYMMDD")

    To

    "C:\Documents and Settings\All Users\Desktop\Filename_" & Format(Date, "YYYYMMDD") & ".csv"

    As far as the errors go, I typically troubleshoot errors by trying to create a macro to export the file. In some cases it is confusing which parameter to put where. And with the macro editor there is a little more information as well as combo boxes that help with typos.

  5. #5
    Join Date
    Aug 2003
    Posts
    123
    Found it!! I was using "True" instead of "Yes" for the Has Field Names argument. Duh!! Thanks all for your help.

  6. #6
    Join Date
    May 2004
    Posts
    159
    I have found that transfertext will insist on a .txt extension -any other will generate the errors you mention. It is easy however to put in a sub that will rename the .txt file to what ever you want. Use the fso method from the scripting libraries.

    Quote Originally Posted by jrn0074
    Right, but it doesn't create a .csv file which I can both load into another app and open automatically in Excel. But a .txt with commas is fine for now.

    The problem now is that using the File...Export wizard works fine, but trying to use TransferText and OutputTo doesn't work. Here's the TransferText attempt:

    DoCmd.TransferText acExportDelim, "FutrouteExportSpec", "qryXprt_Futroute", "C:\Documents and Settings\All Users\Desktop\Filename_" & Format(Date, "YYYYMMDD"), True

    Either I get "object is not found" or "object is read-only" errors. The file will have a different name each day since the current date is appended each time.

Posting Permissions

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