How do I save an Excel file as comma delimited using Access Automation 2

[SBendBuckeye]

I have recently begun using Access as an automation server for Excel, so please bear with me if the questions are low level. I have opened up an Excel file using Access automation and have done some simple verification on the data in the spreadsheet.

I have two objects defined within my Access Excel object, xlWB As WorkBook and xlWS As WorkSheet. Once I have validated the Worksheet (always only 1 in current project) I would like to save it back out in the same folder with the same name except as a .CSV (comma delimited file). If I do it manually I click file Save As and then change the file format to CSV and click save.

How can I do that with code from Access? Do I need to pop a file browser and let the user save from it? I would rather just save it directly with no user intervention since every thing is already known.

Thanks in advance for any help or suggestions you can give me!

Have a great day!

 

[ETID]

I don't know the the exact sql statement ...but if you build a macro to export the table/query to a .csv file,...you can then convert the macro to a module to see the code.

 

[Nikita6003]

Hiya

If you've got the xl object already set up, just use the SaveAs option with filetype set to xlCSV:

xlWB.SaveAs FileName:= l_sFileName & ".csv", FileFormat:=xlCSV

HTH

Cheers
Nikki

 

[SBendBuckeye]

Thanks to both of you for your helpful suggestions! Have a great day!