I want to make a vlookup code of Excel using iLogic.

[jthzzang1KQJ5G]

I want to find the same value in the file name and row A of Excel, read the value in the same column, and create a new file with that value.

I've learned what I've made through the forum, but I'm still a beginner. Please help me learn more. I am attaching my samples.

Dim ExportPath As String = "C:\"

Dim Dialog = New FolderBrowserDialog()

Dialog.SelectedPath = ExportPath

Dialog.ShowNewFolderButton = True

Dialog.Description = "복사할 폴더를 선택해주세요."

If DialogResult.OK = Dialog.ShowDialog() Then

        ' User clicked 'ok' on dialog box - capture the export path

        ExportPath = Dialog.SelectedPath & "\"

End If

Dim Path As String = ExportPath

InvDoc = ThisDoc.Document

Dim refDocs As DocumentsEnumerator = InvDoc.AllReferencedDocuments

Dim refDoc As Document	

For Each refDoc In refDocs
	
    MessageBox.Show(refDoc.DisplayName)
	
Dim NewPN_1 As Object

GoExcel.Open("C:\Users\211215\Desktop\TEST\TEST.xlsx", "sheet1")
Dim oDocNumber As String = ThisDoc.FileName(False)

RowStart = 
RowEnd = 100
'Find the first empty cell
For countA = RowStart To (RowEnd * 0.01) + RowStart
    'If it's not blank jump to cell + 100. E.g. search cell A2 and then A102 etc.
    If Not String.IsNullOrEmpty(GoExcel.CellValue("A" & RowEnd)) Then
        'Add another 100 to searched cells
        RowEnd = RowEnd + 100
    Else
        'Else if it's empty exit loop
        Exit For
    End If
Next

For oRow = RowStart To RowEnd
	
	If (GoExcel.CellValue("A" & oRow)) = oDocNumber Then
		
	GoExcel.CellValue("B" & oRow) = NewPN_1
	
	ThisDoc.Document.SaveAs(Path & NewPN_1 & ".ipt", False)
	
	End If
Next

Next

Dim PN_AssyM As String = GoExcel.CellValue("TEST.xlsx", "sheet1", "C1")

ThisDoc.Document.SaveAs(Path & PN_AssyM & ".iam", False)

iLogicVb.UpdateWhenDone = True

[A.Acheson]

Hi @jthzzang1KQJ5G 

Can you indicate a few areas where your having difficulty? Any error messages, missing functionality etc?

I see in one area you are using an ilogic API snippet that will only work from the document the rule is launched from in this case the assembly.

Dim oDocNumber As String = ThisDoc.FileName(False)

If you need to get the filename you can use IO.Path.GetFileNameWithoutExtension() see help page here

To get filename of referenced document using System.IO methods. 

Dim fileName as String = IO.Path.GetFileNameWithoutExtension(refDoc.FullFileName)

[jthzzang1KQJ5G]

I'm a beginner who just started logic, so the description may not be correct. Please excuse me.
If the value of row A and the oDocNumer value are the same, this is a sentence that takes the value from row B in the same column as row A read from Excel, specifies the value in NewPN_1, specifies a new filename in the specified NewPN_1, and saves it as a different name.
This sentence is not normal. I can take great courage from your response.

For oRow = RowStart To RowEnd
	
	If (GoExcel.CellValue("A" & oRow)) = oDocNumber Then
		
	GoExcel.CellValue("B" & oRow) = NewPN_1
	
	ThisDoc.Document.SaveAs(Path & NewPN_1 & ".ipt", False)
	
	End If
Next

[A.Acheson]

Some items I can see incorrect are the following

1. The partnumber variable was not been set but rather the excel value was been set. This has been reversed. 
2. ThisDoc.Document will be incorrect as it will reference the assembly. Use refDoc object if looping through assembly parts. 

Not shown but if your not filtering out assemblies in your referenced documents then you will get errors. Also if any of your parts are within sub assemblies then you might also get errors. Please share the whole code if you have further issues. 

For oRow = RowStart To RowEnd
	
	If (GoExcel.CellValue("A" & oRow)) = oDocNumber Then
		
       NewPN_1 = GoExcel.CellValue("B" & oRow) 
	
	   refDoc.SaveAs(Path & NewPN_1 & ".ipt", False)
	
	End If
Next

[jthzzang1KQJ5G]

hi @A.Acheson 

Thank you for your reply.

I have continuously learned through communit and help. And I am trying to solve some codes by changing them. However, I succeeded in getting "Existing Item Name" and "Change Item Name" from Excel, but I failed to change it by applying it in order. The following is the code that I changed through learning. Please give me various opinions.

Imports System.Windows.Forms

' Excel 파일 열기 및 데이터 처리
Sub Main()
    ' Excel 파일 열기
    GoExcel.Open("TEST.xlsx", "Sheet1")

    ' 기존품명과 변경품명을 저장할 List 선언
    Dim i As Integer = 2
    Dim oldNameList, newNameList As New List(Of String)

    ' 기존품명 열의 Excel 열 이름 가져오기
    oldCol = GetExcelColumnName(GoExcel.FindColumn("기존품명"))
    ' 변경품명 열의 Excel 열 이름 가져오기
    newCol = Chr(Asc(oldCol) + 1)

    ' Excel에서 데이터 읽어오기
    While GoExcel.CellValue("TEST.xlsx", "Sheet1", oldCol & i) > ""
        ' 기존품명과 변경품명 데이터를 List에 추가
        oldNameList.Add(GoExcel.CellValue("TEST.xlsx", "Sheet1", oldCol & i))
        newNameList.Add(GoExcel.CellValue("TEST.xlsx", "Sheet1", newCol & i))
        i += 1
    End While

    ' 사용자에게 폴더 선택 받기
    Dim ExportPath As String = ""

    Using Dialog As New FolderBrowserDialog()
        Dialog.SelectedPath = "C:\" ' 기본 경로 설정
        Dialog.ShowNewFolderButton = True
        Dialog.Description = "Choose Folder for Export..."

        ' 폴더 선택 대화상자 표시
        If DialogResult.OK = Dialog.ShowDialog() Then
            ExportPath = Dialog.SelectedPath & "\"
        Else
            Return ' 취소되었을 때 처리
        End If
    End Using

    ' Inventor 문서 및 참조 문서 가져오기
    Dim InvDoc As Document = ThisDoc.Document
    Dim refDocs As DocumentsEnumerator = InvDoc.AllReferencedDocuments

    ' 각 부품의 이름을 변경하여 저장
    For Each refDoc As Document In refDocs
        For j As Integer = 0 To oldNameList.Count - 1
            ' 부품 이름 확인 및 변경 후 저장
            If refDoc.DisplayName.EndsWith(oldNameList(j) & ".ipt") Then
                refDoc.SaveAs(ExportPath & newNameList(j) & ".ipt", False)
            End If
        Next
    Next

    ' Excel 파일 닫기
    GoExcel.Close()

    ' 처리된 파일 수를 메시지 박스로 표시
    MessageBox.Show(oldNameList.Count.ToString + " file(s) Complete!")
End Sub

' 열 번호를 Excel 열 이름으로 변환하는 함수
Function GetExcelColumnName(columnNumber As Integer) As String
    Dim dividend As Integer = columnNumber
    Dim columnName As String = String.Empty
    Dim modulo As Integer

    While dividend > 0
        modulo = (dividend - 1) Mod 26
        columnName = Convert.ToChar(65 + modulo).ToString() & columnName
        dividend = CInt((dividend - modulo) / 26)
    End While

    Return columnName
End Function

[A.Acheson · Accepted solution]

Hi @jthzzang1KQJ5G 

Working with the index of the referenced documents will be the wrong approach. There is no way to sync up the excel row index and the reference document index.

What will work is to get the name of the referenced document then search the excel document for the existing name, record its excel row number then index over one column and save as with the new name. 

There is a handy function for this within go excel. Help page shown here.

i = GoExcel.FindRow("mytable.xls", "Sheet1", "Dia", "=", 0.2)

Once you find the row you can get the column value of the existing row by using this function or by manually indexing using cell value.

Dim newName as string = GoExcel.CurrentRowValue("column name")

[jthzzang1KQJ5G]

Thank you for your help.