change worksheet name

[Felix Hyper]

I use Macro OutputTo to export query into Excel file.
I need Worksheet name in the file to be Sheet1, but
during export the worksheet name is the same as query
name.
Is it a way to change it?
P.S. I tried to change query name to Sheet1, and it
works, but I have several queries I need to use.
Thank you.

 

[John Nurick]

Hi Felix,

I suspect that this is not possible using macros. For better advice,
post your question in the microsoft.public.access.macros group.

It can be done if you use VBA. One way is to create a query called
Sheet1 and then have your code temporarily modify it so it exports the
data you need each time. Suppose you wanted to export the query
"ExportMe", you'd use code like this:

Dim dbD as DAO.Database

Set dbD = CurrentDB()
With dbD
.QueryDefs("Sheet1").SQL = .QueryDefs("ExportMe").SQL
End With
docmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, "Sheet1", _
"D:\Folder\Filename.xls"
Set dbD = Nothing

 

[Nikos Yannacopoulos]

Felix,

If you use TransferSpreadsheet instead of OutputTo, one of the arguments is
Range. If you specify something there that is not actually a range in the
target workbook, Access will create a worksheet with that name! Not sure it
was intended for this use, but it works pretty nice, both in macros and VB
code.

HTH,
Nikos

 

[Felix Hyper]

Thank you Nikos, it made the trick.

Felix.

-----Original Message-----
Felix,

If you use TransferSpreadsheet instead of OutputTo, one of the arguments is
Range. If you specify something there that is not actually a range in the
target workbook, Access will create a worksheet with that name! Not sure it
was intended for this use, but it works pretty nice, both in macros and VB
code.

HTH,
Nikos




.