- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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)
Or if this helped you, please, click (like)
Regards
Alan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Some items I can see incorrect are the following
- The partnumber variable was not been set but rather the excel value was been set. This has been reversed.
- 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
Or if this helped you, please, click (like)
Regards
Alan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
hi @A.Acheson
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
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")
Or if this helped you, please, click (like)
Regards
Alan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report