Export Access Tables to Excel then save another sheet to csv

[torican]

Hi everyone.

Please help an access n00b.

I have created an access database to automate our trademe retail feed. (New Zealands alternative to ebay - retail feed updates our listings from a csv dump)

My database pulls data out of our inventory software and holds tables with auction/global data.

What I need to do is create a button in access to copy said tables to excel sheets (overwriting existing data), then save a separate sheet as CSV in the format of "retailfeed_DDMMYYYY_HHMM.csv.



I have tried and succeded in coping data to excel sheet using script like:

Application.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Shipping", "C:\temp\testBook1.xls", False, "Shipping"

But I am now stuck in how to save an output sheet to csv (after my workings).

I can't find/figure out how to do it from access. I have tried creating a macro to do it in excel but keeps giving me errors.

I have also tried it in reverse... Using the access button to run a macro in excel to do the import then save but then I just get errors the database is open.

Any help will be really appreciated. I have only spent a few days ripping my hair out in google!!!

[TheSmileyCoder]

First you must ran the export once manually, and save the export specification. Then you can use:

docmd.TransferText acExportDelim,"SpecName","myTableName","c:\Files\myfileName.csv"

Where specName is the name of the export specificaiton you just saved.