- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Ilogic fails to talk to Excel
Hi,
I am using Inventor 2016, Excel 2013, windows 7 64 bit.
I have very simple iLogic rule:
iwood_supp_thk=GoExcel.CellValue("cc_data.xls", "Sheet1", "wood_supp_thk").
It works well for all other machines. But it fails in one particular machine. The message says:
Error in rule: block4_scenario3, in document: block4_scenario3.ipt
Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Application'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D5-0000-0000-C000-000000000046}' failed due to the following error: Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)).
Looks like the iLogic does not talk to Excel correctly. I re-install Inventor and Excel a couple of times, and use the sequence of installing Office first then Inventor secondly.
Please see attached file for the error message.
What is it? Can somebody help?
Thank you very much!
Limin
Limin
Inventor pro 2023 64 bit update 2.1; Windows 10 pro 64 bit version 21H2; Office 2013 32 bit
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Hello @liminma8458,
I will ask the forum moderator to move this post to the Inventor Customization Forum where this question is better served.
Best Regards,
Don
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Hello,
I am running into the same issue, I wrote an iLogic rule using Inventor 2015 w/ excel 2010 which worked without any issues. Upgraded Inventor to 2016 (Excel still 2010) and now Inventor can't seem to talk to Excel. Help would be greatly appreciated.
Thanks,
'Funnybus
Unable to cast COM object of type 'Microsoft.Office.Interop.Excel.ApplicationClass' to interface type 'Microsoft.Office.Interop.Excel._Application'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D5-0000-0000-C000-000000000046}' failed due to the following error: Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)).
SyntaxEditor Code Snippet
oExcelPath = "G:\FSH_Engineering\ENGINEERING DRAWINGS\_DRAWING TEMPLATES\_INVENTOR\_START PARTS\UTILITY\GAUGE.xlsx" Dim oListGauge As New ArrayList Dim oListThick As New ArrayList Dim oListHolder As String Dim i As Integer For i = 1 To 50 Step 1 Dim oValGauge As String = GoExcel.CellValue(oExcelPath, "Sheet1", "A" & i) Dim oValThick As Double = GoExcel.CellValue(oExcelPath, "Sheet1", "B" & i) Dim sValThick As String = oValThick.ToString("0.0000") If oValThick = 0 Then 'do nothing Else oListGauge.Add(oValGauge) oListThick.Add(sValThick) oListHolder = oListHolder & sValThick & " // " & oValGauge & vbLf End If Next 'MessageBox.Show(oListHolder, "List of Gauge & Thickness") MultiValue.List("GAUGE") = oListGauge MultiValue.List("THICKNESS_GAUGE") = oListThick
Fails at first instance of GoExcel.CellValue
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Try this in a rule and report back to how it runs:
AddReference "Microsoft.Office.Interop.Excel" 'To use excel
Sub Main()
oExcelPath = "G:\FSH_Engineering\ENGINEERING DRAWINGS\_DRAWING TEMPLATES\_INVENTOR\_START PARTS\UTILITY\GAUGE.xlsx"
oSheetName = "Sheet1"
Dim xlApp As Microsoft.Office.Interop.Excel.Application
xlApp = GetObject("", "Excel.Application")
Dim xlwb As Microsoft.Office.Interop.Excel.WorkBook
xlwb = xlApp.Workbooks.Open(oExcelPath)
Dim xlws As Microsoft.Office.Interop.Excel.Worksheet
xlws = xlwb.Worksheets(oSheetName)
MsgBox("Cell A1 is: " & xlws.Cells(1, "A").Value)
xlwb.Close(SaveChanges:= False)
xlApp.Quit
xlApp = Nothing
End Sub
--------------------------------------
Did you find this reply helpful ? If so please use the 'Accept as Solution' or 'Like' button below.
Inventor 2018.2.3, Build 227 | Excel 2013+ VBA
ERP/CAD Communication | Custom Scripting
Machine Design | Process Optimization
iLogic/Inventor API: Autodesk Online Help | API Shortcut In Google Chrome | iLogic API Documentation
Vb.Net/VBA Programming: MSDN | Stackoverflow | Excel Object Model
Inventor API/VBA/Vb.Net Learning Resources: Forum Thread
Sample Solutions:Debugging in iLogic ( and Batch PDF Export Sample ) | API HasSaveCopyAs Issues |
BOM Export & Column Reorder | Reorient Skewed Part | Add Internal Profile Dogbones |
Run iLogic From VBA | Batch File Renaming| Continuous Pick/Rename Objects
Local Help: %PUBLIC%\Documents\Autodesk\Inventor 2018\Local Help
Ideas: Dockable/Customizable Property Browser | Section Line API/Thread Feature in Assembly/PartsList API Static Cells | Fourth BOM Type
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Hey, it crashed due to an error in loading of the Excell interop library, so I don't think adding the reference manually will resolve this.
However you can still use Excel, even with broken library connection (thanks to the late binding). It's similar to what MechMachineMan suggested, except you won't cast the object to Excel types, but use them as objects.
Sub Main()
Dim oExcelPath As String = "G:\FSH_Engineering\ENGINEERING DRAWINGS\_DRAWING TEMPLATES\_INVENTOR\_START PARTS\UTILITY\GAUGE.xlsx"
Dim oExcel As Object = CreateObject("Excel.Application")
oExcel.Visible = False
oExcel.DisplayAlerts = False
Dim oWB As Object = oExcel.Workbooks.Open(oExcelPath)
Dim oWS As Object = oWB.Sheets(1) 'Sheet 1
oWS.Activate()
'In this sample, when you're getting a cell, the row is first
MsgBox("Cell A2 is: " & oWS.Cells(2, 1).Value)
'oWB.Save() 'Use this to save it
oWB.Close (True)
oExcel.Quit
oExcel = Nothing
End Sub
- - - - - - - - - - - - - - -
Regards,
Mike
"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live." - John F. Woods
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
This definitely worked. If you have time, I am very curious as to why GoExcel didn't work and casting it as an object did. Thanks!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
"GoExcel" just like "Microsoft.Office.Interop" uses the Office library file (DLL) to control Excel.
It looks into registers and gets the DLL location from there. Sometimes it may happen (somehow) the location stored in registers becomes invalid.
It might be due to poor un/instalation of the program or just some kind of error.
The sample I gave you "CreateObject("Excel.Application")" is also using registers, but it's not looking for the Interop library but rather directly at the registered Excel application. If you'll instal multiple Office versions the last runned one will be the one that will open using this code.
- - - - - - - - - - - - - - -
Regards,
Mike
"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live." - John F. Woods
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
I recall that after exhausting all other effort, I solved the problem by re-install windows 7 prof, then Office 2013 prof then Inventor 2016 prof for that particular machine. I recommend to keep software in your machine as lean as possible and do not install two versions of any of these program. Too many add on will complicate the communication between these 3 software.
Thanks
Limin
Inventor pro 2023 64 bit update 2.1; Windows 10 pro 64 bit version 21H2; Office 2013 32 bit
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
I Have a very similar problem with some ilogic that is used to refresh values from an Excel list.
Anyone know what command is needed to replace the GoExcel statement?
This is the code in question.
SyntaxEditor Code Snippet
MultiValue.List("myFinishExt") = GoExcel.CellValues("I:\Templates 2018\myFinishExt.xlsx", "Sheet1", "A2", "A100") MultiValue.List("myFinishInt") = GoExcel.CellValues("I:\Templates 2018\myFinishInt.xlsx", "Sheet1", "A2", "A100") MultiValue.List("myMaterial") = GoExcel.CellValues("I:\Templates 2018\myMaterial.xlsx", "Sheet1", "A2", "A100")
I've used the solution from Owner2229
which successfully calls Excel, but I'm not sure what to use in place of GoExcel. to get my values?
Sub Main() Dim oExcelPath As String = "I:\Templates 2018\myFinishExt.xlsx" Dim oExcel As Object = CreateObject("Excel.Application") oExcel.Visible = False oExcel.DisplayAlerts = False Dim oWB As Object = oExcel.Workbooks.Open(oExcelPath) Dim oWS As Object = oWB.Sheets(1) 'Sheet 1 oWS.Activate() 'In this sample, when you're getting a cell, the row is first MsgBox("Cell A2 is: " & oWS.Cells(2, 1).Value) 'oWB.Save() 'Use this to save it oWB.Close (True) oExcel.Quit oExcel = Nothing End Sub
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
You can use a function like this:
Sub Main()
MultiValue.List("myFinishExt") = ReadValues("I:\Templates 2018\myFinishExt.xlsx", 1, 1, 2, 100)
MultiValue.List("myFinishInt") = ReadValues("I:\Templates 2018\myFinishInt.xlsx", 1, 1, 2, 100)
MultiValue.List("myMaterial") = ReadValues("I:\Templates 2018\myMaterial.xlsx", 1, 1, 2, 100)
End Sub
Private Function ReadValues(sExcelPath As String, Sheet As Integer, Column As Integer, StartRow As Integer, EndRow As Integer) As String()
Dim R() As String
Try
Dim Range As Integer = Abs(EndRow - StartRow)
Redim R(Range)
Dim oExcel As Object = CreateObject("Excel.Application")
oExcel.Visible = False
oExcel.DisplayAlerts = False
Dim oWB As Object = oExcel.Workbooks.Open(sExcelPath)
Dim oWS As Object = oWB.Sheets(Sheet)
oWS.Activate()
Dim j As Integer = 0
For i As Integer = StartRow To EndRow
R(j) = oWS.Cells(i, Column).Value
j += 1
Next
oWB.Close (True)
oExcel.Quit()
oExcel = Nothing
Catch
End Try
Return R
End Function
- - - - - - - - - - - - - - -
Regards,
Mike
"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live." - John F. Woods
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Owner2229, thanks for your code however when running it errors with "Object reference not set to an instance of an object"
By adding messagebox comments it seems that the code run until the Catch statement?
Further assistance would be greatly appreciated
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Just an update on the iLogic GoExcel error, after AutoDesk Support remoted on to my m/c with TeamViewer, the ilogic error was cured!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Andy, what did they do to fix it? I am trying to fix this same issue...
I tried this but now when I try to open the file to edit it, excel just blinks on and off and I cannot do anything with the file:
Dim oExcelPath As String = "\\server1\Inventor\Job\Create Projects.xlsx" Dim oExcel As Object = CreateObject("Excel.Application") oExcel.Visible = False oExcel.DisplayAlerts = False Dim oWB As Object = oExcel.Workbooks.Open(oExcelPath) Dim oWS As Object = oWB.Sheets(1) 'Sheet 1 oWS.Activate() 'In this sample, when you're getting a cell, the row is first MsgBox("Cell A2 is: " & oWS.Cells(2, 1).Value) 'look at the rows A2 thru A50, adjust as needed 'note blank rows are ok 'Dim oList As New ArrayList 'oList = oWS.Cells(2, 1).Value '' Set a reference to the DesignProjectManager object. 'Dim oDesignProjectMgr As DesignProjectManager 'oDesignProjectMgr = ThisApplication.DesignProjectManager 'Dim oProject As DesignProject 'For Each oNumber In oList ' ' Create a new single user project ' oProject = oDesignProjectMgr.DesignProjects.Add(kSingleUserMode, _ ' oNumber, oPath & oNumber & "\") 'Next oWB.Close (True) oExcel.Quit oExcel = Nothing
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
--------------------------------------
Did you find this reply helpful ? If so please use the 'Accept as Solution' or 'Like' button below.
Inventor 2018.2.3, Build 227 | Excel 2013+ VBA
ERP/CAD Communication | Custom Scripting
Machine Design | Process Optimization
iLogic/Inventor API: Autodesk Online Help | API Shortcut In Google Chrome | iLogic API Documentation
Vb.Net/VBA Programming: MSDN | Stackoverflow | Excel Object Model
Inventor API/VBA/Vb.Net Learning Resources: Forum Thread
Sample Solutions:Debugging in iLogic ( and Batch PDF Export Sample ) | API HasSaveCopyAs Issues |
BOM Export & Column Reorder | Reorient Skewed Part | Add Internal Profile Dogbones |
Run iLogic From VBA | Batch File Renaming| Continuous Pick/Rename Objects
Local Help: %PUBLIC%\Documents\Autodesk\Inventor 2018\Local Help
Ideas: Dockable/Customizable Property Browser | Section Line API/Thread Feature in Assembly/PartsList API Static Cells | Fourth BOM Type
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
It does work but it still does the same thing. Seems like it is locking the file and not letting go of it or something.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Hi @chrisw01a,
You might check the Task Manager ( ctrl + alt + delete) > Details tab, and see if you see a bunch of EXCEL.EXE tasks listed. If so kill them.
Often when I'm working on something like this, as I hit errors in the iLogic testing it abandons invisible Excel files and leaves them open because the code errors out before being able to close them. Really, a proper error handler to catch the errors and close the file would be best.
In any case, I think I've seen the behavior you mentioned when the file is open invisibly in the background, but I can't recall for sure. It's something to rule out though.
I hope this helps.
Best of luck to you in all of your Inventor pursuits,
Curtis
http://inventortrenches.blogspot.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report