Message 1 of 9
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Hello Experts,
I'm writing a rule to replace missing references and outdated old content center parts.
I have an excel file to get the path of the new valid contend center part filename with the connection to the old filename. They are on separate sheets of the excel file. The connection is the Material Number.
If found this code to use excel without the ilogic goexcel because on my computer (and others) there are always problems with the COM interface of excel.
How can i search the old filename and get the new filename on different sheets of the excel by using functions and subs see code below?
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 Solved! Go to Solution.
