Using Excel templates to create new workbooks not working after Excel update

[SveinnIngi]

Hi,

I’ve been using the same code to create Excel BOM documents using a template. It worked for 10 years until suddenly last week. Everything indicates that this is due to a Windows or Excel update. All I know is that this works in Excel version 2208 and not in 2302.

I get “Exception from HRESULT: 0x800A03EC” in this line:

excelWorkbook = excelApp.Workbooks.Add (Template: = "C:\path\to\template.xltx”)

Has anyone seen something similar or have an idea for me to out?

[A.Acheson]

Hi @SveinnIngi 

Can you attach the complete code for testing? 

Also what does the more info tab of the error message say? 

[SveinnIngi]

Hi Alan. Thank you. I don’t have the complete code at the moment but I saved a screenshot of the more info tab message: 

I hope this clarifies but I can attach or send you a code tomorrow if needed.

Regards,

Sveinn

[A.Acheson]

Perhaps the declarations are missing for excel workbook etc. It is just generically declared as an object..

The error suggest late bindings are occurring see help page on this.

According to this stackoverflow post you can just supply a fullfilepath as below. 

AddReference "microsoft.office.interop.excel.dll"
Imports XL = Microsoft.Office.Interop.Excel
Dim excelApp As XL.Application = GetObject("", "Excel.Application")
Dim newWorkbook As XL.Workbook = excelApp.Workbooks.Add ("C:\path\to\template.xltx”)

[SveinnIngi]

Thank you @A.Acheson, I haven't been able to make it work yet with my limited understanding of vba.

Attached is a trimmed and simplified version of the code as it has been working before the Excel update.

Regards,

Sveinn

[A.Acheson · Accepted solution]

I tested your code as is and apart from switching of the excel processing I was able to get a file saved from the template with both Template:="C:Temp.....xltx" and also with  just the file path "C:Temp.....xltx"

Here is a updated version of the code with late bindings removed. See if it runs better with your excel. tested with Excel 2016.

AddReference "microsoft.office.interop.excel.dll"
Imports XL = Microsoft.Office.Interop.Excel

'define the file to create/open
Dim myXLS_File As String = "C:\Temp\DocName.xlsx"'

Dim excelApp As XL.Application = GetObject("", "Excel.Application")

'set Excel to run visibly, change to True if you want to run it visibly
excelApp.Visible = False
'suppress prompts (such as the compatibility checker)
excelApp.DisplayAlerts = False

Dim excelWorkbook As XL.Workbook 

'check for existing file 
If Dir(myXLS_File) <> "" Then
	'workbook exists, open it
	excelWorkbook  = excelApp.Workbooks.Open(myXLS_File)
	Dim ExcelSheet As XL.Worksheet= excelWorkbook.Worksheets(1)
Else
	'create a new spreadsheet from template
	excelWorkbook = excelApp.Workbooks.Add (Template:= "C:\path\to\template.xltx")'
End If

excelWorkbook.SaveAs (myXLS_File)

excelWorkbook.Close
excelApp.Quit
'excelApp = Nothing

[SveinnIngi]

Thank you very much, Alan.

In the meantime, I ran some office repair on my computer. After that both codes work, the original and the one you provided. I tested your code on a computer that had not been updated and unfortunately I also got some errors, different though. I didn't spend too much time analyzing that, since the conclusion is that there is something weird going on with Office in my company and IT needs to help us update.

I really appreciate your help.

Best regards,

Sveinn