Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Peony
Creator III
Creator III

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

1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

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!

View solution in original post

4 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

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
Champion
Champion

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
Creator III
Creator III
Author

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

Peony
Creator III
Creator III
Author

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