Solved

Writing a CSV file from Access using VBA code

Posted on 2007-03-21
6
354 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 125 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

733 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