- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Hi @terry.nicholls .
There are several ways to start up the Excel application, open Excel documents, and work with Excel in general. In the case of that rule, it seems I chose the following lines to start the Excel application and assign it to a variable:
Dim oExcelApp As Microsoft.Office.Interop.Excel.Application
oExcelApp = New Microsoft.Office.Interop.Excel.ApplicationClass
The simplest change I might suggest is to change 'ApplicationClass' to 'Application' at the end of that second line like this:
Dim oExcelApp As Microsoft.Office.Interop.Excel.Application
oExcelApp = New Microsoft.Office.Interop.Excel.Application
Those ways will always attempt to create a new instance of Excel, whether it is currently running or not.
Or, there are other options too. One option would be to simply use the GetObject() &/or CreateObject() methods like this:
Dim oExcel As Microsoft.Office.Interop.Excel.Application
Try
'try to find an already running instance of the Excel Application
oExcel = GetObject(, "Excel.Application")
MsgBox("Found an instance of the Excel Application already open.",,"")
Catch
'it wasn't found open, so create an instance of it (start the application)
oExcel = CreateObject("Excel.Application") 'Option 1
MsgBox("Created a new instance of the Excel Application.", , "")
Catch
MsgBox("Failed to Get/Create an instance of the Excel Application. Exiting.", , "")
Exit Sub
End Try
And if you are just doing something simple from an iLogic rule, you could just use the built-in 'GoExcel' tools, shown within the iLogic Rule Editor's Snippets > System tab > Excel Data Links group.
As far as the empty cell issue, that can be annoying, but can be dealt with. You can either attempt to check the cell's value, without specifying expected data type, or you can use Cast (direct data type conversion) methods to ensure that the returned value is being understood as the data type you are expecting. Cell values can be many types of data, therefore the 'Value' is defined as Variant (or Object) instead of a specific data type. Therefore it can be a little tricky when attempting to set a cell's value directly to a variable of a certain type. If you are expecting the value to be a String type data, you can have a String type variable, then when attempting to set its value from the oCells.Item().Value you can use the oPName = CStr(oCells.Item().Value), which will attempt to directly convert the cell's value to a String type before setting it as the value of the variable. Another convenient tool to check for an empty cell is something like this:
If String.IsNullOrEmpty(oCells.Item().Value) Then
'or
If Not String.IsNullOrEmpty(oCells.Item().Value) Then
If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click (LIKE or KUDOS)
.
If you want and have time, I would appreciate your Vote(s) for My IDEAS
or you can Explore My CONTRIBUTIONS
Wesley Crihfield
(Not an Autodesk Employee)