Cannot Close Excel File that was created with iLogic

[C_Haines_ENG]

I cant for the life of me figure out what im doing wrong here, and I'm assuming the problem is that excel is still being controlled somehow by Inventor after the code is run, but I cant seem to find out why or how. Below is a simplified code used to create, and then close an excel file. 

It creates it fine but attemping to close it will throw an error saying its still in use by another application.

FileDIR = "C:\BOM Calculator\PART_BOM.xlsx"

excelApp = CreateObject("Excel.Application")
excelApp.Visible = True
excelApp.DisplayAlerts = False
	
excelWorkbook = excelApp.Workbooks.Add

excelApp.Columns.AutoFit  
excelWorkbook.SaveAs(FileDIR)
excelApp.Application.Visible = True

excelApp = Nothing

'Ask If You Want to Close Excel File
Question = MessageBox.Show("Close Excel File?", "Title", MessageBoxButtons.YesNo)

If Question = vbYes

Try
	excelApp = GetObject("Excel.Application")
		For Each wb As Object In excelApp.workbooks
			MessageBox.Show(wb.fullname)
	        If UCase(wb.fullname) = UCase(FileDIR)
				wb.Save 
				wb.Close   
	        End If
	    Next
Catch
Finally
	excelApp = Nothing
End Try

End If

Do I not have enough SOMETHING = NOTHINGS ?

Any help would be appreciated. 

[WCrihfield]

Hi @C_Haines_ENG.  When using CreateObject, you need to 'dispose of' any variables that got assigned something belonging to that 'created object', to truly remove all ties to it.  In this case, the 'excelWorkbook' variable is still holding on to a reference of the opened Workbook, even after you disposed of the 'excelApp' variable.  Before disposing of the 'excelApp' variable, dispose of any other variables created for stuff related to it, then dispose of the excelApp variable.  What is you overall plan here...create a new Excel file, then what, then later try to save and close it again, but without holding on to the original variables for those objects?  Why?

[Andrii_Humeniuk]

Hi @C_Haines_ENG . I added a comma in line 20 excelApp = GetObject(, "Excel.Application") and now the code works fine.

FileDIR = "C:\BOM Calculator\PART_BOM.xlsx"

excelApp = CreateObject("Excel.Application")
excelApp.Visible = True
excelApp.DisplayAlerts = False
	
excelWorkbook = excelApp.Workbooks.Add

excelApp.Columns.AutoFit  
excelWorkbook.SaveAs(FileDIR)
excelApp.Application.Visible = True
'Ask If You Want to Close Excel File
Question = MessageBox.Show("Close Excel File?", "Title", MessageBoxButtons.YesNo)

excelApp = Nothing
	
If Question = vbYes

	Try
		excelApp = GetObject(, "Excel.Application")
			For Each wb As Object In excelApp.workbooks
				MessageBox.Show(wb.fullname)
		        If UCase(wb.fullname) = UCase(FileDIR)
					wb.Save 
					wb.Close   
		        End If
		    Next
	Catch
	Finally
		excelApp.Quit
		excelApp = Nothing
	End Try
Else	
End If

[C_Haines_ENG]

Not sure how its working for you, it still doesnt work for me. 

[C_Haines_ENG]

I tried clearing the workbook variable aswell after both, its not working.

My goal is to create an excel file, write about a million things to it and then KEEP IT OPEN to be able to be copied off of. Im macgyvering a BOM calculator, so you need to be able to copy it to enter it into the system.

However when I run the rule again to re-do all the calculations , it wont close the open excel file. I forget to close it beforehand constantly and it should be able to be done through iLogic so im not sure why its so difficult

[Michael.Navara]

If you start new instance of Excel using CreateObject("Excel.Application"), you are responsible for closing them. You can't only set excelApp = Nothing you must exactly exit the application using excelApp.Quit(). If you call GetObject("Excel.Application") later, it is not guarantied you obtain exactly the same instance of Excel. You get one of Excel objects from RunningObjectTable.

I recommend you to close Excel when you finish your task. Don't keep them open. Or try to get existing (running) Excel application instead of create new one. At least close all files and free them for another processes.

[C_Haines_ENG]

excelApp.Quit was the solution, doesn't even need the excelApp = Nothing, that actually screws it up more.

Here is the final code, works perfectly. Interestingly enough it still doesnt show the message box with the name of the document, closes it anyway. *shrug*

FileDIR = "C:\BOM Calculator\PART_BOM.xlsx"

excelApp = CreateObject("Excel.Application")
excelApp.Visible = True
excelApp.DisplayAlerts = False
	
excelWorkbook = excelApp.Workbooks.Add

excelApp.Columns.AutoFit  
excelWorkbook.SaveAs(FileDIR)
excelApp.Application.Visible = True
'Ask If You Want to Close Excel File
Question = MessageBox.Show("Close Excel File?", "Title", MessageBoxButtons.YesNo)

excelApp.Quit '<- THIS WAS REQUIRED
	
If Question = vbYes

	Try
		excelApp = GetObject(, "Excel.Application")
			For Each wb As Object In excelApp.workbooks
				MessageBox.Show(wb.fullname)
		        If UCase(wb.fullname) = UCase(FileDIR)
					wb.Save 
					wb.Close  
					excelApp.Quit
		        End If
		    Next
	Catch
	End Try
Else	
End If

[WCrihfield]

After your first line of code for MessageBox.Show, where you ask the user if they want to close Excel, you then immediately Quit Excel, before checking the answer to that question.  Then immediately after you quit the Excel application, you are trying to obtain a reference to Excel again, using GetObject, then trying to find that same Workbook again, which seems like an inefficient process.  If you are planning on writing stuff to that Excel file after you create it, then use SaveAs on it, then you will want to leave the Excel application open, and maintain those original variables throughout the rest of your code, then use those same variables to save and close the Workbook, and quit the Excel application at the end, instead of finding the Excel application and the Workbook again at the end.  Even though you used SaveAs on the new Workbook, the excekWorkbook variable should still be representing that Workbook.  If you used Workbook.SaveCopyAs, then the the excelWorkbook variable would no longer be representing that other new file, but would still be representing the original file.

[C_Haines_ENG]

This code was just for quick reference, the quit section will be at the beginning of the code, so that when I re-run the code later it will close the application. You can pretty much imagine everything after the message box as a seperate rule all together. 

[C_Haines_ENG]

I am now seeing what you mean, and yes the code doesnt work anymore...

FileDIR = "C:\BOM Calculator\PART_BOM.xlsx"

'[ TRY TO CLOSE EXCEL FILE FIRST
Try

excelApp = GetObject(, "Excel.Application")

For Each wb As Object In excelApp.workbooks
	If UCase(wb.fullname) = UCase(FileDIR)
		'wb.Save 
		'wb.Close  
		excelApp.Quit
	End If
Next

Catch : End Try	
']

'[ CREATE NEW FILE AND INSERT DATA
excelApp = CreateObject("Excel.Application")
excelApp.Visible = True
excelApp.DisplayAlerts = False
	
excelWorkbook = excelApp.Workbooks.Add

excelApp.Columns.AutoFit  
excelWorkbook.SaveAs(FileDIR)
excelApp.Application.Visible = True

'Insert data into Excel.
With excelApp
	.Range("A1").Select
	.ActiveCell.Value = "Hello, Cole"
End With
']

I realize that it wasnt closing because the loop found it again, it was closing because it was still attached to the first excelApp. I am dumb.