asked on

Writing a CSV file from Access using VBA code

Hi,
I have a table of data in Access which I use to extract information via a recordset and then I need to pass the information to a saved csv file. I have managed to extract the data, achieved the formats in the file required and create a file BUT its just a text file. I don't know how to save it as a csv file? I assume that the csv file needs a csv name extension? At the moment I have "testfile" no extension but I assume a csv file would be "testfile.csv" ? If I add .csv to the file name when it saves it opens in excel and not like a text file?

This is what I am using for the code.....

dim fso as filestream
dim txt as textstream
set fso = new filesystemobject
set txt = fso.createtextfile(strHoldingFolder & strFileName)

txt.writeline ".........all the data as required...."
txt.close
set fso = nothing

this works but I can see I am asking Access to write a text file not a csv file? Is there a simple solution here? I am not an IT programmer so apologies if I have asked something stupid!

Thanks
Dave

Arthur_Wood

A file extension of CSV is normally defaulted to open with Excel, when Office is installed on your PC. However, if you open such a file using Notepad, you will see that it is in fact a Comma Separated file, just as you have asked for.

AW

DaveMatthews76

ASKER

I need to send this file via ftp and the receiver expects to see a file named appropriately....such as "testfile.csv".....
if I use,
set txt = fso.createtextfile("testfile.csv") I don't see the file extension in the windows so I am worried its not named right? But it does say in the file details column "MS Office Excel Comma Separated Values File"........is that ok?

Thanks arthur

rockiroads

Creating a .csv file as u did should work
Possibly because u dont see the suffix is down to your windows explorer settings

in windows explorer, go to Tools/Folder Options click on the View tab then uncheck "Hide extensions for known filetypes"

Did u know u could always create a query that returns your data and run this
DoCmd.OutputTo acOutputQuery, "QueryName", acformatcsv, "c:\myfile.csv"

But then it probably doesnt keep the formatting u require

ASKER CERTIFIED SOLUTION

Arthur_Wood

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DaveMatthews76

ASKER

thanks guys! All resolved!

Arthur_Wood

glad to be of assistance.

AW