- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Cannot Close Excel File that was created with iLogic
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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?
Wesley Crihfield
(Not an Autodesk Employee)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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
Andrii Humeniuk - CAD Coordinator, Autodesk Certified Instructor
LinkedIn | My free Inventor Addin | My Repositories
Did you find this reply helpful ? If so please use the Accept as Solution/Like.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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.
Wesley Crihfield
(Not an Autodesk Employee)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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.