Exporting to Excel, renaming sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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>
 
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
 
Couldn't you just do this?

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