Getting data from a spreadsheet with 2 dimension array

[steveh3]

Hello Gang...

I have a spreadsheet which has two dimensions OD and BC.

For the DimC, I would like to gather all the rows where OD is greater than or equal to the DimC value chosen in the form (grey hi-lited cells).

Then also do the same for the BC. Select all rows where BC is greater than or equal to MaxBC (yellow hi-lited cells).

Then the result for AvailableDiscs would be the intersection of the two sets (green hi-lited). This would then show up in the form as what would be available to chose. 

I have the form and spreadsheet set up, but I can't figure out how to dynamically update the Listing of AvailableDiscs.

I'm assuming this is possible?

I have included the .ipt and spreadsheet as attachments.

Thanks in advance,

Steve H.

[WCrihfield · Accepted solution]

Hi @steveh3.  I didn't have time to create a complete solution for you today, but I did create something to get you further along in your project.  It is difficult to get data from Excel in a 2-dimensional array using iLogic's built-in GoExcel functions, so I opted for digging into Excel's API for this one.  This will get the Excel data to a 2D array for you.  Then just to show you that it worked, I included a message at the end that shows you the values across the last line of data.  The comparisons and filtering can be added later.

This is the code I have for you right now.

AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Sub Main
	Dim oFile As String = "C:\Temp\Wheel_database.xlsx"
	Dim oSheet As String = "FormedPlates"
	'start a new instance of Excel
	Dim oExcel As New Excel.Application
	oExcel.DisplayAlerts = False
	oExcel.Visible = True
	Dim oWB As Workbook = oExcel.Workbooks.Open(oFile)
	Dim oWS As Worksheet = oWB.Sheets.Item(oSheet)
	'specify the range of cells in that sheet you want to read into memory
	Dim oRange As Range = oWS.Range("A1:D32")
	'create 2-dimensional array of Object, and set the range's values into it
	Dim oArray(, ) As Object = oRange.Value
	'read from an array by specifying row, then column, both starting with 1
	Dim oLastRowData As String
	oLastRowData = oArray(32, 1).ToString
	oLastRowData = oLastRowData & " <> " & oArray(32, 2).ToString
	oLastRowData = oLastRowData & " <> " & oArray(32, 3).ToString
	oLastRowData = oLastRowData & " <> " & oArray(32, 4).ToString
	MsgBox("oLastRowData = " & oLastRowData,,"")
	
	oWS = Nothing
	oWB.Close
	oWB = Nothing
	'oExcel.Quit
	oExcel = Nothing
End Sub

[tyler.warner · Accepted solution]

@steveh3 I made some adjustments to the Excel sheet to do some of the calculations for you. The form in the part file now updates to the available options, by selecting your two parameter inputs at the top of the form & then clicking the update button. The only thing that you will need to change to test this out is:

DiscSelector.ipt --> ParametersChosen Rule --> oExcelFileName (the correct path to your excel file)

See if this is what you're looking for.

[steveh3]

Thanks Gentlemen....

I was able to take your logic and it worked.

Appreciate the help!

Steve H.