Change tab name in export to excel

[mark1080p]

Hi,

I have a quick question

I have a list of reports that are saved in a shared location. However The person I report to, wants all the files to have the same tab name.

Please see below, I have trimmed the code to give you the jist of what i am doing...so for argument sake say I have a few queries built called Report 1, Report 2, Report 3 etc...

I have the following/similar code in all my access form buttons
Private Sub Command0_Click()
DoCmd.OutputTo acOutputQuery, "report1", acFormatXLS, "C:\Users\Mark\Access\report1.xls", False
DoCmd.OutputTo acOutputQuery, "report2", acFormatXLS, "C:\Users\Mark\Access\report2.xls", False
DoCmd.OutputTo acOutputQuery, "report3", acFormatXLS, "C:\Users\Mark\Access\report3.xls", False
End Sub

which will run the reports, but the tab name is also called "report1,2,3 respectively" same as the queries. Can anyone advise what vb code I can add to change the tab name to say "test" for example. so when I reun Report 1, 2, 3 etc all the tabs are called "Test"

I read that it can be done with transfer spreadsheet below, but still cant get the code work.

Private Sub Command1_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "report1", "C:\Users\Mark\Access\report1.xls", False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "report2", "C:\Users\Mark\Access\report2.xls", False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "report3", "C:\Users\Mark\Access\report3.xls", False
End Sub

I tried to add the following piece of code
sheets("report1")="test"
but to no avail.

I would change the query name, but I have 10 other similar vbcodes like above that I run, so cant name all the queries/tables with the same name.

Hope someone can help

 

[Norie]

It's not possible to do I'm afraid, unless you use some other method to export the data.

 

[alansidman]

Look at this write up on the subject

http://accessblog.net/2006/07/export-to-excel-range.html

I think you can make it work after reading this.

Alan

 

[Norie]

Alan

I thought you could do something like that but wasn't sure.

In fact when I think about it I definitely used that in the past.

 

[mark1080p]

Brilliant!

Many thanks Alan - It works a treat.

Only issue is to work out how to put data back to HH:MM:SS