topic Need VBScript advise in QlikView

Need VBScript advise

Oleg_Troyansky — Thu, 04 Jun 2009 23:26:44 GMT

Folks,

my turn to ask a question [;)] Michael, Rob or perhaps other VBScript gurus - please help me script the following:

I have a chart with several definitions presented as variables. All relevant variables have a single substring in common. For example:

exp_Oleg_1, exp_Oleg_2, lbl_Oleg_1, lbl_Oleg_2

I'd like to clone the object and replace the common string by another common string in the target chart. For example, instead of the 4 variables above, have the new variable names:

exp_Rob_1, exp_Rob_2, etc...

I was thinking along the following lines:

1. Export XML definitions of the "source object"

2. Read the XML file within the script and replace one substring by another everywhere

3. Create new object from the XML file

My scription knowledge ends at number 1 or 3. I don't know how to accomplish #2. Any ideas?

thanks in advance!

Oleg

Need VBScript advise

Anonymous — Fri, 05 Jun 2009 01:33:29 GMT

Hi Oleg,

Interesting question, had to throw together an example. In this case I used a text box object and added a variable as value called v_Test.

My macro exports the object to XML, parses through it and changes all v_Test to v_NewTest and writes the text into a new XML file which I then load as an object into QV again. This of course needs system access allowed. It can probably be done in a nicer way though without having to write two files to disk but I'm lazy sometimes.

Macro:

sub test
ActiveDocument.GetSheetObject( "TX01" ).WriteXmlPropertiesFile "C:\MychartProp.xml"

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\MychartProp.xml", ForReading)

strText = objFile.ReadAll
objFile.Close
strNewText = Replace(strText, "v_Test", "v_NewTest")

Set objFile = objFSO.CreateTextFile("C:\MyNewChartProp.xml", ForWriting)
objFile.WriteLine strNewText
objFile.Close

ActiveDocument.ActiveSheet.CreateObjectFromXmlPropertiesFile "C:\MyNewChartProp.xml"

end sub

Also, the new object is pasted right on top of the old one which confused me before I moved it and found the other one under it 🙂

Need VBScript advise

Oleg_Troyansky — Fri, 05 Jun 2009 02:14:35 GMT

this is exactly what I needed! works like a champ!

thanks a bunch!

Oleg