?
Solved

Writing a CSV file from Access using VBA code

Posted on 2007-03-21
6
Medium Priority
?
360 Views
Last Modified: 2013-11-27
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
0
Comment
Question by:DaveMatthews76
  • 3
  • 2
6 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 18763009
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
 

Author Comment

by:DaveMatthews76
ID: 18763050
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 18763143
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 500 total points
ID: 18763287
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
 

Author Comment

by:DaveMatthews76
ID: 18763376
thanks guys! All resolved!
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 18764838
glad to be of assistance.

AW
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question