GoExcel - Retrieve one or more values from a Spreadsheet as a list

[tkSHE78]

Does anybody know how to retrieve one or more values from a Spreadsheet as a list?

This code gets the first value: "FA87"

		GearSupport = GoExcel.FindRow("P:\Inventor_Support\Design Configurations\Screw Conveyor Components.xlsx", "Gear Support", "Size", "=", "300", "Drive End", "=", "Pull")
		GearType = GoExcel.CurrentRowValue("Gear Type")

I am looking for a method to get the match(es) as a list like this:

FA87, KA87-Pos.A, KA87-Pos.B, FA97, KA97-Pos.A, KA97-Pos.B

Thomas

[Michael.Navara]

There is not a direct way, because method FindRow returns single integer row index. You need to find appropriate values yourself. Here is a sample code based on your screenshot

GoExcel.Open("C:\Path\To\Screw Conveyor Components.xlsx", "Gear Support")
Dim a1 = GoExcel.CellValue("A1") 'Init excel worksheet

Dim size = "300"
Dim driveEnd = "Pull"

Dim sheetValues As Object(, ) = GoExcel.Application.ActiveSheet.UsedRange.Value2
Dim result As New List(Of String)
For row = sheetValues.GetLowerBound(0) To sheetValues.GetUpperBound(0)
	If sheetValues(row, 1).ToString() = size AndAlso sheetValues(row, 2).ToString() = driveEnd Then
		result.Add(sheetValues(row, 3))
	End If
Next

'Print result
For Each item In result
	Logger.Debug(item)
Next

[tkSHE78]

Hi  Michael,

For some strange reason it will not work.

The spreadsheet I am using is now attached.

Do you have time for testing with my spreadsheet?

If it works for you, then I might be doing something wrong.

/Thomas

Inventor 2022 SP5.2

[Michael.Navara · Accepted solution]

You are right. If you have more than one sheet and the requested one is not the active one, the code requires small update.

Dim xlsFileName As String = "C:\Path\To\Screw Conveyor Components.xlsx"
Dim xlsSheetName As String = "Gear Support"

GoExcel.Open(xlsFileName, xlsSheetName)

Dim size = "300"
Dim driveEnd = "Pull"

Dim sheetValues As Object(,) = GoExcel.Application.Worksheets(xlsSheetName).UsedRange.Value2
Dim result As New List(Of String)
For row = sheetValues.GetLowerBound(0) To sheetValues.GetUpperBound(0)
    If sheetValues(row, 1).ToString() = size AndAlso sheetValues(row, 2).ToString() = driveEnd Then
        result.Add(sheetValues(row, 3))
    End If
Next

'Print result
For Each item In result
    Logger.Debug(item)
Next

[tkSHE78]

Thanks  Michael,

That update made the difference 🙂

/Thomas