QlikView App Dev

[Peony]

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

[felipedl]

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

[tamilarasu · Accepted solution]

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!

[Peony]

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

[Peony]

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