It's been a while since I've done this, but if I remember correctly you can create macros directly in the script. The syntax is something like:
Note that there are no semicolons here. Within the sub it's just regular VBS syntax. If this doesn't work for you, you can also try creating a standard .vbs file to create your CSV and then trigger your QVW reload on completion of the .vbs.
Yes, I know it.
But my question is : how, in the macro, add lines in a QV table on the fly? For example if my macro do a loop on the month number (12 today) and I want that my macro create 12 lines with "Hello World <monthname>" and I want these 12 lines to be added directly in a QV table, without writing a temporary text file or an INLINE, how could I do that ?
Assuming you have to fetch the data in the module code, here's how I do it. Pick a delimiter for your rows that won't appear in your data. Create a module function that returns your rows as a single string with the delimiter between rows. Like this for your example:
Function MakeSomeLines (count)
ret = ""
For i = 1 to count
ret = ret & "Hello World " & cstr(i) & ";"
MakeSomeLines = Left(ret, Len(ret)-1) ' Drop trailing semicolon
In your LOAD statement, call the function with any necessary parms. Split into rows using substring:
LOAD subfield(MakeSomeLines(month(today(1))), ';') as Hello
BTW, if your data already has newline chars then the delimiter and subfield parm is chr(10)
(I'm aware that the above can be done in script, but the example matches the poster's question).
Thanks Vlad but in your case, the number of lines is defined in the script, when the number of lines in my case if given by the result of the macro. It means that the macro can retrieve me 1 or 1000 lines and I need to add these lines in a Qlikview table.
Very interesting method Rob, I understand that subfield is a way to generate more than 1 line in a table with only 1 line in the source. Very very interesting. Unfortunately, it means that if I have 10 000 lines to add, my macro should retrieve the data of the 10 000 lines concatenated in 1 string, and I fear that I can reach a limit.
In fact I search to append data in qlikview table in VB script. Something like ActiveDocument.Table.AppendData(bla...)
Maybe I could Create a chart on the fly, then read it and store in a table ?
You can't manipulate ActiveDocument during load, but you can after the load. Perhaps even in the OnPostReload event. You could use the dynamic update feature -- Document.DynamicUpdateCommand -- to add data.
Easy to do in Desktop, some obstacles in the server. I believe you can use dynamic update in QV Developer without restriction. To use it in the server requires licensing an additional feature. Also, OnPostReload in the server cannot run macros if you chose to implement this with OnPostReload.
So here are my cases/tests:
- On load, call a function that retrieves all data and stores in a text file
- Still on load, read the file
=> issue : unsafe macro must be enable on server.
- On load, call a function that retrieves all data concatenated in a string, then split with Qlikview function subfield
=> issue : dangerous if there is too much lines, the string would be like a CLOB.
- On load, call a function that stores data in an VB array
- Still on load, loop on the array (with VB functions) to read content
=> issue : a VB variable can't survive to a call, and so can't be used to store data
- On load, call a function that stores data in numerous Qlikview variables, created and filled on the fly
- Still on load, loop on the variables (with autogenerate) to read content
=> issue : a Qlikview variable can't be created in the load because ActiveDocument does not exist
- On open, call a function that stores data in numerous Qlikview variables, created and filled on the fly
- On load, loop on the variables (with autogenerate) to read content
=> issue : seems that macro can't be called on OnOpen on server
- On open, call a function that stores data in Qlikview tables with DynamicUpdate.
- On load, loop on the tables
=> issue : DynamicUpdate needs a license on server
- On load, call a function that retrieves the data 1 time and count the results
- Still on load, loop on the count and call a function that retrieves each line
=> issue : as we can't store the data with VB script, the macro will be send 1 time to count + 1 time for each line to retrieve. If the macro is long to run and there are a lot of lines, the execution will be very long.
So, it seems that I have no solution. Maybe some other ideas? Thanks
>So here are my cases/tests:
>- On load, call a function that retrieves all data and stores in a text file
>- Still on load, read the file
>=> issue : unsafe macro must be enable on server.
"Unsafe macro" setting on server sounds scary, but it's not really. I would guess that something close to half the installations have this set on.
>- On load, call a function that retrieves all data concatenated in a string, then split with Qlikview function subfield
>=> issue : dangerous if there is too much lines, the string would be like a CLOB.
Have you tested this? I regularly use this technique to retrieve thousands of lines with no problem. I just ran a quick test and extracted 20k lines from a 1MB+ string with no problem. Worst case, you can make multiple calls to your datasource for something like 1000 lines each -- if your datasource supports chunking like that.
>- On load, call a function that stores data in an VB array
>- Still on load, loop on the array (with VB functions) to read content
>=> issue : a VB variable can't survive to a call, and so can't be used to store data
The macro module can use global variables and code (anythng defined outside a Sub or Function). Those global vars will survive between calls.
Global code will be executed the first time the macro module is used -- eg when a function is called from the script. Global code will also be executed when the macro editor is opened. It's a bit tricky to play with this in development. Best to close and reopen the doc when doing final testing.
Another possibility is to build the csv file in a seperate batch process before the reload. If you have Publisher, this can be scheduled as an external task that runs before the reload. You could also run this process with a script EXECUTE statement but you would have to allow EXECUTE in the script settings.
In fact the "issues" I have given are constraints from administrators (no unsafe macros) or architecture (don't do a concatenate / split of data that could, even if it's rare corrupt or lost data).
For my personnal use I would have used the text file with unsafe macro or your technic with subfield.
I will try the use of global variables, but I have already tried without result. Maybe I have badly tested it.
Your last solution is probably the good one, I think we will do that, because this solution is accepted by Administrators AND architecture.
Thanks a lot.
Sorry for the delay. I work on this subject evening at home, and a bit during the lunch.
The global variables works, true. But it's VERY difficult to do VB under qlikview (switch between Script and Module, no debug, unexpected and random behavior in the call of subs and functions).
Moreover I have got an amazing issue about global variables "locked" that were randomly unavailable in 1 load.
Finally, I have 4 solutions:
- the file created by unsafe macro
- the file created by external batch
- the rows concatenated and splitted by subfield
- use of global Array in VB.
Thanks a lot, I can close this thread