Exporting all variables from the Qlikview application using macro

gilbertomedeiro — Wed, 21 Sep 2022 17:07:35 GMT

Hi everyone!!

It’s about VBScript macro which export all variables from your Qlikview application. You can call it by using a button object or using document event triggers (OnPostReload) located in Settings menu Document Properties option and tab Triggers, for example.

So, why am I develped it? I'll tell you why. A friend of mine was talking about variables sincronization issues between his Qlikview and Qliksense applications. Every time when he create a new variable or he changed the variable definition or even in its comment, he had to replicate this changes to another application which, sometimes, he forgot to do it.

Firstly, I’ve searched if was there some solution on the Qlik Community. Unfortunately, I didn't found nothing ready. So, that's why I developed it. Since then, my friend has been using this macro in his applications and the variables sincronization issues was solved. He just call the macro through document event triggers, setting (OnPostReload) option and then the macro load every single variable into a file (*.csv) which is created in the same folder of your Qlikview application. Once the variables was exported our Qliksense application could be load it and create each variable easily.

Just to be clear and understandable how it works, I attached as example two files (*.qvw) where one of them is the application which need to export his variables every time its reloaded and another is the application which will load data the exported file and it will create every single variable.

When you will open macro editor from the application which will export the variables you will can easily change the file name and its folder destiny. You will can change columns name as well.

So, that is it! I hope it can be useful for everyone!

You can read the scripts below:

ExportVarApp (Macro):

Sub ExportVar()

set docprop = ActiveDocument.GetProperties
wd = docprop.MyWorkingDirectory
wdEx = wd & "\VariablesApp\"

Set fileSystemObject = CreateObject("Scripting.FileSystemObject")
If Not fileSystemObject.FolderExists(wdEx) Then
fileSystemObject.CreateFolder(wdEx)
End If

vNameFile = fileSystemObject.GetBaseName(ActiveDocument.Name) 'Getting Qlikview's document name
fPath = wdEx & vNameFile & "_Variables.csv"

On Error Resume Next
Err.Clear
if fileSystemObject.FileExists(fPath) Then

fileSystemObject.DeleteFile(fPath)

if Err.Number <> 0 then
' An exception occurred
msgbox "Exception:" & vbCrLf &_
" Error number: " & Err.Number & vbCrLf &_
" Error description: '" & Err.Description & vbCrLf &_
" File cannot be deleted because it still open for another application." & vbCrLf &_
" Please, close " & vNameFile & " file and try it again." & vbCrLf
exit sub
else
set fileSystemObject = Nothing

Set objFSO = CreateObject("Scripting.FileSystemObject")
'--------------------------------------------------------------------------------------------------------------------------------------------
' ForReading = 1 - Open a file for reading only. You can't write to this file.
' ForWriting = 2 - Open a file for writing only. Use this mode to replace an existing file with new data. You can't read from this file.
' ForAppending = 8 - Open a file and write to the end of the file. You can't read from this file.
'--------------------------------------------------------------------------------------------------------------------------------------------
Const ForAppending = 8
vSep = "|" 'Delimiter or Separator of fields'
vColumn1 = "VarName"
vColumn2 = "VarDefinition"

set MyVarsApp = ActiveDocument.GetVariableDescriptions

for i = 0 to MyVarsApp.Count - 1
set vObj = MyVarsApp.Item(i)
Set objTS = objFSO.OpenTextFile(fPath, ForAppending, true)
if i = 0 then
objTS.WriteLine(vColumn1 & vSep & vColumn2)
objTS.WriteLine(vObj.Name & vSep & vObj.RawValue)
else
objTS.WriteLine(vObj.Name & vSep & vObj.RawValue)
end if
objTS.Close()
next
msgbox("Arquivo " & vNameFile & "_Variables.csv" & " was exported with success!!")
end if
end if
End Sub

ImportVarApp (Loading and creating variables)

//Load variables
VarTable:
LOAD VarName,
VarDefinition
FROM
VariablesApp\ExportVarApp_Variables.csv
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

//Creating variables
For i = 0 to NoOfRows('VarTable') - 1

Let vVarName = peek('VarName', i, 'VarTable'); // Name of the variable

Let $(vVarName) = peek('VarDefinition', i, 'VarTable'); // Definition of the variable

Next i

//Destroying variables of control
Let i = null();

Let vVarName = null(); //

Drop Table VarTable;

Re: Exporting all variables from the Qlikview application using macro

yocrozet — Wed, 19 Feb 2025 12:57:58 GMT

Thanks for your work
It works great with both apps attached.

article Exporting all variables from the Qlikview application using macro in Member Articles

Exporting all variables from the Qlikview application using macro

Re: Exporting all variables from the Qlikview application using macro