4 Replies Latest reply: Dec 15, 2017 4:18 AM by Nataliia Skliar RSS

    VBS macro to restore variables from .csv

    Nataliia Skliar

      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
          Felip Drechsler

          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

               [C:\Users\felipe.lourenco\Desktop\$(file)]

               (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
            Tamil Nagaraj

            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 Felip Drechsler. Good luck and have a nice day!