topic VBS macro to restore variables from .csv in QlikView

VBS macro to restore variables from .csv

Peony — Thu, 14 Dec 2017 13:57:54 GMT

Hi there.

I've try to create a macro to restore variables from list in .csv doc.

But here some mistake cause it stopped after "Msgbox 9". Please, help to understend the reason.

.csv example in the attacment

Sub RestoreVars



    Set fso = CreateObject("Scripting.FileSystemObject")

    fileName = replace(ActiveDocument.GetProperties.FileName,".qvw","")

    filePath = (fileName)&".var.csv"

    If fso.FileExists(filePath) Then

        Set storageFile = fso.OpenTextFile (filePath, 1, True)

         Else

        Msgbox "No .csv file to restore"

    End If

    storageFile.ReadLine()

    vars=0

    While Not storageFile.AtEndOfStream

    Str = storageFile.ReadLine()

    Msgbox Str

    items = Split(Str,"|",2)

    Msgbox 9

    varName=items(0)

    Msgbox varName

    set var = ActiveDocument.GetVariable("varName")

    Msgbox var

    if var is nothing then

    Msgbox 12

    ActiveDocument.CreateVariable(varName)

    end if

    if uBound(items)>0 then

    varValue=items(1)

ActiveDocument.Variables(varName).SetContent varValue,true

    end if

vars=vars+1

Wend

    storageFile.Close()

    msgbox("Vars Restored:"&vars)

End Sub

Re: VBS macro to restore variables from .csv

felipedl — Thu, 14 Dec 2017 16:27:38 GMT

Hi Natallia,

You can do the import of this variables in Qlik script instead of vb macro.

I've done the following code;

// Start of code

varName = '';

varValue = '';

i = '';

sub restoreVariables(file)

[$(file)]:

LOAD

SubField(VariableName|VariableValue,'|',1) as FieldName,

SubField(VariableName|VariableValue,'|',2) as FieldValue

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

for i = 0 to NoOfRows('$(file)')-1

varName = peek('FieldName',$(i),'[$(file)]');

varValue = peek('FieldValue',$(i),'[$(file)]');

let '$(varName)' = '$(varValue)';

next i;

drop table '$(file)';

end sub;

call restoreVariables('test.var.csv');

varName = Null();

varValue = Null();

i = Null();

// End of code

Save the above code and your file in the same location and you will import all the registered variables to the QVW

Re: VBS macro to restore variables from .csv

tamilarasu — Thu, 14 Dec 2017 17:13:12 GMT

Hi Nataliia,

Logically, you don't need to check whether the variables are available or not. If the variable is available then the macro will always overwrite the existing ones. If not, then macro will automatically create the new variables. So you don't need the below lines that are struck. I hope this makes sense.

The error is caused by the below line.

Msgbox var

Nataliia Skliar wrote:

Sub RestoreVars

    Set fso = CreateObject("Scripting.FileSystemObject")

    fileName = replace(ActiveDocument.GetProperties.FileName,".qvw","")

    filePath = (fileName)&".var.csv"

    If fso.FileExists(filePath) Then

        Set storageFile = fso.OpenTextFile (filePath, 1, True)

        Else

        Msgbox "No .csv file to restore"

    End If

    storageFile.ReadLine()

    vars=0

    While Not storageFile.AtEndOfStream

    Str = storageFile.ReadLine()

    Msgbox Str

    items = Split(Str,"|",2)

    Msgbox 9

    varName=items(0)

    Msgbox varName

    set var = ActiveDocument.GetVariable("varName")

    Msgbox var

    if var is nothing then

    Msgbox 12

    ActiveDocument.CreateVariable(varName)

    end if

if uBound(items)>0 then

    varValue=items(1)

ActiveDocument.Variables(varName).SetContent varValue,true

    end if

vars=vars+1

Wend

    storageFile.Close()

    msgbox("Vars Restored:"&vars)

End Sub

By the way, you can use back end script for this task rather than using macro as mentioned by felipedl. Good luck and have a nice day!

Re: VBS macro to restore variables from .csv

Peony — Fri, 15 Dec 2017 09:15:54 GMT

Thank you. It's a proper idea and I will use it for other projects. But current task requires macro.

Re: VBS macro to restore variables from .csv

Peony — Fri, 15 Dec 2017 09:18:06 GMT

Hi Tamil. You are quite right. Thank you for your advice - it helped.