Changing script to use array.... (Excel) Topic is solved

[mstarr]

Hello and thanks in advance for the assistance. The code below does the following:

Opens excel (hidden)
searches for information
finds match and saves cell info in variable
closes excel

It works pretty well but it's slow. I have a feeling if the information was already in an array it would run much faster. Apparently I have a mental block against excel arrays because I just can't figure it out.

Code: Select all

oLinksDoc := ComObjCreate("Excel.Application")
Sleep, 200
oLinksDoc.Workbooks.Open(LinksFullPath)
oLinksDoc.Visible := False
Sleep, 200

	Loop, 1500
{	
CurrentLinkRow := A_index
Global LType := oLinksDoc.Range("E" CurrentLinkRow).Text
Global SType := oLinksDoc.Range("B" CurrentLinkRow).Text

;msgbox % LType " | " Type " | " SType " | " State

If (SType = State && LType = Type)
{
FoundLink := oLinksDoc.Range("C" CurrentLinkRow).Text
Global FoundLink := FoundLink
		oLinksDoc.ActiveWorkBook.Save()
		oLinksDoc.ActiveWorkBook.Close()
		oLinksDoc.Quit
		
;msgbox % FoundLink 
;WinWaitClose, ahk_class #32770
break
}

[FanaticGuru]

It works pretty well but it's slow. I have a feeling if the information was already in an array it would run much faster. Apparently I have a mental block against excel arrays because I just can't figure it out.

Here is an example of creating a SafeArray from a range and then getting information from the SafeArray.

Code: Select all

xlApp := ComObjActive("Excel.Application")
SafeArray := xlApp.Range("B1:E3").Value
MsgBox % SafeArray[2,3]	;  row 2, column 3 of the SafeArray
MaxRow := SafeArray.MaxIndex(1)
MaxCol := SafeArray.MaxIndex(2)
MsgBox % MaxRow "`t" MaxCol
Row := 1, Col := 1
while (Row <= MaxRow)
{
	while (Col <= MaxCol)
	{
		MsgBox % SafeArray[Row, Col++]
	}
	Col := 1, Row++
}

FG

[FanaticGuru]

You might find this much more useful then looping through all of a ranges cells to find a match.

Code: Select all

xlApp := ComObjActive("Excel.Application")
Formula = =MATCH("Fanatic"&"Guru",A1:A4&B1:B4,0)
FoundRow := xlApp.Evaluate(Formula)
FoundValue := xlApp.Cells(FoundRow , 3).Value ; column 3
MsgBox % "Found Row = " FoundRow "`nColumn 3 Value = " FoundValue

This uses the COM method "Evaluate" to then use an Excel formula utilizing the "Match" function.

Through Evaluate you can get the results of most formulas just as if you typed it into an Excel cell.

This will look in the A1:A4 range for "Fanatic" and the B1:B4 range for "Guru" and return the Row in which a Match is found for both. If no Match is found the Row will be a negative number.

Then once you know the row, you can then act on any column that you want using that row number.

FG

[mstarr]

Thanks so much! @FanaticGuru - The adapted code is below. Your second suggestion is way simpler, but wouldn't that require the excel doc to remain open when searching?

Also, is there a way to make the range dynamic based on the # of columns and rows? I messed around with it but couldnt find the right syntax.

Code: Select all

oLinksDoc := ComObjActive("Excel.Application")
Global SafeArray := oLinksDoc.Range("A1:E1500").Value  ;Puts Info into Array!!!
GLobal MaxRow := SafeArray.MaxIndex(1)
Global MaxCol := SafeArray.MaxIndex(2)
	oLinksDoc.ActiveWorkBook.Save()
	oLinksDoc.ActiveWorkBook.Close()
	oLinksDoc.Quit



;Then later in the code....



Row := 1, Col := 1
while (Row <= MaxRow)
{
	while (Col <= MaxCol)
	{
		
		if (SafeArray[A_index,2]=State && SafeArray[A_index,5]=Type)
		{
			Global FoundLink := SafeArray[A_index,3]
Break
		}
		
	}

	Col := 1, Row++

}
Msgbox % FoundLink

[FanaticGuru]

Thanks so much! @FanaticGuru - The adapted code is below. Your second suggestion is way simpler, but wouldn't that require the excel doc to remain open when searching?

Also, is there a way to make the range dynamic based on the # of columns and rows? I messed around with it but couldnt find the right syntax.

Code: Select all

oLinksDoc := ComObjActive("Excel.Application")
Global SafeArray := oLinksDoc.Range("A1:E1500").Value  ;Puts Info into Array!!!
GLobal MaxRow := SafeArray.MaxIndex(1)
Global MaxCol := SafeArray.MaxIndex(2)
	oLinksDoc.ActiveWorkBook.Save()
	oLinksDoc.ActiveWorkBook.Close()
	oLinksDoc.Quit



;Then later in the code....



Row := 1, Col := 1
while (Row <= MaxRow)
{
	while (Col <= MaxCol)
	{
		
		if (SafeArray[A_index,2]=State && SafeArray[A_index,5]=Type)
		{
			Global FoundLink := SafeArray[A_index,3]
Break
		}
		
	}

	Col := 1, Row++

}
Msgbox % FoundLink

Yes, the getting all information and putting into an array has the advantage of Excel not having to remain open. It is probably better if you are just getting the data once and then searching for multiple URL from the data.

If that is the case, I would create a lookup array. It would be a single associative array where the "key" is the State|Type and the "value" is the URL.

Code: Select all

xlApp := ComObjActive("Excel.Application")
SafeArray := xlApp.Range("B1:E10").Value
MaxRow := SafeArray.MaxIndex(1)

Lookup := {}
Row := 0
while (Row++ < MaxRow)
	Lookup[SafeArray[Row,1]"|"SafeArray[Row,4]] := SafeArray[Row,2]

MsgBox % Lookup["Fanatic|Guru"] ; this will be the value from SafeArray column 2 when column 1 is Fanatic and column 4 is Guru.

This will be a Lookup array where the key is the value from column 1 (State) and column 4 (Type) separated by a | and the elements are the value from column 2 (URL). It is important to realize this is the "columns" of your range used, not columns overall in Excel.

Create your SafeArray with the Range starting on your left most column used (State from what I can tell).

This will allow you to get an URL almost instantly once you create the Lookup array.

FG

[mstarr]

Great information! Luckily the list is only about 1500 and it already returns within 1 second using your previous method. In a list over 5k I can see how this would be extremely useful. Really glad this is documented here

Thanks again for the assistance.

[FanaticGuru]

Great information! Luckily the list is only about 1500 and it already returns within 1 second using your previous method. In a list over 5k I can see how this would be extremely useful.

If you don't want to build a Lookup array, you can also just streamline and speedup your loop.

Code: Select all

Loop % MaxRow
	if (SafeArray[A_index,2]=State && SafeArray[A_index,5]=Type)
	{
		Global FoundLink := SafeArray[A_index,3]
		Break
	}

You don't need to loop through the columns as you are explicitly referencing the columns you want. And now that it is not a loop within a loop, you do not need the Row and Col variables. You can just use A_Index for the Row which you were already doing.


This also looks like a very good candidate to make into a function:

Code: Select all

MsgBox % GetURL("Frog", "Plant") ; slow as file opens
MsgBox % GetURL("Plant", "Dog") ; fast

GetURL(State, Type, LinksFullPath:="C:\Test\Test.xlsx", Range:="B1:E1500") ; put default Excel path \ file here and default range
{
	static
	if !SafeArray
	{
		xlWB := ComObjGet(LinksFullPath)
		SafeArray := xlWB.ActiveSheet.Range(Range).Value
		xlWB.Close()
	}
	Loop % SafeArray.MaxIndex(1)
		if (SafeArray[A_index,1]=State && SafeArray[A_index,4]=Type)
			return SafeArray[A_index,2]
}

The first time the function is called it gets the SafeArray from the Excel workbook. The first time will take a few seconds as it has to start Excel and open the file (this is mostly hard drive speed). Later calls it will already have the SafeArray and will be much faster.

FG

[mstarr]

That's awesome!