How do a create a new Excel file with GoExcel?
Hi @harvey_craig2RCUH. Good question. You basically just do not when using iLogic's GoExcel tools. There are other ways of doing things with Excel in iLogic rules, without using the GoExcel tools, but those are generally much simper to use for basic stuff, and for folks with out much coding experience. My guess is that the code behind the scenes supporting the functionality of the GoExcel iLogic tool is wanting to be able to dispose of any resources being held in Inventor's memory related to the GoExcel tool once you are done using it. And if you still have a reference to a new Excel document that was created by the GoExcel tool, there would still be a resource in your control that it would not have any control over. But I am just guessing. You would have to ask the folks at Autodesk who designed those tools why they did not include a method for that specific task. I'm sure they had a good reason for it, I am just not sure what it is.
Those tools primarily provide a simpler/shortcut way of doing basic tasks which need to use some Excel like functionality, and to be able to do it with very little code. If we have Microsoft Excel installed, we can include some lines in the 'Header' of our rules that will give us access to, and 'Intellisense' support for, the Excel API system, so that we can control Excel using vb.net code. There are several examples of code like this here on this forum. Some of those examples include the header lines, and some do not, it all depends on their specific needs, and specific local resources/system.
Wesley Crihfield
(Not an Autodesk Employee)
Here is a very short and simple example of an iLogic rule that should:
- Starting a new instance of the Excel application
- Creating a new Excel Workbook (Excel document) within that new instance of the Excel application
- Creating a new Excel Worksheet within that new Workbook
- Writing 3 column titles
- Auto-fitting those columns
- Saving that new document to a new file on disk, in the Temp folder
- Closing that document
- Quitting the new Excel application instance.
- Negating the variables we used, to release those references.
...without using the iLogic GoExcel tools.
AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
'Create New instance of the Excel application
Dim oExcel As New Microsoft.Office.Interop.Excel.ApplicationClass
oExcel.DisplayAlerts = False
oExcel.Visible = True
'create new Excel Workbook (document)
Dim oWB As Excel.Workbook = oExcel.Workbooks.Add()
'create new Excel Worksheet (sheet)
Dim oWS As Excel.Worksheet = oWB.Worksheets.Add()
'Create Column Headers by specifying the Cell Address of each cell
oWS.Range("A1").Value = "Column 1"
oWS.Range("B1").Value = "Column 2"
oWS.Range("C1").Value = "Column 3"
'auto-fit the columns
oWS.Columns.AutoFit
'save this new Excel document to a file on disk
oWB.SaveAs("C:\Temp\MyNewExcelFile.xlsx")
'close this Workbook
oWB.Close
'quit the Excel application instance we started
oExcel.Quit
'negate the variables we used, to get rid of system references
oWS = Nothing
oWB = Nothing
oExcel = NothingThe first 3 lines of code shown above are from the 'Header' of the iLogic rule, and provide recognition of the Excel API objects and methods, which enables the built-in Intellisense system to offer help/hints about those objects when you hover your mouse over them, or type a dot after one of them. There are multiple ways to do some of these same things that this code is doing, using different code tactics, and not all of them may work well for everyone. Some folks seem to have more trouble while including those lines in the header, than without them. But without them, we can not 'declare' any of our variables as any of the Excel API object types, and therefore would be essentially coding 'blind' (without any hints/suggestions/help) when dealing with those variables. Sometimes the code will still work OK that way (without the references/imports), but you must be pretty knowledgeable about what you are doing to make it work that way.
Wesley Crihfield
(Not an Autodesk Employee)
Hi @MICHAEL.JONES.AMCE. The error message you are showing is very common. It can be confusing getting a reference to the main application object of a different application other than the one you are actively working within. Some ways seem to work OK for some folks most of the time, and for others only sometimes, while not working at all for yet others. The 'ApplicationClass' type appears to be for 'internal use only', so I generally do not recommend relying on that one. But then the regular Application object appears to be an Interface type, instead of a Class type, so you can not use the 'New' keyword to create a new instance of one. However, we can set a variable to represent an Interface type, then set its value another way, usually through some sort of 'Cast' routine. Below is another pretty simple routine I have just for getting a reference to an instance of the Excel Application object. This routine uses a Try...Catch statement while it first attempts to get an already running instance, then if that fails, it tries to create an instance, and casting the object it gets to the Interface type variable. You can incorporate something like this into your 'Excel' resources. I also have some that are much more detailed for getting or creating things like a Workbook or a Worksheet, with several inputs and optional inputs.
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
AddReference "Microsoft.Office.Interop.Excel.dll"
Sub Main
Dim oExcel As Microsoft.Office.Interop.Excel.Application = GetExcel(True)
Dim oWB As Microsoft.Office.Interop.Excel.Workbook = Nothing
Dim oWS As Microsoft.Office.Interop.Excel.Worksheet = Nothing
If oExcel IsNot Nothing Then
Logger.Info("Got a reference to an Excel Application instance.")
oWB = oExcel.ActiveWorkbook
oWS = oExcel.ActiveSheet
If oWB IsNot Nothing Then
Logger.Info("Excel Active Workbook Name = " & oWB.Name)
oWB = Nothing
Else
Logger.Info("No Active Workbook.")
End If
If oWS IsNot Nothing Then
Logger.Info("Excel Active Worksheet Name = " & oWS.Name)
oWS = Nothing
Else
Logger.Info("No Active Worksheet.")
End If
Else
Logger.Debug("No instance of Excel Application obtained!")
End If
oExcel = Nothing
End Sub
Function GetExcel(Optional bVisible As Boolean = False) As Excel.Application
Dim oXL 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")
oXL = TryCast(oObj, Microsoft.Office.Interop.Excel.Application)
Catch
'it wasn't found open, so create an instance of it (start the application)
oObj = CreateObject("Excel.Application")
oXL = TryCast(oObj, Microsoft.Office.Interop.Excel.Application)
End Try
oObj = Nothing
If oXL IsNot Nothing Then oXL.Visible = bVisible
Return oXL
End FunctionIf this solved your problem, or answered your question, please click ACCEPT SOLUTION .
Or, if this helped you, please click (LIKE or KUDOS) 👍.
Wesley Crihfield
(Not an Autodesk Employee)
Hi @MICHAEL.JONES.AMCE. It is like I said...some of these suggestions may work for me, and may work for some others, but may not work for everyone. I am not always 100% sure why. Another interesting piece of information is that when I do not have Excel visibly open (manually) then use the 'CreateObject' method, I seem to get an instance of the Excel.ApplicationClass Type, but when I have Excel open visibly (manually) and I use the GetObject method I get an instance of the Excel.Application Type. But in both cases I do not get any errors, and all Logger statements print to the iLogic Log window as expected. I am not sure what may be different between my situation and yours.
I am using a Windows 10 Enterprise Dell Precision tower computer, Inventor Professional 2024.0.1, and Microsoft 365 (Version 2312 Build 16.0.17126.20190). In my iLogic Configuration settings, I have my Excel Options Engine set to COM.
Wesley Crihfield
(Not an Autodesk Employee)
