Saturday, March 9, 2013

Macro to Copy Variables In From Another Document


My preference when I need to copy variables from one QlikView document to another is to use a small Windows script to do the copying (I wrote about it in 2008 - search on "Copying Variables From One QlikView Document to Another"). But, I know there are some people who can't use a Windows script because of corporate or I.T. rules. Here's a QlikView macro that you can insert into your module area that will copy in all of the variables from another document. 

This macro has you entering a pathname for the document into an input box. For anyone in the advanced class you can replace that with a browse-for-file feature if you think it is worth the time (I wrote about it - search on "Browse-for-File Macro Button"). In the next blog posting ("More on Copy Variables From Document to Another") I show a macro that can be installed once in a toolkit document and it will copy variables from a specified external document to another external document.

Here's the macro code. Try it out - you can copy and paste the text you see in this blog. Remember, as always, to make a backup copy of your document before testing macros like this. To run the macro, create a button for it or for one-time use just click the Test button in the module editor. There's no error checking but the most common error for this macro is entering an invalid pathname. If your source document requires a password then you will be asked for the password when the macro opens the source document.

Sub Copy_Variables
' /* Copy Variables In From Another QV Document */
' /* QlikView Maven, February 2013 */
Dim objQV, objSource, objDest, objSourceVar, objDestVar 
Dim objVars, varcontent, objTempVar, varname, i, varctr
'initialize
fn=inputbox("Enter pathname to source document containing variables you want to copy:","Enter Pathname for Source Document")
if trim(fn)="" then 'no entry or cancel
  exit sub
  end if
Set objSource=Application.OpenDoc(fn) 
set objVars=objSource.GetVariableDescriptions
varctr=0

'Loop through the variables
for i = 0 to objVars.Count - 1
  varctr=varctr+1
  set objTempVar = objVars.Item(i)
  varname=Trim(objTempVar.Name) 
  Set objSourceVar=objSource.Variables(varname) 
  varcontent=objSourceVar.GetRawContent
  'display the variable to check on progress if needed
  'msgbox(varname & " = " & varcontent)

  'update the value of variable in current document
  Set objDestVar=ActiveDocument.Variables(varname)
  If objDestVar is nothing then
    'must need to create variable
    ActiveDocument.CreateVariable varname
    Set objDestVar=ActiveDocument.Variables(varname)
    End If

  objDestVar.SetContent varcontent,true

next 'end of loop 

'we're done, show a message and close down
msgbox varctr&" variables copied from "&fn
objSource.CloseDoc
set objSource=nothing
set objVars=nothing
set objTempVar=nothing
set objSourceVar=nothing
set objDestVar=nothing
End Sub  'end of Copy_Variables



2 comments:

Steve Dark said...

Thanks! This fills a major gap in the functionality of QlikView.

unknownn said...

how to paste it in another qvw including expressions