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
DaveMatthews76Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arthur_WoodCommented:
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
0
DaveMatthews76Author Commented:
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
0
rockiroadsCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Arthur_WoodCommented:
This line of code:

set txt = fso.createtextfile("testfile.csv")

will create a file with the specified name, including the .csv extension.  As rockiroads has pointed out, you may not be seeing the extension becuase of the settings on your instance of Wndows Explorer.

AWW
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DaveMatthews76Author Commented:
thanks guys! All resolved!
0
Arthur_WoodCommented:
glad to be of assistance.

AW
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.