Problem with GoExcel.FindRow

[Anonymous]

Can't I really can't figure it out...

I made a code that goes and look in an excel file. It finds the Stock Number in the column Stock_Number and adds the description of said material from the column Description_Materiau. (I joined the .xlsx file)

We have three computer working on drawings on the same server and this code works on 2 out of 3 computer.

The first error that pops up on the computer is: 

Error in rule: DescriptionMateriau_iproperty_test, in document: TUNNEL.ipt

Exception from HRESULT: 0x800A03EC

and when you run the rule again it's:

GoExcel: Could not match the column title: "Stock_Number".

But if I paste the path of the .xlsx (the one used in the code) in the windows explorer of said computer, it will open. So the computer knows where to find the file and has access to it.

Here's the code:

SyntaxEditor Code Snippet

'set the target document
oDoc = ThisApplication.ActiveEditDocument

'Run juste si c'est un .ipt
If oDoc.DocumentType = kPartDocumentObject Then

    Dim propertyName As String = "Description_Materiau"
    Dim propertyValue As String = "Description Materiau"
    customPropertySet = ThisDoc.Document.PropertySets.Item("Inventor User Defined Properties")
    Try
    oProp = iProperties.Value("Custom",propertyName)
    Catch
    ' Assume error means not found
    customPropertySet.Add("", propertyName)
    iProperties.Value("Custom", propertyName) = propertyValue
    End Try
    
    Dim oPropSet2 As PropertySet
    oPropSet2 = oDoc.PropertySets.Item("Design Tracking Properties")
    SN = oPropSet2.Item("Stock Number").Value
    
    If SN = Nothing Then
    
        MessageBox.Show("Le num�ro de mat�riel n'est pas entr�", "Stock Number non-entr�")
        
    Else
        
        GoExcel.Open("\\192.168.7.10\dessins$\Inventor\iLogic\Liste_materiau.xlsx","Feuil1")
        GoExcel.TitleRow = 1
        
        'compare excel data
        If GoExcel.FindRow("\\192.168.7.10\dessins$\Inventor\iLogic\Liste_materiau.xlsx", "Feuil1", "Stock_Number", "=", SN)
        
            ' Get a reference to the PropertySets
            ' we're really only using Design Tracking Properties in this case though
            Dim oPropSet1 As PropertySet
            oPropSet1 = oDoc.PropertySets.Item("Inventor Summary Information")
            Dim oPropSet3 As PropertySet
            oPropSet3 = oDoc.PropertySets.Item("Inventor User Defined Properties")
            
            'set value to excel data
            iProperties.Value("Custom", propertyName) = GoExcel.CurrentRowValue(propertyName)
        
        'This last part doesn't work. It will put the description of row 2 if it does find the Stock Number in the list 
        'Ne marche pas... Pourquoi?
        Else 
        
            MessageBox.Show("Le num�ro de mat�riel n'existe pas dans la liste Excel. Ajouter le num�ro de mat�riel dans la liste ainsi que sa description et r�essayer.", "Description non-trouv�e")
        
        End If
        
    End If
    
End If





 

[Owner2229]

Here you go:

Dim oExcelPath As String = "\\192.168.7.10\dessins$\Inventor\iLogic\Liste_materiau.xlsx"
Dim oExcelSheet As String = "Feuil1"
Dim oDoc As Document = ThisApplication.ActiveDocument
'Run juste si c'est un .ipt
If oDoc.DocumentType <> kPartDocumentObject Then Exit Sub
Dim propName As String = "Description_Materiau"
Dim propValue As String = "Description Materiau"
'Get a reference to the PropertySets
'We're really only using Design Tracking Properties in this case though
Dim oPropSet1 As PropertySet = oDoc.PropertySets.Item("Inventor User Defined Properties")
Dim oPropSet2 As PropertySet = oDoc.PropertySets.Item("Design Tracking Properties")
Dim oPropSet3 As PropertySet = oDoc.PropertySets.Item("Inventor Summary Information")
Dim oProp As Inventor.Property
Try
	oProp = oPropSet1(propName)
Catch
	oProp = oPropSet1.Add("", propName)
End Try
oProp.Value = propValue

Dim SN As String = oPropSet2.Item("Stock Number").Value
If SN = vbNullString Then
	MessageBox.Show("Le num�ro de mat�riel n'est pas entr�", "Stock Number non-entr�")
Else
	GoExcel.Open(oExcelPath, oExcelSheet)
	GoExcel.TitleRow = 1
	'Get the Excel row
	Dim oRow As Integer = GoExcel.FindRow(oExcelPath, oExcelSheet, "Stock_Number", "=", SN)
	If oRow > 0 Then
		'Get value from the Excel cell
		oProp.Value = GoExcel.CurrentRowValue(propName)
	Else
		MessageBox.Show("Le num�ro de mat�riel n'existe pas dans la liste Excel. Ajouter le num�ro de mat�riel dans la liste ainsi que sa description et r�essayer.", "Description non-trouv�e")
	End If	
End If

[Anonymous]

Hi Owner2229, 

It did fix the part about not showing the last message box if the code wasn't in the list.

Thank you!

But it still doesn't work on my last computer. It works fine on the other two...

I'm cheking with the IT guy if it could be something on the server... But still I can open and edit the excel file on the computer so I really don't get why it wouldn't work...

Also note that the Excel file is in the external rule folder... So the computer definitely as access to it since it has access to all the external rules. This one being the only that doesn't work...

[Owner2229]

Let's try to handle the inaccessible file exception:

Anyway, does it pop up an error message or something?

The issue also might be in Excel. Does (or did) the last PC have an other version of Excel or Inventor installed or the current reinstalled?

If you're not sure, create a simple rule that'll open an Excel file, write to it, save it and close it:

GoExcel.Open("C:\Path\ExcelFile.xls", "Feuil1")
GoExcel.Cell("A1").Value = "something"
GoExcel.Save()
GoExcel.Close(True)

If so, we'll have to handle this a bit differently (without GoExcel).

Dim oExcelPath As String = "\\192.168.7.10\dessins$\Inventor\iLogic\Liste_materiau.xlsx"
If Not System.IO.File.Exists(oExcelPath) Then
	MessageBox.Show("Can't access the Excel file.", "Inaccessible file")
	Exit Sub
End If
Dim oExcelSheet As String = "Feuil1"
Dim oDoc As Document = ThisApplication.ActiveDocument
'Run juste si c'est un .ipt
If oDoc.DocumentType <> kPartDocumentObject Then Exit Sub
Dim propName As String = "Description_Materiau"
Dim propValue As String = "Description Materiau"
'Get a reference to the PropertySets
'We're really only using Design Tracking Properties in this case though
Dim oPropSet1 As PropertySet = oDoc.PropertySets.Item("Inventor User Defined Properties")
Dim oPropSet2 As PropertySet = oDoc.PropertySets.Item("Design Tracking Properties")
Dim oPropSet3 As PropertySet = oDoc.PropertySets.Item("Inventor Summary Information")
Dim oProp As Inventor.Property
Try
	oProp = oPropSet1(propName)
Catch
	oProp = oPropSet1.Add("", propName)
End Try
oProp.Value = propValue

Dim SN As String = oPropSet2.Item("Stock Number").Value
If SN = vbNullString Then
	MessageBox.Show("Le num�ro de mat�riel n'est pas entr�", "Stock Number non-entr�")
	Exit Sub
End If
GoExcel.Open(oExcelPath, oExcelSheet)
GoExcel.TitleRow = 1
'Get the Excel row
Dim oRow As Integer = GoExcel.FindRow(oExcelPath, oExcelSheet, "Stock_Number", "=", SN)
If oRow > 0 Then
	'Get value from the Excel cell
	oProp.Value = GoExcel.CurrentRowValue(propName)
Else
	MessageBox.Show("Le num�ro de mat�riel n'existe pas dans la liste Excel. Ajouter le num�ro de mat�riel dans la liste ainsi que sa description et r�essayer.", "Description non-trouv�e")
End If
GoExcel.Close(True)

[Anonymous]

All of the computer are running Excel 2007 and Inventor 2017... They can work on shared filed on both software without any problem.

I added this part to my code:

If Not System.IO.File.Exists(oExcelPath) 
Then MessageBox.Show("Can't access the Excel file.", "Inaccessible file") 
Exit Sub 
End If

But I still get the error:

GoExcel: Could not match the column title: "Stock_Number".

ButI tried to change the path to an inexisting and it does work on my computer...

It also won't work with: 

'I changed the path
GoExcel.Open("C:\Path\ExcelFile.xls", "Feuil1")
GoExcel.CellValue("C:\Path\ExcelFile.xls", "Feuil1","A1") = "something"
GoExcel.Save()
'Had to comment the last line out cause it doesn't open the file on either of the computer
'GoExcel.Close(True)

 I get the error:

Error in rule: DescriptionMateriau_iproperty_test, in document: RA764-011616S2.ipt

n’a pas pu être trouvé. (Exception from HRESULT: 0x80030002 (STG_E_FILENOTFOUND))

But it does work on my computer...

I also tried 

If Not System.IO.File.Exists("\\192.168.7.10\dessins$\Inventor\Design_2010\ExcelFile.xlsx") Then 
MessageBox.Show("Can't access the Excel file.", "Inaccessible file") 
Exit Sub 
End If

GoExcel.Open("\\192.168.7.10\dessins$\Inventor\Design_2010\ExcelFile.xlsx", "Feuil1")
GoExcel.CellValue("\\192.168.7.10\dessins$\Inventor\Design_2010\ExcelFile.xlsx", "Feuil1","A1") = "something"
GoExcel.Save()
'Had to comment the last line out cause it doesn't open the file on either of the computer'GoExcel.Close(True)

 And I get the same error as above:

Error in rule: DescriptionMateriau_iproperty_test, in document: RA764-011616S2.ipt

n’a pas pu être trouvé. (Exception from HRESULT: 0x80030002 (STG_E_FILENOTFOUND))

But it does work on my computer...

At this point my guess is that the problem is with Excel.

[Anonymous]

I just tried switching the file to .xls (excel 97-03) instead of .xlsx but it doesn't change anything. 

Still works on my computer and not on the other one.

[Anonymous]

Currently trying to go without the GoExcel.

Found this https://reinventinginventor.wordpress.com/2014/11/24/re-writing-the-goexcel-functions-for-use-in-ilo...

But I can't get it to work. This error pops on the line right before the Msgbox. 

SyntaxEditor Code Snippet

Sub Main
'Cr�e la custom iProperty Description Materiau et mets la description du materiau selon le stock number'Dim oExcelPath As String = "\\192.168.7.10\dessins$\Inventor\iLogic\Liste_materiau2.xls"'Dim oExcelSheet As String = "Feuil1"
Dim oDoc As Document = ThisApplication.ActiveDocument

Dim FullFileName As String = "\\192.168.7.10\dessins$\Inventor\iLogic\Liste_materiau2.xls"
Dim CurrentSheetName As String = "Feuil1"
Dim LookupColumnName As String = "Stock_Number"
Dim TitleRow As Long = 1 
 

excelApp = CreateObject("Excel.Application")
excelApp.DisplayAlerts = False

'Run juste si c'est un .ipt
If oDoc.DocumentType <> kPartDocumentObject Then Exit Sub
    
    Dim oPartcompdef as PartComponentDefinition = oDoc.ComponentDefinition
    'OPT pour BOMstructure:
    'Default = 51969
    'Normal = 51970
    'Phantom = 51971
    'Reference = 51972
    'Purchased = 51973
    'Inseparable = 51974
    'Varies = 51975
    'V�rifie le BOMstructure et ne fait rien si c'est Purchased
    If oPartcompdef.BOMStructure = 51973 Then Exit Sub
    
        Dim propName As String = "Description_Materiau"
        Dim propValue As String = "Description Materiau"
        
        'Get a reference to the PropertySets
        'We're really only using Design Tracking Properties in this case though
        Dim oPropSet1 As PropertySet = oDoc.PropertySets.Item("Inventor User Defined Properties")
        Dim oPropSet2 As PropertySet = oDoc.PropertySets.Item("Design Tracking Properties")
        Dim oPropSet3 As PropertySet = oDoc.PropertySets.Item("Inventor Summary Information")
        Dim oProp As Inventor.Property
        Try
            oProp = oPropSet1(propName)
        Catch
            oProp = oPropSet1.Add("", propName)
        End Try
        oProp.Value = propValue
        
        Dim SN As String = oPropSet2.Item("Stock Number").Value
        
        If SN = vbNullString Then
            MessageBox.Show("Le num�ro de mat�riau n'est pas entr�", "Stock Number non-entr�")
        Else
            'GoExcel.Open(oExcelPath, oExcelSheet)
            'GoExcel.TitleRow = 1
            
            'Get the Excel row
            'Dim oRow As Integer = GoExcel.FindRow(oExcelPath, oExcelSheet, "Stock_Number", "=", SN)
            Dim LookupString As String = SN
            i = LookupRowFromSpreadsheet(FullFileName, CurrentSheetName, LookupColumnName, TitleRow, LookupString, excelApp)
            MessageBox.Show("The bug is on the line before", "Bug")
            If oRow > 0 Then
                'Get value from the Excel cell
                oProp.Value = ParameterValueFromSpreadsheet("Stock Number", TitleRow, i, excelApp)
                'oProp.Value = GoExcel.CurrentRowValue(propName)
            Else
                oProp.Value = propValue
                MessageBox.Show("Le num�ro de mat�riau n'existe pas dans la liste Excel. Ajouter le num�ro de mat�riau dans la liste ainsi que sa description et r�essayer.", "Description non-trouv�e")
            End If    
            
        End If
End Sub        
        
Function LookupRowFromSpreadsheet (SpreadsheetFileName As String, SpreadSheetSheetName As String, SpreadsheetLookupColumnName As String, TitleRowNum as Integer, LookupValue As String, excelApp As Object) As Long
Dim LookupColumnNum As Long
LookupColumnNum = 0

Dim LookupRowNum As Integer
LookupRowNum = 0

Dim CurrentColumnNum As Integer
CurrentColumnNum = 0

excelWorkbook = excelApp.ActiveWorkbook
excelWorkbook.Sheets(SpreadSheetSheetName).Activate
excelSheet = excelApp.ActiveSheet

Dim LookUpColumnRange As Object

Try
LookupColumnNum = excelApp.WorksheetFunction.Match(SpreadsheetLookupColumnName, excelSheet.Rows(TitleRowNum),0)
Catch
MsgBox("Can't find the column " & Chr(34) & SpreadsheetLookupColumnName & Chr(34) & " in row " & TitleRowNum & " on sheet " & SpreadSheetSheetName)
excelWorkbook.Close (Savechanges:=False)
excelApp.Quit
excelApp = Nothing
Exit Function
End Try

LookUpColumnRange = excelSheet.Columns(LookupColumnNum)

Try
LookupRowFromSpreadsheet = excelApp.WorksheetFunction.Match(LookupValue, LookUpColumnRange,0)
Catch
MsgBox("Can't find " & Chr(34) & LookupValue & Chr(34) & " in the column titled: " & SpreadsheetLookupColumnName & Chr(34) & " On sheet " & SpreadSheetSheetName)
excelWorkbook.Close (Savechanges:=False)
excelApp.Quit
excelApp = Nothing
LookupRowFromSpreadsheet = 0
Exit Function
End Try
End Function

Function ParameterValueFromSpreadsheet (ParameterName As String, TitleRowNum as Integer, LookupRowNum As Long, excelApp As Object) As Object

Dim CurrentColumnNum As Long
CurrentColumnNum = 0

excelWorkbook = excelApp.ActiveWorkbook
excelSheet = excelApp.ActiveSheet

Try
CurrentColumnNum = excelApp.WorksheetFunction.Match(ParameterName, excelSheet.Rows(TitleRowNum),0)
Catch
MsgBox("Something has gone wrong. I was looking in this File: " & excelWorkbook.Name & vbWriteLine & " and in this Sheet: " & excelSheet.Name & "For this parameter: " & ParameterName )
Exit Function
End Try

If excelApp.WorksheetFunction.IsError(excelSheet.Cells(LookupRowNum,CurrentColumnNum)) = "True" Then ' Checks for "#N/A" in a field and alerts you to where it happened
MsgBox("Something has gone wrong. I was looking in this File: " & excelWorkbook.Name & " and in this Sheet: " & excelSheet.Name & " For this parameter: " & ParameterName )
Else
Dim dubTempParameterValue As Double
dubTempParameterValue = 0
Try
dubTempParameterValue = excelSheet.Cells(LookupRowNum,CurrentColumnNum).Value
ParameterValueFromSpreadsheet = dubTempParameterValue
Catch
'MsgBox(ParameterName & " is not a Double")
Dim strTempParameterValue As String
strTempParameterValue = CStr(excelSheet.Cells(LookupRowNum,CurrentColumnNum).Text)
ParameterValueFromSpreadsheet = strTempParameterValue
End Try
End If

End Function        

[MjDeck]

Can you post the part file from the computer that shows the problem? Or if the part is confidential, please post a simpler part file that produces the same error.

[Anonymous]

Every part on this computer produces the error... 

And the same part on other computer are working. Even if I run the rule with my computer on the part (and it works), it doesn't work on the third computer. It gets the column title error.

[MjDeck]

Which version of Inventor are you running?
Please try this test.

After this line:

GoExcel.Open(oExcelPath, oExcelSheet)

add the line

MessageBox.Show("Cell A1 = " + GoExcel.CellValue(oExcelPath, oExcelSheet, "A1"), "Test")

What does the message say?

[Anonymous]

Inventor 2017 and Excel 2007 

It doesn't work. Same error as when I tested writing something in an Excel file in post 5:

Error in rule: DescriptionMateriau_iproperty_test, in document: RA764-011616S2.ipt

n’a pas pu être trouvé. (Exception from HRESULT: 0x80030002 (STG_E_FILENOTFOUND))

Our IT guy will reinstall Excel on lunch time today. If this doesn't do it we will reinstall Inventor on the computer (Don't really wanna, don't feel like setting it up again)

[MjDeck]

Please try this test rule on a computer that doesn't have the problem and on the computer  that does:

Dim oExcelPath As String = "\\192.168.7.10\dessins$\Inventor\iLogic\Liste_materiau.xlsx"
If Not System.IO.File.Exists(oExcelPath) Then
	MessageBox.Show("Can't access the Excel file.", "Inaccessible file")
	Exit Sub
End If

Dim excelApp = CreateObject("Excel.Application")

Try
Dim workBook = excelApp.Workbooks.Open(oExcelPath)
Dim a1 = workBook.WorkSheets(1).Cells(1,1).Value.ToString()
MessageBox.Show("Cell A1 = " + a1, "Test")
Finally
excelApp.Quit()
End Try

[Anonymous]

Reinstalled Excel but it doesn't change anything.

Next step was gonna be to reinstall Invenor but I tried the code you sent an it works on both computer!

Seems like GoExcel doesn't work on this computer. What could be the cause? My guess is that reinstalling Inventor would fix it, am I right?

Now how would I do findrow using this technique?

[Owner2229 · Accepted solution]

Hey, sorry for late reply. This is the "other option" (without GoExcel) I spoke of.

It's an issue with Excel library. From my experience it occures on about 1/10 of computers.

Reinstaling Inventor wouldn't fix anything. The only way seems to be reinstaling whole system, and the issue might come back if you install new verion of Inventor or Excel.

The best way is probably using the Object Excel calling.

I already have the rest of the code reworked to use the Object Excel calling, but I've forgot it at work, so I'll send it to you tomorrow in the morning.

[MjDeck]

While you're waiting for the other Mike to provide a solution that doesn't use GoExcel, please try running the attached rule on both computers. It will show information about the Excel environment. Please post screenshots of the message boxes.

You can run this rule as an external rule, or copy and paste it into an empty part.

[Anonymous]

I attached the two printscreens.

Everything is exactly the same except it switched the two first row...

[MjDeck]

Thanks.
I've got an idea for a possible fix. If you can post the exact version of Inventor (from the Help > About dialog) on the computer with the problem, then I can post a patch for testing.

[Owner2229 · Accepted solution]

Here's the updated rule:

Sub Main()
Dim oExcelPath As String = "\\192.168.7.10\dessins$\Inventor\iLogic\Liste_materiau.xlsx"
If Not System.IO.File.Exists(oExcelPath) Then
	MessageBox.Show("Can't access the Excel file.", "Inaccessible file")
	Exit Sub
End If
Dim oExcelSheet As String = "Feuil1"
Dim oDoc As Document = ThisApplication.ActiveDocument
'Run juste si c'est un .ipt
If oDoc.DocumentType <> kPartDocumentObject Then Exit Sub
Dim propName As String = "Description_Materiau"
Dim propValue As String = "Description Materiau"
'Get a reference to the PropertySets
'We're really only using Design Tracking Properties in this case though
Dim oPropSet1 As PropertySet = oDoc.PropertySets.Item("Inventor User Defined Properties")
Dim oPropSet2 As PropertySet = oDoc.PropertySets.Item("Design Tracking Properties")
Dim oPropSet3 As PropertySet = oDoc.PropertySets.Item("Inventor Summary Information")
Dim oProp As Inventor.Property
Try
	oProp = oPropSet1(propName)
Catch
	oProp = oPropSet1.Add("", propName)
End Try
oProp.Value = propValue

Dim SN As String = oPropSet2.Item("Stock Number").Value
If SN = vbNullString Then
	MessageBox.Show("Le num�ro de mat�riel n'est pas entr�", "Stock Number non-entr�")
	Exit Sub
End If

'Start Excel
StartExcel(oExcelPath)
If oWS Is Nothing Then Exit Sub

'Get the column ID
Dim ColumnID As Integer = GetColumnID("Stock_Number")
If ColumnID = 0 Then
	MessageBox.Show("Column 'Stock_Number' was not found", "Wrong column name")
	StopExcel()
	Exit Sub
End If

'Get the Excel row
Dim RowID As Integer = GetRowID(ColumnID, SN)
ColumnID = GetColumnID(propName)
If RowID > 0 And ColumnID > 0 Then
	'Get value from the Excel cell
	oProp.Value = GetRowValue(ColumnID, RowID)
Else
	MessageBox.Show("Le num�ro de mat�riel n'existe pas dans la liste Excel. Ajouter le num�ro de mat�riel dans la liste ainsi que sa description et r�essayer.", "Description non-trouv�e")
End If

'Stop Excel
StopExcel()
End Sub

Private oExcel As Object = Nothing
Private oWB As Object = Nothing
Private oWS As Object = Nothing

Sub StartExcel(oExcelPath As String)
	Try
		oExcel = CreateObject("Excel.Application")
		oWB = oExcel.Workbooks.Open(oExcelPath)
		oWS = oWB.Sheets(1)
		oExcel.Visible = False
		oExcel.DisplayAlerts = False
		oWS.Activate
	Catch
		StopExcel()
	End Try
End Sub

Sub StopExcel()
	On Error Resume Next
	oWB.Close (True)
	oExcel.Quit()
End Sub

Function GetColumnID(oValue As String) As Integer
	If oWS Is Nothing Then Return 0
	Dim ID As Integer = 1
	Try
	While True
		Dim CV As String = oWS.Cells(1, ID).Value
		If CV = oValue Then Exit While
		If CV = vbNullString Then
			ID = 0
			Exit While
		End If
		ID = ID + 1
	End While
	Catch
	End Try
	Return ID
End Function

Function GetRowID(ColumnID As Integer, oValue As String) As Integer
	If oWS Is Nothing Then Return 0
	Dim ID As Integer = 1
	Try
	While True
		Dim CV As String = oWS.Cells(ID, ColumnID ).Value
		If CV = oValue Then Exit While
		If CV = vbNullString Then
			ID = 0
			Exit While
		End If
		ID = ID + 1
	End While
	Catch
	End Try
	Return ID
End Function

Function GetRowValue(ColumnID As Integer, RowID As String) As String
	If oWS Is Nothing Then Return vbNullString
	Dim ID As String = vbNullString
	Try
		ID = oWS.Cells(RowID, ColumnID ).Value
	Catch
	End Try
	Return ID
End Function

[Anonymous]

Thanks owner2229!

Gonna try this right away!

MjDeck the Inventor version is Build 199, Release: 2017.2.1- Date Wed 09/21/2016

On the other computer it's the same (and GoExcel works) and on my computer it's Build 196, Release: 2017 SP1 - Date Wed 07/13/2016 (and GoExcel works)

Thanks for your help!

[Anonymous]

Thanks a lot Owner2229 it works perfectly!