Loading data from the .xlsx table into the table

[ProRuzneDrobnosti]

Hello everyone, is it possible to modify this code (received from FINET_Laurent) so that the data is written to an table (General ) on the Inventor drawing page. Not in Titleblock

Thank you very much for your reply

Sub main
	Dim excelTable As Dictionary(Of String, String) = get_inputs
	
	Dim doc As Inventor.DrawingDocument = ThisApplication.ActiveDocument
	Dim titleblock As Inventor.TitleBlock = doc.ActiveSheet.TitleBlock
	Dim s As Inventor.DrawingSketch = titleblock.Definition.Sketch
	
	For Each t As Inventor.TextBox In s.TextBoxes
			For Each k As String In excelTable.Keys
				If t.Text = "<" & k & ">" Then
					titleblock.SetPromptResultText(t, excelTable(k))
					Exit For
					
				End If
			Next
	Next
	
End Sub

Function get_inputs() As Dictionary(Of String, String)
	Dim propertiesColumn As String = "A"
	Dim valuesColumn As String = "B"
	Dim firstLine As Integer = 1
	Dim lastLine As Integer = 3
	
	Dim excelTable As New Dictionary(Of String, String)

	For i As Byte = firstLine To lastLine
		Dim p As String = GoExcel.CellValue("TitleBlock.xlsx", "Tabelle1", propertiesColumn & i)
		Dim v As String = GoExcel.CellValue("TitleBlock.xlsx", "Tabelle1", valuesColumn & i)
		excelTable.Add(p, v)
		
	Next

	GoExcel.Close
	Return excelTable

End Function

[Michael.Navara]

It requires different approach, but it is possible. This sample requires valid XLS file.

Sub Main

    Dim drw As DrawingDocument = ThisDoc.Document
    Dim sheet As Sheet = drw.ActiveSheet
    Dim table As CustomTable = sheet.CustomTables(1)

    Dim xlsFileName = "C:\Path\To\TableContent.xlsx"
    Dim xlsSheet = "Sheet1"

    GoExcel.Open(xlsFileName, xlsSheet)
    
    For rowIndex As Integer = 1 To table.Rows.Count
        For colIndex As Integer = 1 To table.Columns.Count
            table.Rows(rowIndex)(colIndex).Value = GetValue(rowIndex, colIndex)
        Next

    Next

    GoExcel.Close()
End Sub

Function GetValue(rowIndex As Integer, colIndex As Integer) As String
    Dim cellAddress As String = Char.ConvertFromUtf32(64+colIndex) & rowIndex
    Dim cellValue As Object = GoExcel.CellValue(cellAddress)
    If cellValue Is Nothing Then Return ""
    Return cellValue
End Function

[ProRuzneDrobnosti]

Thank you very much, everything works! Allow me to raise the difficulty level? Is it possible to find the name of the component shown on the drawing page in the XLS table and fill in the data from the corresponding XLS line. Thank you very much!

[Michael.Navara]

It is possible. Can you upload some testing data (Source XLS, drawing with model, ...)?

[ProRuzneDrobnosti]

Here is a test project with a drawing and an xls file I hope the project will work for you. Thank you!

[ProRuzneDrobnosti]

Hello, I want to ask if the project and the table I sent are working? Thanks for the help

[Michael.Navara · Accepted solution]

This is he modified code for filling data to fake parts list. But I recommend you to use standard PartsList and iProperties instead

Sub Main()

    Dim drw As DrawingDocument = ThisDoc.Document
    Dim sheet As Sheet = drw.ActiveSheet
    Dim table As CustomTable = sheet.CustomTables(1)
    Dim document as Document = sheet.DrawingViews(1).ReferencedDocumentDescriptor.ReferencedDocument

    Dim xlsFileName = "C:\Path\To\Table.xlsx"
    Dim xlsSheet = "Sheet1"

    'Init Excel
    GoExcel.Open(xlsFileName, xlsSheet)
    dim a1 = GoExcel.CellValue("A1")

    'Load data from UsedRange
    'See Excel API reference for more information
    Dim excelApp = GoExcel.Application
    Dim workSheet = excelApp.ActiveSheet
    value2 = workSheet.UsedRange.Value2

    'Get referenced model part number
    Dim partNumber = document.PropertySets("{32853F0F-3444-11D1-9E93-0060B03C1CA6}")("Part Number").Value.ToString()
    'Find row data
    Dim xlsRow = FindRow(partNumber)

    'Set table values
    For i As Integer = 0 To 3
        table.Rows(2)(i+1).Value = xlsRow(i)
    Next

    GoExcel.Close()
End Sub

Dim value2 As Object(,)

Function FindRow(partNumber As String) As Object()
    For r As Integer = 1 To value2.GetUpperBound(0) 
        If value2(r, 1) <> partNumber Then Continue For
        Dim result As Object() = {
                                    value2(r, 1),
                                    value2(r, 5),
                                    value2(r, 7),
                                    "???"
                                }
        Return result
    Next
    Return new Object(){"","","",""}

End Function

[ProRuzneDrobnosti]

This is great! Thank you very much. The fact is that you have to work with tables in which the data often changes (not the parameters of the part) In the drawings, which are sometimes quite large, everything will have to be changed manually. Or forget 🙂