Multiple TransferSpreadsheet commands results in all created sheets being selected when opened-CodePudding

A bit hard to explain in words, but in this case the code and screenshot should really help. I've created this piece of simple code to reproduce the issue. This code is created in a VBA module in Access 2013.

It creates two equal excels with 4 sheets, one in acSpreadsheetTypeExcel9 / xls format, the other in acSpreadsheetTypeExcel12XML / xlsx.

Function TestExportExcel()

s = "\xxx\Desktop\temp\test.xls"

        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblPLCselect", s, , "sheet 1"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblPLCselect", s, , "sheet 2"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblPLCselect", s, , "sheet 3"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblPLCselect", s, , "sheet 4"

s = "\xxx\Desktop\temp\test.xlsx"

        DoCmd.TransferSpreadsheet acExport, , "tblPLCselect", s, , "sheet 1"
        DoCmd.TransferSpreadsheet acExport, , "tblPLCselect", s, , "sheet 2"
        DoCmd.TransferSpreadsheet acExport, , "tblPLCselect", s, , "sheet 3"
        DoCmd.TransferSpreadsheet acExport, , "tblPLCselect", s, , "sheet 4"

End Function

The functional issue here is that in the second (xlsx) export all sheets are selected, in contradiction to the xls version:

I want only the first sheet to be selected when users open the sheet, but I also want to export in acSpreadsheetTypeExcel12XML / xlsx format.

Is there an efficiënt way (this export has to run very often) to manipulate this?

CodePudding user response：

Bizarre! Yes, I get same behavior. Only way I can see to fix is to use Excel automation.

Dim objExcel As Object, wb As Object
Set objExcel = CreateObject("Excel.Application")
Set wb = objExcel.Workbooks.Open(s)
wb.Worksheets("sheet_2").Activate
wb.Worksheets("sheet_1").Activate
wb.Save
objExcel.Quit

Since you use a space in sheet names in your code, Excel will replace with underscore character.