Managing/Manipulating Data Pulled from Excel

[Phylum]

I'm having a really difficult time wrapping my head around what is probably a very easy concept.

I've got an Excel file that contains 4 colums and 4 rows, the first being a header row. (In reality the columns will remain the same but the rows will grow.)

The objective is to poll machines for data then cross reference the polled data with what's in column A or column B. If there's a match, I need to use data from columns C & D. I think some type of array (multidimensional perhaps) is what will suite my needs, but I'm having a hard time thinking this through and putting it all together.

I found most of this code thanks to Google but I've made adjustments here and there to suit my needs.

REM the Excel Application
Dim objExcel
REM the path to the excel file
Dim excelPath
REM how many worksheets are in the current excel file
Dim worksheetCount
Dim counter
REM the worksheet we are currently getting data from
Dim currentWorkSheet
REM the number of columns in the current worksheet that have data in them
Dim usedColumnsCount
REM the number of rows in the current worksheet that have data in them
Dim usedRowsCount
Dim row
Dim column
REM the topmost row in the current worksheet that has data in it
Dim topmostrow
REM the leftmost column in the current worksheet that has data in it
Dim leftmostcolumn
Dim Cells
REM the current row and column of the current worksheet we are reading
Dim curCol
Dim curRow
REM the value of the current row and column of the current worksheet we are reading
Dim word

REM where is the Excel file located?
excelPath = "C:\OldNew.xlsx"

WScript.Echo "Reading Data from " & excelPath

REM Create an invisible version of Excel
Set objExcel = CreateObject("Excel.Application")

REM don't display any messages about documents needing to be converted
REM from old Excel file formats
objExcel.DisplayAlerts = 0

REM open the excel document as read-only
REM open (path, confirmconversions, readonly)
objExcel.Workbooks.open excelPath, false, true


REM How many worksheets are in this Excel documents
'worksheetCount = objExcel.Worksheets.Count
worksheetCount = 1 'it will only have one.
'WScript.Echo "We have " & workSheetCount & " worksheets"

REM Loop through each worksheet
For counter = 1 to worksheetCount
'WScript.Echo "-----------------------------------------------"
'WScript.Echo "Reading data from worksheet " & counter & vbCRLF

Set currentWorkSheet = objExcel.ActiveWorkbook.Worksheets(counter)
REM how many columns are used in the current worksheet
'usedColumnsCount = currentWorkSheet.UsedRange.Columns.Count
usedColumnsCount = 4
REM how many rows are used in the current worksheet
usedRowsCount = currentWorkSheet.UsedRange.Rows.Count
'usedRowsCount = 4

REM What is the topmost row in the spreadsheet that has data in it
'topmostrow = currentWorkSheet.UsedRange.Row
topmostrow = 2 'because row1 is a header row
REM What is the leftmost column in the spreadsheet that has data in it
leftmostcolumn = currentWorkSheet.UsedRange.Column
leftmostcolumn = 1

Set Cells = currentWorkSheet.Cells
' Cells(Row,Column)
Dim i, j, random,
For i=2 To usedRowsCount
For j=1 To usedColumnsCount
WScript.Echo Cells(i,j)
'random = random + Cells(i,j) & vbTab
Next
Next
Next
WScript.Quit

So to help visualize a bit, my Excel 'database' looks like this:
____________________________________
|COLUMNA|COLUMMB|COLUMNC|COLUMND|
|data1 a |data1 b |data1 c |data1 d |
|data2 a |data2 b |data2 c |data2 d |
|data3 a |data3 b |data3 c |data3 d |
|data4 a |data4 b |data4 c |data4 d |
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Conceptually I'm thinking an array that sort of matches what I have in Excel, excluding the columns like
data_array(_
data1 a,data1 b,data1 c, data1 d, _
data2 a,data2 b,data2 c, data2 d, _
etc _
)

That way I can do things like
If (InStr(ucase(polled_data), ucase(data_array(i))) then
XLdata = data_array(i+2) & "," data_array(i+3)
end if

The part I'm struggling with is the For loops and getting the data I want together, together. (1 Row houses relevant data for that Row and no others)

Also, this seems sluggish to me and I'm guessing its because I'm using the Excel object. Would it be better to have VB pull from a CSV instead?

 

[tsuji]

[0]
[tt] For i=2 To usedRowsCount
[blue]XLdata=""
For j=1 To 2
if instr(1,polled_data, Cells(i,j),1)<>0 then
XLdata=Cells(i,3) & "," & Cells(i,4)
exit for
end if
Next
'do thing with XLdata here. XLdata is a function of i[/blue]
Next
[/tt]

[1] If you use instr() which means polled_data appeared as a substring particle of either Cells(i,1) or Cells(i,2), you construct your XLdata, if none of them contains a substring particle equal to polled_data, you have an XLdata empty.

[2] If you mean actually polled_data (case-insensitive) match exactly either Cells(i,1) or Cells(i,2), then you replace the condition by this;
[tt] [blue]if strcomp(polled_data, Cells(i,j),1)<>0 then[/blue][/tt]

 

[tsuji]

amendment
[2.1] the corresponding line is meant to read:
[tt] if strcomp(polled_data, Cells(i,j),1)[red]=[/red]0 then [/tt]

 

[tsuji]

amendment-2
[1.1] should be read properly like this (inversing the rolls)
[1-amended] If you use instr() which means polled_data [red]containing[/red] as a substring particle of either Cells(i,1) or Cells(i,2), you construct your XLdata, if none of them contains a substring particle equal to polled_data, you have an XLdata empty.

[1.2] If the literal statement made in [1] is what you're after, the conditional line should then be read like this.
[tt] if instr(1,[red]Cells(i,j),polled_data[/red],1)<>0 then[/tt]