Export Access data to CSV file

[aman1234]

Hi Guys

I have developed a project using Excel as a frontend and Access as a backend. Now I want to write code that will export data from
Access table to CSV file .
Can anybody please help me to do this?

Regards,

[snb]

You can load Access data directly into an Excel workbook.

[aman1234]

Thaks snb, I have done that so now how can I export the data which is stored in sheet1 to csv file using vba.

[Bmoe]

In the dropdown under the file name when you click save as... You can select csv and wouldn't need to do any vba.

[aman1234]

No actually I want to do it using vba. so that when the users select Date from and Date To and press Generate Report button then it should automatically save the data in sheet1 in CSV file in a different folder .

[aman1234]

I am writng the following code to export excel data to CSV file.

Please Login or Register  to view this content.

But when I go to the Export folder and try to open the file then it gives an error message:
"Excel has detected that Test1.csv is a SYLK file but cann't load it.Either the file has errors or its not a SYLK file.Click OK to try to open the file in a different format.

I hope anyone can figure it out.

Thanks

[Bmoe]

Ohh well then.

Please Login or Register  to view this content.

Also, this is dangerously close to John Walkenbach's book "Excel 2007 Power Programming with VBA" PG(845-846) So I will give him the credit.

[snb]

You'd better not use a function if nothing is being calculated.
Use

Please Login or Register  to view this content.

The problem is being caused bij the characters 'ID' in cell A1.
A preceding hyphen will solve that issue.

[aman1234]

Thanks snb for your help.

[aman1234]

Guys the code works fine but now there is one more problem, when it gets exported to CSV format then the data doesn't display
in systemetic order.For all rows the data appear in each column properly. If one column has no data for 1 row then 2nd columns
data shouldn't appear in 1st column space.

Is there any way to do something like we do in excel ws.usedrange.columns.autofit ?

Thanks

[snb]

Did you read your PM ?

[aman1234]

What is PM? Sorry I didn't get you.

I want all the data in CSV file in systemetic format so that one column data doesn't get mixed up with another column data.