Exporting from Access to Excel, into a new worksheet

[jonwebb]

Hi,

I've had a look through the board but can't find the answer for this.

I'm using VB within Access to export a query to Excel, and then run an Excel Macro to format the data, add some totals, and rename the worksheet to be a date e.g. 12-11-04.

This will be run daily, and will upload a new report, which (theoretically) will have the current date as the sheet name, so that we will have one master workbook with many sheets, all for different days.

I can only get the VB to push the query into one worksheet. I've tried the two following methods with mixed results...

1) DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryMasterByDate", "C:\DAILY ELECTRONIC TBs MACRO(BY BSP).XLS"

This one creates a new workbook (if not existing) and does all the correct stuff. However, if the workbook is already there, it copies over the top of the existing worksheet and doesn't give it the name 'qryMasterByDate' which causes my Excel macro to go wrong during a rename.

2) DoCmd.OutputTo acOutputQuery, "qryMasterByDate", "MicrosoftExcel(*.xls)", "C:\DAILY ELECTRONIC TBs MACRO(BY BSP).XLS", True, ""

This one overwrites my workbook completely, which causes the Excel macro to disappear.

So in a nutshell, can I use one of these processes (or similar) to write into the spreadsheet, into a new worksheet, without overwriting anything currently existing.

Thank you!

Jon

 

[Norie]

What abourt renaming the query in the macro? This could be done but might be a bit tricky.

Is it a particularly complicated query?

If not you could create it at runtime with the name being the date you require.

BTW you do realise that all the formatting/totalling etc could be done from within Access rather than running the Excel macro.

It would be quite easy to change the existing code.

And if you did the formatting etc from Access you could also do the renaming etc which would avoid you having to rename the Access query.

 

[jonwebb]

Hi,

I've ended up solving it myself. Once it's copied into Excel, I run the macro which does all the formatting, instead of renaming, I copy it into a new sheet and delete the transferred one. It seems that the transferSpreadsheet command always puts it into logical 'sheet 1' on the spreadsheet, therefore copying my formatted one away means it won't get touched. Deleting the logical 'sheet1' means a new one is created next time.

Thanks for your help,
Jon