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

Post a reply

Confirmation code
Enter the code exactly as it appears. All letters are case insensitive.
Smilies
:D :) ;) :( :o :shock: :? 8-) :lol: :x :P :oops: :cry: :evil: :twisted: :roll: :!: :?: :idea: :| :mrgreen: :geek: :ugeek: :arrow: :angel: :clap: :crazy: :eh: :lolno: :problem: :shh: :shifty: :sick: :silent: :think: :thumbup: :thumbdown: :salute: :wave: :wtf: :yawn: :facepalm: :bravo: :dance: :beard: :morebeard: :xmas: :HeHe: :trollface: :cookie: :rainbow: :monkeysee: :monkeysay: :happybday: :headwall: :offtopic: :superhappy: :terms: :beer:
View more smilies

BBCode is ON
[img] is OFF
[flash] is OFF
[url] is ON
Smilies are ON

Topic review
   

Expand view Topic review: Changing script to use array.... (Excel)

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

Post by mstarr » 20 Jun 2018, 08:38

:shock: That's awesome!

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

Post by FanaticGuru » 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]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]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

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

Post by mstarr » 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.

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

Post by FanaticGuru » 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]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]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

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

Post by mstarr » 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]GeSHi © Codebox Plus

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

Post by FanaticGuru » 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]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

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

Post by FanaticGuru » 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]GeSHi © Codebox Plus


FG

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

Post by mstarr » 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]GeSHi © Codebox Plus


Top