Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;