Qlikview Macro optimization

[Sleepwalker]

Hi QV Community.

I have a macro that loads data from Excel.

Spoiler

sub from_excel
Set oXL=CreateObject("Excel.Application")
Excel=oXL.GetOpenFilename("All Files (.),.",,"Select file",False)
Set oWB=oXL.Workbooks.Open(Excel,False)
oXL.Visible = false
Set x = oXL.Worksheets(1)
for i=1 to 65536
b="A"&i&":A"&i
a=x.Range(b).Value
if a="" then exit for
ActiveDocument.Fields("Product").ToggleSelect "" & a
next
oXL.Quit
Set oXL=nothing
end sub

When working with large amounts of data, it is very slow.

Is it possible to speed up macro execution?

[marcus_sommer]

I assume that you want with this macro to exonerate your users to select multiple values manually respectively to teach them to create a valid selection-list - which is really simple. If you have the values in column A in A1 to A### you could write in Column B for B1:

= A1

in B2 then

= B1&"|"&A2

and this formula copied to the end of values from column A. The last value ob column B could now be copied and within the search you enters (ctrl + V). That's quite fast and the most users should be able to apply it.

Nevertheless if you want to keep by a macro-approach I would suggest not to read the values cell by cell and to add each one to the current selection (and triggers each time a new calculation) else to load them into an array and then to loop through it and to build a concatenated search-string in the above mentioned manner - means:

(value1|value2|value3)

and then to assign this search-string to a normal selection. Here an example what's meant with the array: https://www.herber.de/forum/archiv/1288to1292/1291205_Zellwerte_effizient_in_Array_einlesen.html which is in german - you may translate it with google or just using a few key-statements to find multiple other postings in your preferred language.

- Marcus

[Brett_Bleess]

Did Marcus' post help you with your use case?  If so, do not forget to return to your thread and use the Accept as Solution button on his post to give him credit for the assistance and let other Community Members know it worked.

I am going to add one thing additional, using macros does force a single-thread condition, so that is definitely going to slow things down in that regard...

Regards,
Brett