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

Get help with using AutoHotkey and its commands and hotkeys
mstarr
Posts: 25
Joined: 31 Dec 2015, 09:29

Changing script to use array.... (Excel)

18 Jun 2018, 09:42

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] [Expand] [Download] GeSHi © Codebox Plus

FanaticGuru
Posts: 1137
Joined: 30 Sep 2013, 22:25

Re: Changing script to use array.... (Excel)

18 Jun 2018, 12:39

mstarr wrote: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] [Expand] [Download] (Untitled.ahk)GeSHi © Codebox Plus


FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

Google Search, Dictionary, Thesaurus - Quickly Get Information from Specific Web Resources

[Function] Timer - Create and Manage Timers
FanaticGuru
Posts: 1137
Joined: 30 Sep 2013, 22:25

Re: Changing script to use array.... (Excel)

18 Jun 2018, 14:03

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

Code: [Select all] [Download] (Untitled.ahk)GeSHi © Codebox Plus

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
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

Google Search, Dictionary, Thesaurus - Quickly Get Information from Specific Web Resources

[Function] Timer - Create and Manage Timers
mstarr
Posts: 25
Joined: 31 Dec 2015, 09:29

Re: Changing script to use array.... (Excel)

18 Jun 2018, 15:27

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] [Expand] [Download] GeSHi © Codebox Plus

FanaticGuru
Posts: 1137
Joined: 30 Sep 2013, 22:25

Re: Changing script to use array.... (Excel)

18 Jun 2018, 18:30

mstarr wrote: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] [Expand] [Download] GeSHi © Codebox Plus

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] [Download] (Untitled.ahk)GeSHi © Codebox Plus

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
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

Google Search, Dictionary, Thesaurus - Quickly Get Information from Specific Web Resources

[Function] Timer - Create and Manage Timers
mstarr
Posts: 25
Joined: 31 Dec 2015, 09:29

Re: Changing script to use array.... (Excel)

19 Jun 2018, 09:23

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
Posts: 1137
Joined: 30 Sep 2013, 22:25

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

19 Jun 2018, 12:54

mstarr wrote: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] [Download] (Untitled.ahk)GeSHi © Codebox Plus

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] [Expand] [Download] (Untitled.ahk)GeSHi © Codebox Plus

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
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

Google Search, Dictionary, Thesaurus - Quickly Get Information from Specific Web Resources

[Function] Timer - Create and Manage Timers
mstarr
Posts: 25
Joined: 31 Dec 2015, 09:29

Re: Changing script to use array.... (Excel)

20 Jun 2018, 08:38

:shock: That's awesome!

Return to “Ask For Help”

Who is online

Users browsing this forum: aleksazr, Banayat, colt, labrint, swagfag and 47 guests