Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 🙂
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 🙂
this is exactly what I needed! works like a champ!
thanks a bunch!
Oleg