Error invoking Excel from Access 2013

Topic

New Reply

I’m not sure that this is an Excel issue but I’m starting here because the problem and resolution are in Excel.

I create a new spreadsheet from Access using the following code:
Set bks = objExcel.Workbooks
‘Open the workbook
bks.Add strExcelTemplate
‘insert the order number into A1
objExcel.Cells(1, 1) = strMasterOrder
‘Save with the new name
objExcel.ActiveWorkbook.SaveAs strFileSaveName, FileFormat:=xlOpenXMLWorkbook, Password:=””, WriteResPassword:=””, ReadOnlyRecommended:=False, CreateBackup:=False

Where
strExcelTemplate = BasePath & “job cost sheet.xltx”
strFileSaveName = SupportDir & strMasterOrder & “CostSheet.xlsx”

This code ran happily (without the ‘x’) for several years on versions of Office from 98 to 2010 and later with the ‘x’ on 2010 and 2013. Windows versions from XP to 8.1.

In March we had to upgrade a Dell laptop to Office 365. We had some issues with Excel at the time but not with this particular piece of code.

Two weeks ago it quit working properly. On the production machine when one clicks the ‘Cost Sht’ button it goes through all of the right steps but it also invokes the compatibility checker without making it accessible thus appearing to hang the program. You have to call the task manager, switch to the Excel compatibility checker (the one that promises the world will end if you save in that old format) and when the dialogue box comes up click Continue. At that point the spreadsheet appears with the designated name/extension.

Thinking that it might be just a setting changed by an update I installed it on another Dell laptop running Windows 7. It doesn’t work on that one either but on that one I get a “Cancel impending refresh” messsage, the sheet opens, and I have to manually refresh to get the data updated.

I’m probably just missing some little quirk in the second instance but I’m stumped as to the compatibility issue since I’m starting with an x format template and saving the same way.

The program continues to run properly in 2010 on Windows 7.
The Access and Excel options on both Dells are the same.

Any insights will be greatly appreciated.

Reply | Quote

Replies

Office 365 currently gives you Office 2013 on the desktop, so if it runs on 2013 on other machines, it really ought to run on this one. You can skip the compatibility check using:

Code:

'Save with the new name
With objExcel.ActiveWorkbook
.CheckCompatibility = False
.SaveAs strFileSaveName, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
End With

It’s not clear to me what data is refreshing, or when, so it’s hard to comment on that.

Reply | Quote

Thanks Rory, that did the trick. It also fixed the data refresh issue which is still a mystery but I’m not going to poke it.

Reply | Quote