Exporting to Excel, renaming sheet

[Guest]

Hello,

I know how to Export a Query using VBA with the following:

DoCmd.TransferSpreadsheet acExport, , strQuery, strFile, True

This works fine.

However after this creates the Excel File, it names the Sheet as the Query
Name.

I know one way is that I can just change the Query name, but I need the
Query name as 1 name & the Sheet as another.

How can I programatically change the Sheet name in Excel withou having to
open it up ?

Thank you,
Jeff

 

[Guest]

Sorry I was able to figure it out in case anyone needs...

Dim xls As Object
Dim wrkb As Object

Set xls = CreateObject("Excel.Application")
Set wrkb = xls.Workbooks.Open("C:\Path\NameOfExcelFile.xls")
wrkb.ActiveSheet.Name = "NewName"
wrkb.Save
xls.Workbooks.Close
xls.Quit
Set wrkb = Nothing
Set xls = Nothing

 

[Ken Snell \(MVP\)]

When you use ActiveSheet, you'll likely find that you will have an instance
of EXCEL still running in Task Manager when you finish. It's critical that
you always use full references with Automation to avoid this problem.

Assuming that you want to rename the first worksheet:

Dim xls As Object
Dim wrkb As Object

Set xls = CreateObject("Excel.Application")
Set wrkb = xls.Workbooks.Open("C:\Path\NameOfExcelFile.xls")
wrkb.Worksheeets(1).Name = "NewName"
wrkb.Save
xls.Workbooks.Close
xls.Quit
Set wrkb = Nothing
Set xls = Nothing

--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell \(MVP\)]

Additionally, always close and set to Nothing all child objects before you
close their parent object:

Dim xls As Object
Dim wrkb As Object

Set xls = CreateObject("Excel.Application")
Set wrkb = xls.Workbooks.Open("C:\Path\NameOfExcelFile.xls")
wrkb.Worksheeets(1).Name = "NewName"
wrkb.Save
wrkb.Close
Set wrkb = Nothing
xls.Quit
Set xls = Nothing

 

[Guest]

Couldn't you just do this?

DoCmd.TransferSpreadsheet acExport, 8, "Query", "FileName", False, "sheet"

 

[Ken Snell \(MVP\)]

Couldn't you just do this?

DoCmd.TransferSpreadsheet acExport, 8, "Query", "FileName", False, "sheet"

Maybe, depending on a number of things. See
http://alexdyb.blogspot.com/2006/07/export-to-excel-range.html