Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
kitjupitera
Contributor

VBS macro to restore variables from .csv

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

Tags (3)
1 Solution

Accepted Solutions

Re: VBS macro to restore variables from .csv

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!

4 Replies
felipedl
Valued Contributor III

Re: VBS macro to restore variables from .csv

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

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!

kitjupitera
Contributor

Re: VBS macro to restore variables from .csv

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

kitjupitera
Contributor

Re: VBS macro to restore variables from .csv

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

Community Browser