Close Excel Workbook that is Open, and not the first instance.

Close Excel Workbook that is Open, and not the first instance.

C_Haines_ENG
Collaborator Collaborator
1,537 Views
21 Replies
Message 1 of 22

Close Excel Workbook that is Open, and not the first instance.

C_Haines_ENG
Collaborator
Collaborator

Hello all, I'm going to unbury this monster because its driving me completely insane!

 

So I've figured out how to get the first instance of an excel application, but this code will not work if its not the first excel instance open. GetObject(,"Excel.Application") will only get the first instance of an excel application and it would be very common to have another engineer have another excel document open before running my ilogic script.

 

Is there a way to find if an excel workbook is open no matter if its the first or 30th instance of excel, and close the application attached to it, delete it, and then create a new one to save to that location.

 

Attached below is my code:

 

BOMPath = "C:\BOM Calculator\PART_BOM.xlsx" 'FINAL DESTINATION OF BOM RESULTS

'CREATE CALCULATOR FOLDER IF IT DOESNT EXIST
If Not System.IO.Directory.Exists(System.IO.Path.GetDirectoryName(BOMPath)) Then
    System.IO.Directory.CreateDirectory(System.IO.Path.GetDirectoryName(BOMPath))
	
End If

If System.IO.File.Exists(BOMPath)

	ExcelApp = GetObject(BOMPath).Application
	
	ExcelApp.Range("B2") = "PART #    "
	
	ExcelWB = ExcelApp.Workbooks("PART_BOM.xlsx")
	ExcelWB.Close
	ExcelApp.Quit
	
	ExcelApp = Nothing
	
	My.Computer.FileSystem.DeleteFile(BOMPath)
	
End If
	


'[ CREATE EXCEL APPLICATION, INSERT HEADER, AND CLOSE PREVIOUS INSTANCE IF STILL OPEN
	
If System.IO.Directory.Exists(BOMPath)
	My.Computer.FileSystem.DeleteFile(BOMPath)
End If
	
ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = True
ExcelWB = ExcelApp.Workbooks.Add
ExcelWB.SaveAs(BOMPath)

AppActivate("PART_BOM")

ExcelApp = Nothing

Despite what the documentation says:

ExcelApp = GetObject(BOMPath).Application

Will CREATE a hidden background process if the file isnt open, driving me completely insane.

 

I cant figure this out, and when I get close it just closes every single excel application thats open. Please help. 

0 Likes
Accepted solutions (1)
1,538 Views
21 Replies
Replies (21)
Message 2 of 22

WCrihfield
Mentor
Mentor

Hi @C_Haines_ENG.  I have never seen anyone do it quite like that.  The vb.net Interaction.GetObject() Function is not always very well understood.  If you supply a 'path', whether it is a full file path and name with extension to an existing file, or just an empty String, it will attempt to start an instance of the application that is the default one for opening the type of file specified, or the type of application specified in the 'Class' portion.  You must NOT PROVIDE the 'Path' portion, not even an empty String, if you do not want to start a new instance of the application.  But then, if there is no active instance of that application, it will throw an error.  That's why I always use a Try...Catch...End Try statement, where the 'GetObject' method is in the Try side, and the CreateObject method is on the Catch side.

 

Another aspect of that GetObject that many do not realize is the Type of object it will return to whatever variable you are capturing it to.  Even though you are targeting a specific Application object, it may return one Type on the GetObject side, while returning a different Type on the CreateObject side.  We usually need to check which Type we got, then use one of the 'Cast' methods to get the Type we want to be working with directly.

Take a look at this GetExcel Function I often use, as an example:

 

AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop.Excel

Function GetExcel(Optional bVisible As Boolean = False) As Microsoft.Office.Interop.Excel.Application
	Dim oObj As Object = Nothing
	Try
		'try to find an already running instance of the Excel Application
		oObj = GetObject(, "Excel.Application")
		'Logger.Info("GetObject - TypeName(oObj) = " & TypeName(oObj))
		'gets an Application Type object
	Catch
		'it wasn't found open, so create an instance of it (start the application)
		oObj = CreateObject("Excel.Application")
		'Logger.Info("CreateObject - TypeName(oObj) = " & TypeName(oObj))
		'gets an ApplicationClass Type object
	End Try
	If oObj Is Nothing Then Return Nothing
	Dim oXL As Microsoft.Office.Interop.Excel.Application = Nothing
	Try
		oXL = DirectCast(oObj, Microsoft.Office.Interop.Excel.Application)
	Catch
		Logger.Error("Error trying to 'Cast' " & TypeName(oObj) & " to 'Excel.Application' Type!")
	End Try
	oObj = Nothing
	If oXL IsNot Nothing Then oXL.Visible = bVisible
	Return oXL
End Function

 

PS.  Also, it can be good practice to create a Boolean variable just before that process, that is set to False.  This Boolean variable will be used to record if we 'created' this instance of the Application or not (True if Created).  The later, after we are done using that Application, use that Boolean variable to determine whether or not to 'Quit/Close' the Application.  If the Boolean is true (meaning Application was created), then it is OK to quit/close the application, else do not close it.  Just an additional tip.

 

If this solved your problem, or answered your question, please click ACCEPT SOLUTION .
Or, if this helped you, please click (LIKE or KUDOS) 👍.

 

Wesley Crihfield

EESignature

(Not an Autodesk Employee)

0 Likes
Message 3 of 22

C_Haines_ENG
Collaborator
Collaborator

I had used this for previous versions but was running into this problem all the same. The GetObject(,"Excel.Application") only grabs the first instance of Excel, not the last opened, so if you open say a reference sheet and then run this program, you're out of luck.

 

GetObject("PATH").Application does actually get the correct file if its open, but otherwise will create its own instance.  If I could work it so that it doesn't open in the background and I could close it even if it did open, it would work fine.

 

If you do GetObject("PATH") it will do the same thing, but alot better. I was able to close this one because this targets the "Workbook" instead of the application, so you can close it but then it leaves the application open. Maybe I should have settled for this.

 

I couldn't find a way to derive the Application from the workbook, only the workbook from the application. I think that may be the missing component. 

0 Likes
Message 4 of 22

WCrihfield
Mentor
Mentor

Hi @C_Haines_ENG.  In the past I have read about, and looked into complicated scenarios where multiple instances of Excel are open at the same time, and trying to work with a specific instance of it, but I never went far enough into it for it to become 'readily available info in my head', because I simply never needed that level of control where I work, and therefore do not have that working experience with that specific scenario.

 

I can however get Workbook from Application, or Application from Workbook.  Funny thing is that most of these Types of objects are Interface, instead of Class instances, so we can not simply use 'New' keyword, and must instead use Property values to get them.  The Workbook object has a Property named Application, and also one called ActiveSheet (Object value Type).  The Application has several 'Active...' type properties, including the ActiveWorkbook Property, ActiveSheet, ActiveCell, and also has the Workbooks Property.  Worksheet also has an Application Property.

 

1)  What is odd to me at the moment is, if I do not have an instance of Excel open, and use the 'GetObject' method, and specify the full file name of the Excel file I want to open, it will give me a Workbook object, which I can then use to get the Application object, which I can then use to get the Worksheet object.  However it seems to only allow me to visibly show the Application, and not the Workbook or Worksheet.

 

2)  If I use the GetObject method, and specify an empty Strings for path, and specify the Class, it will return an ApplicationClass object Type.  I can then use that to 'Open' the file, to get the Workbook object.  I can then use the Workbook object to get the Worksheet object.  In this scenario, I can make all objects involved visible.

 

3)  If I use the CreateObject method, it will give me the ApplicationClass object, which I can use to open the file to get the Workbook, then the Worksheet, and can also make all of them visible.

 

Example of first scenario test code (CreateObject, supplying full file name):

Dim sFile As String = "C:\Temp\Test.xlsx"
If Not System.IO.File.Exists(sFile) Then Return
'gets the Workbook object
Dim oWB As Object = GetObject(sFile, "Excel.Application")
Logger.Info("GetObject Type = " & TypeName(oWB))
'gets the Application object
Dim oXLApp As Object = oWB.Application
Logger.Info("oXLApp Type = " & TypeName(oXLApp))
oXLApp.Visible = True 'make the application visible
'there is no Workbook.Visible property or method, and ActiveWorkbook is ReadOnly
'gets the Worksheet object
Dim oWS As Object = oWB.ActiveSheet
Logger.Info("oWS Type = " & TypeName(oWS))
oWS.Visible = True 'supposed to make the Worksheet visible, but does not in test
MsgBox("Review Opened Excel file.", vbInformation, "iLogic")
oWB.Close
MsgBox("Review Workbook Closed, but not Excel.", vbInformation, "iLogic")
oXLApp.Quit
oWS = Nothing
oWB = Nothing
oXLApp = Nothing

Example of second scenario test code (GetObject, empty String supplied):

Dim sFile As String = "C:\Temp\Test.xlsx"
If Not System.IO.File.Exists(sFile) Then Return
'get the ApplicationClass object
Dim oXLApp As Object = GetObject("", "Excel.Application")
Logger.Info("oXLApp Type = " & TypeName(oXLApp))
oXLApp.Visible = True 'make the application visible
'get the Workbook object
Dim oWB As Object = oXLApp.Workbooks.Open(sFile)
Logger.Info("GetObject Type = " & TypeName(oWB))
'gets the Worksheet object
Dim oWS As Object = oWB.ActiveSheet
Logger.Info("oWS Type = " & TypeName(oWS))
oWS.Visible = True 'supposed to make the Worksheet visible, but does not in test
MsgBox("Review Opened Excel file.", vbInformation, "iLogic")
oWB.Close
MsgBox("Review Workbook Closed, but not Excel.", vbInformation, "iLogic")
oXLApp.Quit
oWS = Nothing
oWB = Nothing
oXLApp = Nothing

Example of scenario 3 test code (CreateObject):

Dim sFile As String = "C:\Temp\Test.xlsx"
If Not System.IO.File.Exists(sFile) Then Return
'gets the ApplicationClass object
Dim oXLApp As Object = CreateObject("Excel.Application")
Logger.Info("oXLApp Type = " & TypeName(oXLApp))
oXLApp.Visible = True 'make Excel visible
'gets the Workbook object
Dim oWB As Object = oXLApp.Workbooks.Open(sFile)
Logger.Info("oWB Type = " & TypeName(oWB))
'gets the Worksheet object
Dim oWS As Object = oWB.ActiveSheet
Logger.Info("oWS Type = " & TypeName(oWS))
oWS.Visible = True 'make the Worksheet visible
MsgBox("Review Opened Excel file.", vbInformation, "iLogic")
oWB.Close
MsgBox("Review Workbook Closed, but not Excel.", vbInformation, "iLogic")
oXLApp.Quit
oWS = Nothing
oWB = Nothing
oXLApp = Nothing

Not sure why scenario 1 is acting like that, but maybe that is part of the problem you seem to be experiencing...aside from the multiple instances of Excel issue.

Wesley Crihfield

EESignature

(Not an Autodesk Employee)

0 Likes
Message 5 of 22

WCrihfield
Mentor
Mentor

Another thought comes to mind about a possible way to work around the multiple instances issue, but it is untested.

Once you get the Application object, it has a Property named Windows.  That is a collection of all the Windows in Excel, for all Workbooks, whether they are visible or hidden.  You could check its Count, then if more than 1, then iterate through them, checking something about them.  A Window has several properties (including Application and Visible - also Activate and Close methods), but perhaps the best one to use in this situation might be the ActiveSheet property (there is no ActiveWorkbook property).  It can possibly have Nothing as its value, if no sheets are active in it.  You could then check the object it returns against the Worksheet you have an reference/instance of, to see if they are the same object.  Or, if it had a value, you could try getting the sheet's parent Workbook, then compare that to the one you are trying to work with.  Just some food for thought.

Wesley Crihfield

EESignature

(Not an Autodesk Employee)

0 Likes
Message 6 of 22

mat_hijs
Collaborator
Collaborator

I use the following code to open and close Excel:

Private Declare Auto Function GetWindowThreadProcessId Lib "user32.dll" (ByVal hwnd As IntPtr,
          ByRef lpdwProcessId As Integer) As Integer

Public Shared Function OpenExcel(ByVal Optional bVisible As Boolean = True) As Application
    Dim oResult As Application = Nothing
    Dim oExcelApplication As Application = Nothing

    Try
        ' Create an instance of Excel
        oExcelApplication = New Application With {
        .Visible = bVisible
        }
    Catch ex As Exception
        ' Release the application in case of an error
        If oExcelApplication IsNot Nothing Then
            Marshal.ReleaseComObject(oExcelApplication)
        End If
    End Try

    oResult = oExcelApplication

    Return oResult
End Function

Public Shared Sub CloseExcel(ByVal oExcelApplication As Application)
    Try
        ' Get the handle of the excel application
        Dim iHandle As Integer = oExcelApplication.Hwnd

        ' Get the process ID
        Dim iProcessID As Integer = 0

        GetWindowThreadProcessId(iHandle, iProcessID)

        ' Get the process
        Dim oProcess As Process = Process.GetProcessById(iProcessID)

        ' Quit the excel application
        oExcelApplication.Quit()

        ' Release the excel application object
        ReleaseObject(oExcelApplication)

        ' Kill the process if it hasn't exited
        If Not oProcess.HasExited Then
            oProcess.Kill()
        End If

    Catch ex As Exception

    End Try

End Sub

This will create a new instance of Excel, no matter if one is already opened. It closes a specific instance of Excel so you just need to pass the result of the OpenExcel Function to the CloseExcel Sub and it will close the exact instance you created. This should prevent any background processes that keep running after closing Excel.

Message 7 of 22

WCrihfield
Mentor
Mentor

Hi @mat_hijs.  Nice idea in general, but a few details seem to be missing.

  • First, we can not use 'New' keyword to create an 'instance' of an Interface, because the Excel.Application object is an Interface, not a Class.
    • There is the Excel.ApplicationClass Type that is a Class, and we can use the 'New' keyword to create an instance of that...which is what we get from the Interaction.CreateObject method.
  • Next, the references/imports are missing, so terms like 'Marshal' and 'ReleaseObject' are not recognized...likely due to getting this from an add-in or StackOverflow type source, instead of being designed for use within iLogic rules.
    • Marshal is a Class within the System.Runtime.InteropServices Namespace.
  • Next, the phrases "As Application" and "New Application" are ambiguous (not specific enough), because it can get confused with Inventor.Application or System.Windows.Forms.Application (or other referenced API's Application).  It should likely be specified as Excel.Application instead, to avoid that potential issue.

Wesley Crihfield

EESignature

(Not an Autodesk Employee)

0 Likes
Message 8 of 22

mat_hijs
Collaborator
Collaborator

I'm not really sure on the first issue you mention, but it works for me.

I did copy this snippet from my add-in so that's why some references may be missing indeed.

0 Likes
Message 9 of 22

WCrihfield
Mentor
Mentor

I am not 100% sure, because I can not test it on my end right now, but I believe I understand now why it is allowing the 'New' keyword to be used.  I believe it is because of the ambiguity, and mistaken Type identity.  If I drop that code into an iLogic rule, then hover my mouse over those 'Application' terms within those declaration lines, the intellisense shows it as being 'System.Windows.Forms.Application', instead of either Inventor.Application or Excel.Application or Excel.ApplicationClass.

WCrihfield_0-1725627779250.png

And the System.Windows.Forms.Application Type is a Class, instead of an Interface, so we are allowed to use the New keyword on it.  However, if I change 'Application' to 'Excel.Application' it gives me the 'New can not be used on an Interface' error, because both Excel.Application Type and also Excel._Application Type are both an Interface, and only the Excel.ApplicationClass is a Class.  I am also not sure why it is not showing an ambiguity error on your end, but I suppose Visual Studio may treat that type of situation differently than iLogic rules.  Even though I have VS 2022 Community installed, I do not use it that much, and is mostly to support Inventor SDK related stuff.  Where I work no one has administrator's rights on their own computers, so I doubt I will ever be able to author my own Inventor add-ins while working here.  So, I tend to push the capabilities of iLogic to its limits, somewhat simulating functionality of add-ins, since it works with vb.net.

 

Also, out of curiosity, I did quite a bit of searching online, and within the Microsoft vb.net documentation, and could not find anything about that method you are using within your "CloseExcel" method named "ReleaseObject".  I can find tons of stuff about the Marshal.ReleaseComObject and Marshal.FinalReleaseComObject, but not that other one.  But I see you using the Marshal one within your "OpenExcel" method, so I am not sure why the two are different.  Is that a custom method defined outside of this code somewhere else within your add-in?  If not then, what Namespace &/or assembly is that method defined in?  Why using a different method in those two methods?  Again, just curious, not picking, because I like learning about anything new I see.

Wesley Crihfield

EESignature

(Not an Autodesk Employee)

0 Likes
Message 10 of 22

mat_hijs
Collaborator
Collaborator

In my Add-In when I hover over Application it shows Microsoft.Office.Interop.Excel.Application, which is indeed an interface. This is probably because Microsoft.Office.Interop.Excel is imported and Inventor is not. I honestly don't even know what an interface is, that's beyond my little understanding of coding.

 

ReleaseObject is one of my own Subs, I just forgot to add it.

Public Shared Sub ReleaseObject(ByVal oObject As Object)
    Try
        Marshal.FinalReleaseComObject(oObject)
        oObject = Nothing
    Catch ex As Exception
        oObject = Nothing
    End Try
End Sub

Why am I using that and simply Marshal.FinalReleaseComObject? Just because I overlooked it. Is it necessary to set oObject to Nothing? I don't know.

I had been messing around trying to fix the problem of closing Excel properly and when I finally got it working I just never touched it again.

 

I also don't see what you're doing as picking, you're just trying to learn and also help other people.

Message 11 of 22

WCrihfield
Mentor
Mentor

OK, thanks.  Like you, I do not have a university degree in software design, or decades of experience writing code for applications.  One of my degrees did involve generating CNC programming, both manually, and with the help of multiple CAM software, and have decades experience in that area.  I do have some experience with VBA in Excel, then later in Inventor, but have mostly only dabbled into other areas of coding here and there over the years, on the side.  I got more heavily into learning vb.net when I moved from Solidworks to Inventor, and began utilizing its iLogic add-in's capabilities, which I liked way more than the older VBA system, even though it had a much more simplistic user interface to work within.  The custom 'snippets' are, right beside the code window, has been a huge help along the way.

Good luck in your future Inventor automation pursuits.

Wesley Crihfield

EESignature

(Not an Autodesk Employee)

0 Likes
Message 12 of 22

C_Haines_ENG
Collaborator
Collaborator

How does this close a specific instance?

 

This also only works in excel doesn't it?

0 Likes
Message 13 of 22

mat_hijs
Collaborator
Collaborator

It closes a specific instance because I pass the Excel object that I created myself to the sub. It gets the handle of that specific instance, based on that the process ID and based on that the process itself. Then it closes that specific process.

I use this code in my Add-in for Inventor.

0 Likes
Message 14 of 22

C_Haines_ENG
Collaborator
Collaborator

So I would have to open and close the application at once? I'm trying to close an excel app that I just left opened. 

0 Likes
Message 15 of 22

C_Haines_ENG
Collaborator
Collaborator
Accepted solution

As always, there's a simple code to do a hard job somewhere. Using your information it finally clicked for me and I present to you the working Excel opener and deleter. This works with multiple instances of excel. If you supply GetObject with just a path it will either find the open instance of it or create a background process of the WORKBOOK, not the application which was where I was getting tripped up. 

 

Dim ExcelPath As String = "PATH TO YOUR EXCEL FILE"

Try 'TRIES TO FIND IF EXCEL FILE IS OPEN, OR IF IT EXISTS (IN THAT ORDER)

	ExcelWB = GetObject(ExcelPath)
	ExcelApp = ExcelWB.Application
	ExcelApp.DisplayAlerts = False

	ExcelWB.Close
	If ExcelApp.Workbooks.Count = 0 Then ExcelApp.Quit

	System.IO.File.Delete(ExcelPath)

Finally 'IF IT GETS HERE THE FILE IS NOT OPEN, AND DOESNT EXIST, SO CREATE A NEW APPLICATION. 

	ExcelApp = CreateObject("Excel.Application")
	ExcelApp.Visible = True
	ExcelApp.DisplayAlerts = False

	ExcelWB = ExcelApp.Workbooks.Add
	ExcelWB.SaveAs(ExcelPath)

End Try

This code will open and close an instance of excel determined by the supplied path. After it closes, it will delete the file. 

0 Likes
Message 16 of 22

mat_hijs
Collaborator
Collaborator

That would depend on how you define at once. The idea is that I open Excel, do whatever needs to be done in Excel and then close Excel.

I don't really see the difference that using GetObject to open the workbook instead of doing it the regular way would make in closing everything. The way you are actually closing it is exactly the same as how I initially started to do it, but this often left a background process running. However I'm using an add-in and I believe you are using iLogic. From past experiences I feel like iLogic somehow handles some of these problems automatically, but I'm not 100% sure.

As long as it works for you that's ok!

0 Likes
Message 17 of 22

C_Haines_ENG
Collaborator
Collaborator

I'm trying to avoid the user having to close the excel workbook every time when this rule could be run more than 5 times.

 

Plus its a reference sheet so you would be reading off of it until you wanted to update it. Even still this code would work for your application too, it works with already closed or open documents. Give it a try?

0 Likes
Message 18 of 22

C_Haines_ENG
Collaborator
Collaborator

I should note that it IS creating a background instance but I just ignore the problem and close it either way. All GetObject is doing is making SURE the workbook is closed so I can delete it. 

0 Likes
Message 19 of 22

mat_hijs
Collaborator
Collaborator

My code already works for me regardless of when Excel is already open or not. In my case I always want to open the Excel file, read the data and then close it again. The Excel files to me are nothing more than the data I need to process, once read I don't need it anymore and I don't even open them visibly.

I am trying to see why you wouldn't want to close the Excel that you opened though. Wasn't that the point of your post? To close the specific instance that you opened, instead of just the first one?

0 Likes
Message 20 of 22

C_Haines_ENG
Collaborator
Collaborator

I only needed to close the workbook so I could delete it. I want to completely clear the document every time its run. 

 

If I was just opening and closing an excel document, I would just Workbook.Close and Application.Quit on the instance I created. 

 

Although if you aren't even visibly reading it why open it? Isn't there a ton of usecases of reading data from a closed excel workbook? iLogic has GoExcel

0 Likes