Solved

Writing a CSV file from Access using VBA code

Posted on 2007-03-21
6
344 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 125 total points
Comment Utility
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
Comment Utility
thanks guys! All resolved!
0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
glad to be of assistance.

AW
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now