Name an Excel Spreadsheet Tab

[Joe R.]

I'm using the TransferSpreadsheet method to create an
Excel spreadsheet. The name of the tab assigned for the
spreadsheet is the name of the query or table from which
it was created.

To manually designate a worksheet tab name, I can rename
the query and export the results to an Excel workbook. My
question is, is there a way using TransferSpreadsheet or
some other function/method to designate a tab name? I
know I can designate the file name.

If there is not a way to designate a tab name, would some
one provide the VB code to rename or copy a query and then
to delete the copied query?

Or if there a better way to approach this, I would
appreciate your input.

 

[Joe R.]

-----Original Message-----


Joe, once you have done the TransferSpreadsheet you could
open an instance of Excel from within Access VBA, open the
workbook you just transferred, and do the following:

Sheets("Sheet1").Name = "asdfasdfasd"

substituting the name of the created sheet for "Sheet1"
and whatever you want to call it for asdfasdf.

HTH,
.

------------
When I insert the code, substituting the actual name
for "Sheet1", I get a 'Sub or Function' not defined error
and the word Sheets is highlighted. Not knowing any other
way to open an Excel file from Access with VB, I used the
OutputTo function prior to inserting your code. Below
shows the code used:

DoCmd.OutputTo acQuery, "qryExcel", "MicrosoftExcel
(*.xls)", Me![cmbFielddir] & ".xls", True, ""
Sheets("qryExcel").Name = "test"

One note: in the actual code the query name had spaces;
e.g., "qry Excel Spreadsheet for Sites".

 

[Joe R.]

-----Original Message-----

-----Original Message-----


Joe, once you have done the TransferSpreadsheet you could
open an instance of Excel from within Access VBA, open the
workbook you just transferred, and do the following:

Sheets("Sheet1").Name = "asdfasdfasd"

substituting the name of the created sheet for "Sheet1"
and whatever you want to call it for asdfasdf.

HTH,
.

------------
When I insert the code, substituting the actual name
for "Sheet1", I get a 'Sub or Function' not defined error
and the word Sheets is highlighted. Not knowing any other
way to open an Excel file from Access with VB, I used the
OutputTo function prior to inserting your code. Below
shows the code used:

DoCmd.OutputTo acQuery, "qryExcel", "MicrosoftExcel
(*.xls)", Me![cmbFielddir] & ".xls", True, ""
Sheets("qryExcel").Name = "test"

One note: in the actual code the query name had spaces;
e.g., "qry Excel Spreadsheet for Sites".
.
Error is Run time error 1004

Method 'Sheets' of Global_Failed