Can't open Excel 2013 file after it's been edited through Access 2013 VBA

Question

I am working on an Access database where I have a button that, when clicked, copies a file (a template excel file) and then opens the new copy, sets the value of a couple of cells, and then saves & closes it. Another button does the exact same thing, but right afterwards calls another sub to print the new file.

The problem I'm having is, I can't manage to open these edited files in Excel. They print without any problems through the access VBA code, which means it does get edited, saved, and then printed. But when I double click one of these files, excel starts but the file doesn't open at all. There's no table. If I already had a file open in Excel, and right click one of these files and select print, the previous excel sheet gets printed instead.

Here's the Acess VBA sub that copies the template and then proceeds to edit the copy:

Private Sub SaveXLFile(FileNameFull As String)
'FileNameFull: Complete path for file to be saved, including extension.

Dim ExcelApp As Excel.Application
Dim ExcelBook As Excel.Workbook
Dim ExcelSheet As Excel.Worksheet

Dim fso As Object

'copy template
Set fso = VBA.CreateObject("Scripting.FileSystemObject")
fso.CopyFile ThePath & "Arbetsorder - Blankett.xlsx", FileNameFull


Set ExcelBook = GetObject(FileNameFull)
Set ExcelApp = ExcelBook.Parent
Set ExcelSheet = ExcelBook.Worksheets(1)
'we are open and ready for action!


'take values from form and put them in the correct places
ExcelSheet.Range("C2").Value = [Kund]

'edit page 2
'Set ExcelSheet = ExcelBook.Worksheets(2)
'ExcelSheet.Range("B31").Value = [TB_Tid_Ställ]


ExcelBook.Close savechanges:=True
Set ExcelSheet = Nothing
Set ExcelBook = Nothing
Set ExcelApp = Nothing
End Sub

The print code is as follows:

Private Sub PrintXLFile(FileNameFull As String)
'opens an excel file and prints it.
'FileNameFull: Complete path to file, including extension.
Dim ExcelApp As Excel.Application
Dim ExcelBook As Excel.Workbook
Dim ExcelSheet As Excel.Worksheet

Set ExcelBook = GetObject(FileNameFull)
Set ExcelApp = ExcelBook.Parent
Set ExcelSheet = ExcelBook.Worksheets(1)

ExcelSheet.PrintOut Copies:=1, Collate:=True
ExcelBook.Sheets(2).PrintOut Copies:=1, Collate:=True

ExcelBook.Close savechanges:=False 'close without changes
Set ExcelSheet = Nothing
Set ExcelBook = Nothing
Set ExcelApp = Nothing
End Sub

...and the sub for the actual button click is not shady in any way:

Private Sub KnappPrint_Click()
Dim NewFileNameWExt As String

NewFileNameWExt = [I-Order] & ".xlsx"
SaveXLFile (ThePath & "Arbetsordrar\" & NewFileNameWExt)
PrintXLFile (ThePath & "Arbetsordrar\" & NewFileNameWExt)
End Sub

What am I doing wrong? I've tried only editing the first sheet in case I did something wrong when switching sheets, but that did not solve it.

What is even more confusing is that it prints fine when KnappPrint_Click() is run, but right clicking the file in Explorer and selecting Print does nothing.

Answer 1

The reason why you are having problems with your sheet is how you are opening your workbook. Try changing your Excel app and workbook allocations to this:

Set ExcelApp = CreateObject("Excel.Application")
Set ExcelBook = ExcelApp.Workbooks.Open(str)
Set ExcelSheet = ExcelBook.Worksheets(1)