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?
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")
"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.
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.
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: