Renaming Excel Sheet names from Access

[GovtWerker]

Hello,

I'm running a few queries and then exporting them to an Excel Spreadsheet. The spreadsheet Tab are named: zPMI271_A_Detail_BSykes, zPMI271_A_Fault_BSykes, zPMI271_A_Total_BSykes

I'd like to have the Tabs name: Detail, Fault and Total.
I was sucessful getting the first one to be named Detail, but the other two I'm not so lucky.

Any ideas on how to rename workbooks (2) and (3)?




Here's what I have:
MyQry = "zPMI271_A_Detail_BSykes"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, MyQry, MyDir & MyFile, -1
MyQry = "zPMI271_A_Fault_BSykes"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, MyQry, MyDir & MyFile, -1
MyQry = "zPMI271_A_Total_BSykes"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, MyQry, MyDir & MyFile, -1
'Now setting MyQry to the second query
'Autosize the spreadsheet
'The code below automatically sizes the spreadsheet columns to match the data contain within (AutoFit)
Workbooks.Open FileName:=MyDir & MyFile
Worksheets(1).Columns.AutoFit 'Autofit the first sheet
Worksheets(2).Columns.AutoFit
Worksheets(3).Columns.AutoFit
Worksheets(4).Columns.AutoFit
' I want to change the Tab Names
Workbooks(1).ActiveSheet.Name = "Detail" ' This works fine


Thanks,
GovtWerker

 

[Trevor G]

Do you want the sheet names to change rather than workbooks?

Sheets(1).Name="Detail"
Sheets(2).Name="Fault"
Sheets(3).Name="Total"

 

[GovtWerker]

Thank you so much, it worked on the first try!

 

[Trevor G]

Happy to help and thanks for letting me know this has provided you with a solution.