Excel Export Tab Name

[jgelpi16]

I have the below "TransferSpreadsheet" command running. However, whenever it does run it names that tab "_CR2" instead of "CR2". Any idea why? I'm plum out. Thanks in advance!

DoCmd.TransferSpreadsheet acExport, , strTableName, strFileLoc & strFileName & strFileExt, True, "CR2"

[boblarson]

The part you have in there with "CR2" should not be there. That is the RANGE argument and the helpfile even states:

When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

You can rename the worksheet to what you want if you want to using this code:

Dim objXL As Object
Dim xlWB As Object
 
DoCmd.TransferSpreadsheet acExport, , strTableName, strFileLoc & strFileName & strFileExt, True
Set objXL = CreateObject("Excel.Application")
Set xlWB = objXL.Workbooks.Open(strFileLoc & strFilename & strFileExt)
 
With xlWB
.Worksheets(1).Name = "CR2"
.Save
.Close
End With
 
objXL.Quit
Set objXL = Nothing

[jgelpi16]

Interesting....I'll change my practices. I should mention that I have used this method a lot and it has never caused problems. Always naming my tabs as desired. Is it possible they added this feature with Access 2010? Thanks again for the information and advice.

[boblarson]

Interesting....I'll change my practices. I should mention that I have used this method a lot and it has never caused problems. Always naming my tabs as desired. Is it possible they added this feature with Access 2010? Thanks again for the information and advice.

It hasn't changed anything I know of. The tab name normally takes on the name of the table or query which is being exported.