Exporting to Excel, Set Sheetname

[Guest]

I used the following code to export an Access query to a formatted Excel
spreadsheet:

DoCmd.OutputTo acOutputQuery, stDocName, acFormatXLS, stFilename, False

It works fine except that the name of the spreadsheet is set to the name of
the query. Is there a way to set the name of the spreadsheet (not the
workbook, I've solved that problem)? I've also tried the TransferSpreadsheet
method and it also assigns the query or table name being exported as the
sheet name.

Any help would be appreciated.

 

[Guest]

Use the Range argument to specify the sheet name:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1",
"C:\Test.xls", , "SheetNew"

In this case, the table is called Table1, the Excel file is C:\Test.xls, and
the sheet name within the workbook is called "SheetNew".

 

[Guest]

Thanks Brian. That works, but when I use TransferSpreadSheet, I lose
formatting. Do you know how I can both keep the formatting and name the
SpreadSheet?

 

[Guest]

Sorry...I missed the part about "formatted". I do not believe there is any
way to retain formatting when exporting into a spreadsheet. However, with
just a little trickery, you can automate the entire process. There may be an
easier way, but here is what I have used:

1. Export your spreadsheet into Workbook1.
2. Shell out to Excel, opening Workbook2 that contains only an Auto_Open
macro that does the formatting for you in Workbook1, saves Workbook1, and
closes itself. (You can just record the macro in Workbook2 and save it as
Auto_Open.)

Private Sub SendToExcel()
Private OpenExcel As Variant
DoCmd.TransferSpreadsheet acExport, AcSpreadsheetTypeExcel9, "Table1",
"C:\Test.xls", , "SheetNew"
DataFile, 1
OpenExcel = Shell("Excel.exe " & "C:\FilePath\FileName", 0) 'the macro
workbook
End Sub

The only sticky part that I have not done as yet is to check to see if Excel
is open first. If not, and you close both workbooks, it will leave an Excel
window open with no workbook open, so you will probably want to close Excel.
If it is open when you start, you will want to leave it open when finished
because the user likely has another workbook open. I have seen posts on how
to do this, but have never incorporated it into my code.

In order for the Shell to work, you will need to ensure that the path to
"Excel.exe" is included in the PC's Path environment variable, or you must
specify the full path (C:\Program Files\...\OfficeWhatever\Excel.exe"), which
can be difficult if users have varying versions of Office, in which case
Excel may be in the ...Office10 or ...Office11 folder.