Listing All Variables and Contents

[Author's note:  You can see information about a different method for accomplishing this same thing. Search on: QlikView Maven Utility That Can Extract and Show All of the Variables ]

After last weeks blog posting I got some questions about how you might list out all of the document variables and their contents. It can be a useful thing for reports that depend on variables for their functionality and useful for corporate developers who must document report functionality.

Below is a Microsoft Excel macro that could help you. When you run this macro it will ask you to select a QlikView report file and then it will copy all of the document variables and their contents into the current worksheet. Once the data is in a spreadsheet you can sort it, print it, search it for particular fields or functions, or write it out as a text or tab-delimited file. In a future blog posting I'll show how variables can be loaded into a report from a spreadsheet which, along with the macro below, will give you a method for sharing and maintaining variables.

You can get a copy of the spreadsheet with the macro installed and a button that runs the macro at this website:  [Author's note - old site is not working anymore].  The spreadsheet on the website also includes a button that will list all variables from a currently open QlikView document (sorry about the advertisements on the site - just click on the spreadsheet downloads).
Here's the Excel macro code. You can copy and paste the macro code from this window and put it into your own spreadsheet if you want to build your own.

Sub List_QV_Variables()
'This Excel macro will ask you to select a QlikView Report File and
'then it will put the variable names and contents into your current worksheet
'--Tim Benoit, Nov 2008
Dim oQV, oRpt, oVars, oTempVar, oThisVar, qv_fn

'Get the QlikView report pathname
qv_fn = Application.GetOpenFilename("QlikView Report File (.),*.*", , "Select a QlikView .qvw Report File", False)
If qv_fn = 0 Then
  Exit Sub
  End If

'Write out some heading text
ActiveSheet.Cells(1, 1).Formula = "QlikView Variables List"
ActiveSheet.Cells(2, 1).Formula = "Report pathname:"
ActiveSheet.Cells(2, 2).Formula = qv_fn
ActiveSheet.Cells(3, 1).Formula = "Variable Name"
ActiveSheet.Cells(3, 2).Formula = "Variable Content"

'Open the QlikView report file
Set oQV = CreateObject("QlikTech.QlikView")
Set oRpt = oQV.OpenDoc(qv_fn)
Set oVars = oRpt.GetVariableDescriptions
'Loop through the variables
For i = 0 To oVars.Count - 1
  Set oTempVar = oVars.Item(i)
  varname = Trim(oTempVar.Name)
  Set oThisVar = oRpt.Variables(varname)
  varcontent = oThisVar.GetRawContent
  'Write data into worksheet cells
  ActiveSheet.Cells(i + 4, 1).NumberFormat = "@"
  ActiveSheet.Cells(i + 4, 1).Formula = varname
  ActiveSheet.Cells(i + 4, 2).NumberFormat = "@"
  ActiveSheet.Cells(i + 4, 2).Formula = varcontent
  Next

'Close QlikView
oRpt.CloseDoc
oQV.Quit
End Sub