TransferSpreadsheet fails after upgrade to Excel 2010

[schniggeldorf]

Hi:

I have some vba code that previously worked without difficulty. It determines which type of schedule a user wishes to print, then exports the data from the appropriate Access 2003 table into the appropriate Excel 2003 workbook.

However, our IT department just upgraded me to Excel 2010. I copied the Excel 2003 workbook to a Excel 2010 .xlsm workbook, changed the export path specification to reflect the change, and tried to run my code with the new version. However, whenever I attempt this, I now get a "external table is not in the expected format" error.

The relevant code follows. (I'm sorry if this isn't formatted correctly. I couldn't find the formatting directions for this site.) ScheduleExportPath is a constant defined in another module, but I checked to see that it was choosing the path correctly, and it was. PatientScheduleTemplateName and its cousins are likewise constants defined elsewere.

I assume there's some change I need to make in the TransferSpreadsheet statement to reflect the fact that I'm now transferring to Excel 2010 rather than 2003, but I can't figure out what it is.

Sub ExportToExcelSchedule(ScheduleType As String, ScheduleOwner As String, MaxWeeks As Integer)

Dim xlApp As Object
Dim xlWBk As Object
Dim wsParameters As Object
Dim wsSheet As Object
Dim wsWeek As Object
Dim wsFoundSheet As Object
Dim DataRange As Object

Dim i As Integer
Dim rowFirstTime As Integer
Dim rowLastTime As Integer
Dim rowLastData As Long

Dim myRange

Dim strWeek As String
Dim ScheduleTemplateNameConst As String
Dim tblToExport As String

Dim flg As Boolean


Set xlApp = CreateObject("Excel.Application")

'Select appropriate ScheduleTemplateNameConstant & tblToExport

Select Case ScheduleType
Case "Patient"
ScheduleTemplateNameConst = PatientScheduleTemplateName
tblToExport = "tblPatientPrintout"
Case "Provider"
ScheduleTemplateNameConst = ProviderScheduleTemplateName
tblToExport = "tblProviderPrintout"
Case "Equipment"
ScheduleTemplateNameConst = EquipmentScheduleTemplateName
tblToExport = "tblEquipmentPrintout"
Case "Room"
ScheduleTemplateNameConst = RoomScheduleTemplateName
tblToExport = "tblRoomPrintout"
End Select

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, tblToExport, ScheduleExportPath & ScheduleTemplateNameConst, True

Thanks for any help you can provide.

 

[Frothingslosh]

For .xlsx files, you need to replace acSpreadsheetTypeExcel9 with a simple 10. (acSpreadsheetTypeExcel9 refers to the Excel 2000 format)

I have been unable to find the value online that refers to the xlsm extension, unfortunately, so you might need to convert the worksheet to .xlsx.

In regards to formatting code here, all they said is in the sticky up above - there are CODE tags available that we should use.

 

[schniggeldorf]

Thank you for your suggestion. Unfortunately, when I attempted it, I received an error message stating that 10 was an invalid option within Accesss 2003.

 

[Frothingslosh]

DOH I'm sorry, for some reason I thought you had Access 2010 as well - it only works with 2007 and later.

I'm afraid your only option may be to do a save as and save it as a 97-2003 (.xls) format. You'll lose some functionality in the spreadsheet, though, if you've used any of the new features.

Hopefully someone more experienced can stop by and tell me I'm full of it!

 

[schniggeldorf]

I tried saving the excel file in .xls format, but that didn't work either.

Eventually, I found the solution elsewhere, so I'm writing this just in case somebody else ever has the same problem. In one of those wonderful "undocumented and irrational microsoft weirdness" moments, it turns out that Access could only export the table to excel 2010 if the name of the table was <=8 characters long. Because my original name was longer than that, it failed.

Weirdness #2: After changing the table name to something shorter, and find/replacing the table name in all my code, queries based on the table could no longer be successfully found by vba. I had to run each such query manually first, and afterwards they all ran OK. Very strange.