change worksheet name

  • Thread starter Thread starter Felix Hyper
  • Start date Start date
F

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.
 
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
 
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
 
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




.
Click to expand...
 
Back
Top