QlikView Macros – Useful collection

Macros are very important feature of QlikView. Here is a collection of useful functions I encountered over the years. I will update this collection whenever I get the chance and you may suggest new code. Please note that some of them require system access.

1) Run external program:

FUNCTION RunExe(cmd)   
   CreateObject("WScript.Shell").Exec(cmd)
END FUNCTION

SUB CallExample
   RunExe("c:\Program Files\Internet Explorer\iexplore.exe")
END SUB

2) Export object to Excel

FUNCTION ExcelExport(objID)
  set obj = ActiveDocument.GetSheetObject( objID )
  w = obj.GetColumnCount
  if obj.GetRowCount>1001 then
    h=1000 
  else h=obj.GetRowCount
  end if
  Set objExcel = CreateObject("Excel.Application")
  objExcel.Workbooks.Add
  objExcel.Worksheets(1).select()
  objExcel.Visible = True
  set CellMatrix = obj.GetCells2(0,0,w,h)
  column = 1
  for cc=0 to w-1
   objExcel.Cells(1,column).Value = CellMatrix(0)(cc).Text
   objExcel.Cells(1,column).EntireRow.Font.Bold = True
   column = column +1
  next
  c = 1
  r =2
  for RowIter=1 to h-1 
    for ColIter=0 to w-1 
      objExcel.Cells(r,c).Value = CellMatrix(RowIter)(ColIter).Text
      c = c +1
    next
   r = r+1
   c = 1
 next
END FUNCTION

SUB CallExample
   ExcelExport( "CH01" )
END SUB

3) Export object to JPG

FUNCTION ExportObjectToJpg( ObjID, fName)
   ActiveDocument.GetSheetObject(ObjID).ExportBitmapToFile fName
END FUNCTION

SUB CallExample
   ExportObjectToJpg "CH01", "C:\CH01Image.jpg" 
END SUB

4) Export object from a hidden sheet to JPG

FUNCTION ExportObjectToJpg(SheetID, ObjID, fName)
  set s=ActiveDocument.GetSheetById(SheetID)
  set sp=s.GetProperties
  sp.Show.Always = true
  s.SetProperties sp
  s.Activate
  for i=0 to s.NoOfSheetObjects-1
      if s.SheetObjects(i).GetObjectId = ObjID then 
          ActiveDocument.GetApplication.WaitforIdle
          s.SheetObjects(i).ExportBitmapToFile fName
      end if    
  next
END FUNCTION

SUB setVariable(varName, varValue)
 set v = ActiveDocument.Variables(varName)
 v.SetContent varValue, true
END SUB

SUB CallExample
  ' "vShow" is the variable which controls the visibility of the sheet: Sheet properties -> Show Sheet -> Conditional: =vShow
  setVariable "vShow", 1 
  set ss= ActiveDocument.GetSheet(0)
  ExportObjectToJpg "Document\SH02", "Document\CH01", "D:\CH01Image.jpg" 
  ActiveDocument.GetSheetById(ss.GetProperties.SheetId).Activate
  setVariable "vShow", 0 ' replace 0 with your own condition to hide the sheet
END SUB

5) Save and exit QlikView

SUB SaveAndQuit
   ActiveDocument.Save
   ActiveDocument.GetApplication.Quit
END SUB

6) Clone Dimension Group

SUB DuplicateGroups 
    SourceGroup = InputBox("Enter Source Group Name") 
    CopiesNo = InputBox("How many copies?") 
    SourceGroupProperties = ActiveDocument.GetGroup(SourceGroup).GetProperties 
    FOR i = 1 TO CopiesNo 
      SET DestinationGroup = ActiveDocument.CreateGroup(SourceGroupProperties.Name & "_" & i) 
      SET DestinationGroupProperties = DestinationGroup.GetProperties 

      IF SourceGroupProperties.IsCyclic THEN 
        DestinationGroupProperties.IsCyclic = true 
      ELSE
        DestinationGroupProperties.IsCyclic = false
      END IF
      DestinationGroup.SetProperties DestinationGroupProperties

      SET Fields = SourceGroupProperties.FieldDefs 
      FOR c = 0 TO Fields.Count-1 
        SET fld = Fields(c) 
        DestinationGroup.AddField fld.name 
      NEXT
      Application.waitforidle 
    NEXT 
END SUB

7) Open document with selection of current month

SUB DocumentOpen
  ActiveDocument.Sheets("Intro").Activate
  ActiveDocument.ClearAll (true)
  ActiveDocument.Fields("YearMonth").Select ActiveDocument.Evaluate("Date(MonthStart(Today(), 0),'MMM-YYYY')")
END SUB

8) Read and Write variables

FUNCTION getVariable(varName)
 set v = ActiveDocument.Variables(varName)
 getVariable = v.GetContent.String
END FUNCTION

SUB setVariable(varName, varValue)
 set v = ActiveDocument.Variables(varName)
 v.SetContent varValue, true
END SUB

9) Open QlikView application, reload, press a button and close (put the code in a .vbs file)

Set MyApp = CreateObject("QlikTech.QlikView")
Set MyDoc = MyApp.OpenDoc ("C:\QlikViewApps\Demo.qvw","","")
Set ActiveDocument = MyDoc
ActiveDocument.Reload
Set Button1 = ActiveDocument.GetSheetObject("BU01")
Button1.Press
MyDoc.GetApplication.Quit
Set MyDoc = Nothing
Set MyApp = Nothing

10) Delete file

FUNCTION DeleteFile(rFile)
	set oFile = createObject("Scripting.FileSystemObject")

	currentStatus = oFile.FileExists(rFile)

	if currentStatus = true then
		oFile.DeleteFile(rFile)
	end if
	set oFile = Nothing
END FUNCTION

SUB CallExample
    DeleteFile ("C:\MyFile.PDF") 
END SUB

11) Get reports information

function countReports
	set ri = ActiveDocument.GetDocReportInfo
	countReports = ri.Count
end function

function getReportInfo (i)
	set ri = ActiveDocument.GetDocReportInfo
    set r = ri.Item(i)
    getReportInfo = r.Id & "," & r.Name & "," & r.PageCount & CHR(10)
end function

12) Send mail using Google Mail

SUB SendMail
	Dim objEmail

	Const cdoSendUsingPort = 2 	' Send the message using SMTP
	Const cdoBasicAuth = 1     	' Clear-text authentication
	Const cdoTimeout = 60      	' Timeout for SMTP in seconds

     mailServer = "smtp.gmail.com"
     SMTPport = 465
     mailusername = "MyAccount@gmail.com"
     mailpassword = "MyPassword"

     mailto = "destination@company.com" 
     mailSubject = "Subject line" 
     mailBody = "This is the email body" 

	Set objEmail = CreateObject("CDO.Message")
	Set objConf = objEmail.Configuration
	Set objFlds = objConf.Fields

	With objFlds
    	.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
    	.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = mailServer
	.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = SMTPport
	.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
    	.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = cdoTimeout
	.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasicAuth
	.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = mailusername
	.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = mailpassword
    	.Update
	End With

	objEmail.To = mailto
	objEmail.From = mailusername
	objEmail.Subject = mailSubject
	objEmail.TextBody = mailBody
	objEmail.AddAttachment "C:\report.pdf"
	objEmail.Send

	Set objFlds = Nothing
	Set objConf = Nothing
	Set objEmail = Nothing
END SUB

13) Autozoom sheet to fit the window

SUB AutoZoom

     ActiveDocument.GetApplication.WaitForIdle
     ActiveDocument.ActiveSheet.FitZoomToWindow

END SUB

14) Import and export all variables from an Excel file (by Barry)

Sub ImportVariablesFromExcel

' Imports all variable definitions from the Excel file specified in the variable 'v.Filename.Variables', 
' The path to the Excel file needs to be in a relative format.
'
' Variable definitions need to be placed on a worksheet named 'Variables' and containing the following columns:
'
'	1: Variable, name of the variable.
'
'	2: Expression, expression or value of the variable. Prefix with ' when starting with =, otherwise Excel tries
'	   to interpret the expression as an Excel formula.
'
'	3: Comment, comments for the variable.

	Set doc = ActiveDocument
	Set wbFilename = ActiveDocument.GetVariable("v.Filename.Variables")

	If wbFilename is Nothing then

		' The variable that stores the location of the variables Excel file does not exist
		MsgBox "The required variable 'v.Filename.Variables' does not exists!", 16, "Error"

	Else

		If Instr(Lcase(wbFilename.GetRawContent), "xls") = 0 then

			' The variable exists, but does not contain a valid Excel filename (based on looking for the 'xls' part)
			MsgBox "No valid Excel filename specified in variable 'v.Filename.Variables'", 16, "Error"

		Else

			'Get the path of the current QVW       		
       		QvwPath = Left(ActiveDocument.GetProperties.Filename, InStrRev(ActiveDocument.GetProperties.Filename, "\"))

       		' Initialize Excel, open the file and get a reference to the Variables worksheet
       		Set objExcel = CreateObject("Excel.Application")
       		Set objWorkbook = objExcel.Workbooks.Open(QvwPath & wbFilename.GetRawContent)
       		Set objSheet = objWorkbook.Sheets("Variables")

       		' Ignore the header by starting on the second row
       		i = 2

       		' Create a new variable (or overwrite if it already exists) for each row in the worksheet
       		Do while not IsEmpty(objSheet.Cells(i, 1))

            	doc.CreateVariable(objSheet.Cells(i, 1).Value)

              	Set v = doc.Variables(objSheet.Cells(i, 1).Value)

              	v.SetContent objSheet.Cells(i, 2).Value, true
              	v.SetComment objSheet.Cells(i, 3).Value

              	i = i + 1

       		Loop

			' Close Excel, otherwise we'll be left with running instances in the background
			objWorkbook.Saved = True
			objWorkbook.Close
			objExcel.Quit

			Set objSheet = Nothing
			Set objWorkbook = Nothing
          	Set objExcel = Nothing

		End If

	End If

End Sub  

Sub ExportVariablesToExcel

' Exports all variables, with the exception of QlikView specific variables, to a new
' Excel workbook in a worksheet called 'Variables', this sheet contains 3 columns:
'
'	1: Variable, name of the variable.
'
'	2: Expression, expression or value of the variable. Prefix with ' when starting with =, otherwise Excel tries
'	   to interpret the expression as an Excel formula.
'
'	3: Comment, comments for the variable.

	Set doc = ActiveDocument

	Set objExcel = CreateObject("Excel.Application")
	Set objWorkbook = objExcel.Workbooks.Add
    Set objSheet = objWorkbook.Sheets.Add

    objSheet.Name = "Variables"   
	objSheet.Cells(1, 1).Value = "Variable"
 	objSheet.Cells(1, 2).Value = "Expression"
 	objSheet.Cells(1, 3).Value = "Comment"

	Set vars = ActiveDocument.GetVariableDescriptions

	r = 2

	For i = 0 to vars.Count - 1

		Set v = vars.Item(i)

            ' Exclude all QlikView specific variables
			If not v.IsConfig and not v.IsReserved then

				objSheet.Cells(r, 1).Value = v.Name

                ' Excel treats expressions starting with = as an Excel formula, 
                ' so prefix with ' to indicate that it should be treated as text
                if Left(v.RawValue, 1) = "=" then			
					objSheet.Cells(r, 2).Value = "'" & v.RawValue
				else
					objSheet.Cells(r, 2).Value = v.RawValue
				end if

				objSheet.Cells(r, 3).Value = ActiveDocument.Variables(v.Name).GetComment

			r = r + 1
			end if

       next

	' Show Excel so the exported variables can be inspected before saving
	objExcel.Visible = True

End Sub

15) Add conditional expression to chart

SUB AddConditionalExpressionToExistingChart

set chart = ActiveDocument.GetSheetObject("CH01")
    ExpNo = chart.AddExpression( "sum( Expression1 )")

set cp = chart.GetProperties
set ExpVis = cp.Expressions.Item(ExpNo).Item(0).Data.ExpressionVisual

ExpVis.Label.v = "Sales"
ExpVis.NumAdjust = 1      'center
ExpVis.LabelAdjust = 1    'center

set ExpCond= cp.Expressions.Item(ExpNo).Item(0).Data.EnableCondition

ExpCond.Type = 2
ExpCond.Expression = "sum( Expression1 ) > 0"

chart.SetProperties cp

END SUB

16) Change layout property to all object in one step (AllowMoveSize = False)

SUB BlockObjects

for s = 0 to ActiveDocument.NoOfSheets - 1
    set vSheet = ActiveDocument.GetSheet(s)
	Objects = vSheet.GetSheetObjects
	For i = lBound(Objects) To uBound(Objects)
		set vObject = Objects(i)
		set vObjectFrame = vObject.GetFrameDef
		vObjectFrame.AllowMoveSize = false
		vObject.SetFrameDef vObjectFrame
	Next
next

END SUB

17) Reset INPUT FIELD

SUB ResetInputField
       set fld = ActiveDocument.Fields("FieldName")
       fld.ResetInputFieldValues 0
END SUB

18) Dynamic Update (insert values from inputboxes into a table)

SUB InsertValues

 cust = getVariable("vCustomer")
 val = getVariable("vSales")

 SET Result = ActiveDocument.DynamicUpdateCommand ("INSERT INTO * (Customer, Sales) VALUES ('" & cust & "', " & val & ");")
	
 if Result = false then
    MsgBox Result.ErrorMessage
 end if  

END SUB

SUB UpdateValues

 cust = getVariable("vCustomer")
 val = getVariable("vSales")

 SET Result = ActiveDocument.DynamicUpdateCommand ("UPDATE * SET Sales = " & val & " WHERE Customer = '" & cust & "'")
	
 if Result = false then
    MsgBox Result.ErrorMessage
 end if  

END SUB

SUB DeleteValues

 cust = getVariable("vCustomer")
 val = getVariable("vSales")

 SET Result = ActiveDocument.DynamicUpdateCommand ("DELETE FROM SALES_TABLE WHERE Customer = '" & cust & "' and Sales = " & val)
	
 if Result = false then
    MsgBox Result.ErrorMessage
 end if  

END SUB

Happy Qliking!

Advertisements
Tagged with: , ,
Posted in Macro
94 comments on “QlikView Macros – Useful collection
  1. Thanks for the Tips, anymore tips on EXECUTE commands

    • luciancotea says:

      The EXECUTE command is pretty straightforward, examples in the Help file are clear.

      However, I wish that EXECUTE command could retrieve the exit info of the invoked programs.

      • Nick Mustacich says:

        Lucian very useful macro’s…. thank you.

        Have you ever written a macro to insert data held in an input box into a chart utilizing the dynamic update feature. If so would you willing to share.

        Thanks – Nick

      • luciancotea says:

        See macro 18 🙂

      • Nick Mustacich says:

        Thank yo for sharing macro #18 …greatly appreciated! I need to clarify a point where in the macro are you specifying the chart to insert the variables from the input boxes. I expected to either specify by “ActiveDocument.GetSheetObject(“CH01″)” or chart name.

        Thanks – Nick

      • luciancotea says:

        You dont add data to an interface object, you add it to the tables in the data model.

        All the interface objects will reflect the changes in the data behind.

      • Nick Mustacich says:

        So for real time update, I would include a PartialReload command at the end of the macro so the chart would update after the reload. Is this correct?

      • luciancotea says:

        No. You are modifing the data in QlikView, a reload would erase the changes. If you need permanent changes, you have to export the data into your database.

      • Nick Mustacich says:

        Good Morning Lucian, I have implemented the Dynamic Update feature so I can add an Action statement, which is
        INSERT INTO * (Territory, [Customer ID], [Product Code], Month)
        VALUES (‘$(vTerritory)’, ‘$(vNewCustID)’, ‘$(vProductCode)’, ‘$(vMonths)’);

        UPDATE Budget;

        This inserts the data into the Budget Table in my Data Model. After this action runs I need to save the just updated Budget Table to a new qvd saving the data I just inserted via a macro. I have tried multiple macro code with no luck.

        Is this possible with a macro setup to run after the Insert command? If yes can you supply the macro code?

        Thank you for your assistance, greatly appreciated!

        Nick

      • luciancotea says:

        Use a TableBox object to export the data to CSV:

        set table = ActiveDocument.GetSheetObject(“TB01”)
        table.Export “Transactions.csv”, “, ”

        then run a separate QVW that updates the existing QVD with the new transactions found in the exported CSV.

      • Nick Mustacich says:

        Lucian,

        Thanks got it….that worked!

        Thank yo very much for all your help.

        Have a great day – Nick

      • luciancotea says:

        You’re welcome!

    • Alex TM says:

      This is very useful Lucian. Thanks

      One question regarding #2. How would I edit it so that a table with 10 columns, had columns 3-10 with a width of 250 pixels?

  2. Hi!
    I was trying to send mails with Macros, but with my company mail (hosted).
    When i sent a mail with google mail, there’s no problem, but when I modify the parameters to send a mail with my company mail, it shows an error (transpor error with the conection to the server)

    msgConf.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2
    msgConf.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “mail.qvalue.cl”
    msgConf.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 2525

    msgConf.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1
    msgConf.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “user@mail.com”
    msgConf.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “password”
    msgConf.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = 0

    (the configuration tutorial says that if i want to send mails witout ssl, I have to use these parameters)

    What is wrong with these?

    Regards

  3. luciancotea says:

    You have to take it to the local system admin. Maybe you have the wrong settings or you can’t reach the server.

  4. Timo says:

    Hi,
    thanks for sharing this!

    I’ve been searching though how to get a system name of QV settign field to be used in macro?

    For example in Expression sheet of Chart properties there’s a checkbox ‘Values on Data Points’. Could you advice how do I get the name of that or any other field for using it in macro?

    Thanks for your help in advance,

    Timo

    • luciancotea says:

      To learn object properties, you can use the “API Guide” QlikView file that comes with QlikView installation. Or you can download it from http://community.qlikview.com/docs/DOC-2640

      Here’s your example:

      set chart = ActiveDocument.ActiveSheet.CreateBarChart
      chart.AddDimension “ProductType”
      chart.AddExpression “count(Customer)”
      set p = chart.GetProperties
      set expr = p.Expressions.Item(0).Item(0).Data.ExpressionVisual
      expr.ShowAsBar = true
      expr.NumbersOnBars = true
      chart.SetProperties p

  5. Benny says:

    Do I need both parts of this script for the macro? If so, what is fName?

    FUNCTION ExportObjectToJpg( ObjID, fName)
    ActiveDocument.GetSheetObject(ObjID).ExportBitmapToFile fName
    END FUNCTION

    SUB CallExample
    ExportObjectToJpg “CH01”, “C:\CH01Image.jpg”
    END SUB

    And is it possible to run this macro in the LOAD script to automate this?

    Thanks

    • luciancotea says:

      The FUNCTION() is the part that does the actual export. You can copy and paste it in your macro editor as is. ObjID is the object ID that you want to export and fName is the file name used for export.

      The SUB part is an example of usage.

      You can run a macro in the LOAD script: https://luciancotea.wordpress.com/2013/07/04/call-vba-macro-function-from-the-load-script/

      • Benny says:

        Thanks I got the function to work but it’s not working in the LOAD script.

        This is what I have in my LOAD script: LET vImage = ImageExport;

        ImageExport is the name of the macro.

      • luciancotea says:

        In your case, I believe you try to call interface objects. They are not available at LOAD time.

        You have 2 options:

        1. Run your macro on a trigger OnPostReload (but it won’t work on a server)
        2. Run your macro from an external vbs file (see macro no 9)

      • Benny says:

        What do you mean when “calling an interface object”?

        I am trying to export a jpg image of an object in macro (code from your macro #4) but want this done automatically after every reload so I wanted to put it in the LOAD script.

        I cannot use OnPostReload because I want to use the QEMC for a scheduled reload.
        And does macro #9 require Task Scheduler? Or can that be done in LOAD script?

        Thanks

      • luciancotea says:

        ActiveDocument.GetSheetObject(ObjID).ExportBitmapToFile = “calling an interface object”

        1. In QlikView, use a button (ID = “BU01”) to run your export function.
        2. Copy macro #9 into a .vbs file, and set it to run with Task Scheduler.

      • Benny says:

        Unfortunately, I have come up with the same idea only using a batch file. I cannot use Task Scheduler because that needs my computer to be on. The image that gets sent out daily is needed on the weekends as well. Is there any other work around this?

        Thanks for all your help and patience

      • luciancotea says:

        1. Run it from the server. 2. Use a scheduler that runs as a service like JIT (http://www.gibinsoft.net/gipoutils/fileutil/)

  6. Praveen says:

    Hi,
    I want to create Straight table from VBScript. with the help of one input box and one button i will do some modifications on table content and then display. Is it correct way of using VBSCRIPT for this requirement Or is there any other way to achieve this. ?

  7. Prajna Alva says:

    hi,
    can u suggest how to export XML data in a variable from qlikview.

    Regards,
    Prajna

    • luciancotea says:

      Hi Prajna,

      I’m not sure what your question is. For quick and good help you can post your question with all the details (with an example attached) on http:\\community.qlikview.com

      Lucian

  8. jj says:

    is it possible to use ExportBitmapToFile function to get objects from an inactive sheet ?
    I tried to set up a button in one sheet that prints another that is currently not in display.

    The result always seem to be a blank white page.
    Even if run a macro line to activate the sheet, export, then activate back my original sheet, it wont work.

    is there a solution to this ?

  9. jj says:

    thanks, i realize i was missing the line “ActiveDocument.GetApplication.WaitforIdle”

  10. Alejandro says:

    Hello good day. wanted to know if there is any solution or any ultizar formulated to 18 digits in the numbers without becoming in scientific notation? or if you can take them as text to numbers and as the could add? I need to use 18 digit numbers to add them and you do not lose prescicion. thank you very much

  11. Agilan says:

    Hi, I want to export a table object into an copyable table format and use it in the body of the mail when the dashboard is hosted on the web server (run mode). How can I do it.

  12. Hans says:

    Hi, I want to take a data source .. load it and save off multiple data files on the fly with different content based on the query I use .. (i.e. different group by clauses and where clauses).
    Once files are created use indicidual data files in different tabs containing dashboards.
    Is that possible ?

  13. Josephine says:

    For Number 14, Any idea whether a server reload will flush away the imported variables?

  14. fabian says:

    How to automate the loading of data from an Excel file?

  15. Hi, thanks for sharing this!
    I’ve posted this the Qlik community (http://community.qlik.com/thread/118580) but maybe you can help me with this.

    I wrote a macro (VBS) to print a report to PDF and then mail it.
    I need to loop through a field to generate a PDF report for each value and then get the email address associated (ie, the possible selection on the email field).
    However, the code doesn’t work perfectly as sometimes when the macro evaluates the possible values for the email field, the current selections on the first field change and I end up emailing reports that don’t have an associated email and therefore shouldn’t be sent.

    Can you help me with this?? Thank you very much.

    Here is a piece of the macro code:

    set mySelections = ActiveDocument.Fields(FieldName).GetPossibleValues(10000, True)
    ‘Loop through FieldName
    Dim i
    for i = 0 to mySelections.Count – 1
    ActiveDocument.Fields(FieldName).Clear
    ActiveDocument.Fields(EmailFieldName).Clear

    ActiveDocument.GetApplication.WaitForIdle
    Dim FieldValue
    FieldValue = mySelections.Item(i).text
    ActiveDocument.Fields(FieldName).Select FieldValue
    ActiveDocument.GetApplication.WaitForIdle

    Print_PDF FieldValue, reportName, in_ReportID, reportPath

    ‘ here is the line where the field selection changes
    set thisEmailSelection = ActiveDocument.Fields(EmailFieldName).GetPossibleValues(1)

    If thisEmailSelection.Count = 1 Then
    Dim EmailAddress
    EmailAddress = thisEmailSelection.Item(0).text

    If len(EmailAddress) > 10 Then
    SendMail FieldValue, reportPath, reportName, in_ReportID, EmailAddress
    End If
    End If

    set thisEmailSelection = Nothing
    Next
    set mySelections = Nothing

    end sub

    Thanks again,
    Kind Regards,
    Marina C.

  16. Marcus says:

    It’s very helpful

  17. Luca says:

    Just found this lurking in the net. AWESOME.

    Thank you man, u spared me tons of hours!!!

  18. sampath botla says:

    Hi Lucas, That email macro is not working in my personal system, and if we want that macro to run in remote desktop what else we need to run the macro.

    Thanks & Regards

  19. Gerardo says:

    hi Lucian,
    it is possible?
    select data into ‘variable’ from table where ??

    Thanks & Regards

  20. Cristian D. says:

    Hi Lucian,

    I’m trying to create a macro in order to export all the expressions from all the qvw files from a given folder. My idea is to create a macro for export and a sub call for that macro inside a loop in load script but i don’t know if that is possible given that objects can not be accessed from the load script. I tried to make a function to call the sub, but it seems to do nothing.

    Can you please give me a suggestion for my issue?

    Many thanks,

    Cristian D.

  21. Cristian D. says:

    Hi again,

    I have just downloaded the free version of QV Plus but it seems to work with only one application at a time. I have hundreds of applications whose expressions have to be exported daily and in this case QV Plus is not a solution 😦 The result should be an automatic daily process.

    Cristi

  22. Gonçalo says:

    I am trying to export automatically all the objects in my qlikview sheet but I don’t understand why not all of them are being automatically being exported.

    I use a external vbs script that loads the qlikview file, runs it, and then it presses automatically a button inside qlikview and then closes qlikview. Inside my qlikview file I have the dashboard and a button that has another vb code that exports the charts into image files to a path I want.

    Automatically the exportation does not export them all, but if I go and press manually the button, all files are exported to the destination path. Why?

    My vbs code:

    Set MyApp = CreateObject(“QlikTech.QlikView”)
    Set MyDoc = MyApp.OpenDoc (“C:\qlikview\dsiON-dsiCS-TV-04092014_1.qvw”)
    Set oShell = CreateObject(“WScript.Shell”)
    Set ActiveDocument = MyDoc
    ActiveDocument.ReloadEx 0,1
    Set Button1 = ActiveDocument.GetSheetObject(“BU01”)
    Button1.Press
    MyDoc.GetApplication.Quit
    Set MyDoc = Nothing
    Set MyApp = Nothing

    My exportation code inside qlikview:

    sub Exportation()

    ActiveDocument.ClearAll

    for j= 0 to ActiveDocument.NoOfSheets – 1

    ActiveDocument.ActivateSheet j

    set s=ActiveDocument.Sheets(j)
    Objects = s.GetSheetObjects

    FOR i = 0 TO uBound(Objects)
    SET obj = Objects(i)
    obj.ExportBitmapToFile “C:\xampp\htdocs\dsionauto\”& replace(obj.GetCaption.Name.v,” “,””) & “.png”
    next

    next

    ActiveDocument.ActivateSheet 0

    end sub

    Why the difference and how to solve that? Any clues?

    • luciancotea says:

      After reloading, the document cache is cleared and all the objects needs to be recalculated.

      When you switch to a new sheet, the objects start to compute and you are trying to export before the computation is done.

      So, after sheet switch, add a “ActiveDocument.GetApplication.WaitForIdle” to let QlikView finish before you export.

      You can add a “SLEEP 5000” to delay more, depends on your data and expressions complexity.

  23. Malbordio says:

    Unfortunately it didn’t work regards what I wanted.

    This is how is my script now:

    sub Exportation()

    ActiveDocument.ClearAll

    for j= 0 to ActiveDocument.NoOfSheets – 1

    ActiveDocument.ActivateSheet j

    set s=ActiveDocument.Sheets(j)
    Objects = s.GetSheetObjects

    FOR i = 0 TO uBound(Objects)
    SET obj = Objects(i)
    obj.ExportBitmapToFile “C:\xampp\htdocs\dsionauto\”& replace(obj.GetCaption.Name.v,” “,””) & “.png”
    next

    next

    ActiveDocument.ActivateSheet 0

    ActiveDocument.GetApplication.WaitForIdle

    SLEEP 5000

    end sub

    Did I miss something?

    Can you take a look at this vídeo I made? It can be opened using internet explorer or you may be able to play it using a player too:

    https://drive.google.com/file/d/0BwboNOzedwfgZjJZamNCM01TZG8/edit?usp=sharing

    Thanks in advance.

  24. Malbordio says:

    The SLEEP 5000 wasn’t working so I’ve tried with

    ActiveDocument.GetApplication.Sleep 5000

    It runs successfully but the output is the same.

    I’ve tried to increase the delay time to 50000.

    Got the same results.

  25. luciancotea says:

    The delay part should go after the cycle activate, not the last:

    sub Exportation()

    ActiveDocument.ClearAll

    for j= 0 to ActiveDocument.NoOfSheets – 1

    ActiveDocument.ActivateSheet j

    ActiveDocument.GetApplication.WaitForIdle
    ActiveDocument.GetApplication.SLEEP 5000

    set s=ActiveDocument.Sheets(j)
    Objects = s.GetSheetObjects

    FOR i = 0 TO uBound(Objects)
    SET obj = Objects(i)
    obj.ExportBitmapToFile “C:\xampp\htdocs\dsionauto\”& replace(obj.GetCaption.Name.v,” “,””) & “.png”
    next

    next

    ActiveDocument.ActivateSheet 0

    end sub

    but now I see that you have missing parts, not blanc images. try to post your problem on the community site, you can attach example there.

  26. Malbordio says:

    Lucian, anyway thanks for all your help. It didn’t work but I’ve managed to try something else on my vbs, and I got half of the exported images with leds, just the way I wanted. Anyway, something is not yet right because it’s only half of the graphics.

    The new vbs script (the one I ran externally) is:

    Set MyApp = CreateObject(“QlikTech.QlikView”)
    Set MyDoc = MyApp.OpenDoc (“C:\qlikview\dsiON-dsiCS-TV-04092014_2.qvw”)
    Set oShell = CreateObject(“WScript.Shell”)
    Set ActiveDocument = MyDoc
    ActiveDocument.GetApplication.WaitForIdle
    ActiveDocument.ReloadEx 0,1
    ActiveDocument.GetApplication.WaitForIdle
    Set Button1 = ActiveDocument.GetSheetObject(“BU01”)
    ActiveDocument.GetApplication.WaitForIdle
    Button1.Press
    WScript.Sleep 2000
    ActiveDocument.GetApplication.WaitForIdle
    MyDoc.GetApplication.Quit
    Set MyDoc = Nothing
    Set MyApp = Nothing

  27. Ankit says:

    I have a container object and based on container index 0 or 1 I want to show and hide two different charts. For eg for container index 0 I want to show chat A and for index 1 I want to show Chart B. how is this possible through Macros. Please help out.

  28. Rakesh says:

    I want to update data on webview in realtime.

  29. murreni says:

    Hi, i need to display in a msgbox how long my macro needs for execution.
    how can i implement this in an existing macro?
    any ideas

  30. Hanson says:

    Hi, I encounter a very strange situation,Run Macro is effective in debug mode, but not effective in web view, mode for Version 11 qlikview.

  31. hulisani says:

    Hi, I am trying to export qlikview data to excel with multiple sheet using sheet ID instead of Object ID, any idea on how I can resolve that?

  32. sandeep says:

    Hi,

    Im attempting to rework a macro which emails an Excel workbook. The macro creates a new tab for each field identified (this all works). The problem Im having is the chart table should be filtered to only paste results matching the tab name. This is not working and I was hoping I may find a method to get this working with assistance of those who may be performing similar tasks in QV.

    Im just puzzled to why it channels through the dimension and labels the tabs perfectly fine but when attempting to filter the chart it doesnt work. I knowwhen i select values from a list box in the QV app the chart filters with no issues.

    vb script:

    function OpenExcelandSendEmail()
    ‘ Create Excel export
    ActiveDocument.ClearAll
    set obj = ActiveDocument.GetSheetObject(“CH107”)
    ‘set valname= ActiveDocument.Fields(“BaseIMAirportCode”).GetPossibleValues
    set val=ActiveDocument.Fields(“BaseIMAirportCode”).GetPossibleValues

    strDay=ActiveDocument.Evaluate(“=Date(Today(), ‘MMDDYYYY’)”)

    Set xlApp = CreateObject(“Excel.Application”)
    xlApp.Visible = False
    xlApp.Displayalerts = False
    Set xlWorkbook = xlApp.Workbooks.Add

    xlWorkbook.Worksheets(“Sheet1”).Select

    for i=0 to val.Count-1

    ActiveDocument.Fields(“BaseIMAirportCode”).Select val.Item(i).Text
    ‘ActiveDocument.Fields(“BaseIMAirportCode”).Select val.Item(i).Text
    ActiveDocument.GetSheetObject(“CH107”).CopyTableToClipboard true
    xlWorkbook.ActiveSheet.Paste xlWorkbook.ActiveSheet.Range(“A1”)
    xlWorkbook.ActiveSheet.Name = val.Item(i).Text
    xlWorkbook.Sheets.Add

    next

    xlWorkbook.Worksheets(“Sheet”&val.Count+1).Select
    xlWorkbook.Worksheets(“Sheet”&val.Count+1).Delete

    xlWorkbook.SaveAs
    xlWorkbook.Close
    xlApp.Displayalerts = True
    xlApp.Quit

  33. sandeep says:

    Thank you for the advice, I’ll give that a shot.

  34. sandeep says:

    Hi,

    After reviewing the app and macro again yesterday, it is still not funtioning as intended, and I think the problem may be due to me using the same script built out for a table box vs. chart table. Furthermore, I attempted to change the loop to include:

    ActiveDocument.Fields(“UNIT”).Select “UNIT_VALUE”
    and/or
    Set x = ActiveDocument.Fields(“y”).GetSelectedValues
    ActiveDocument.Fields(“z”).SelectValues x

    which is very mych what I used before, but still with no luck. I was hoping to identify if you had any other ideas that might this report work.

    Thanks

  35. Vinol joy says:

    Hi,

    I am trying to make a a selection from a list box which has an expression in it.

    I generally use the below syntax to select values from list box:-
    listbox3=Array(“”)

    Set Qv = CreateObject(“QlikTech.QlikView”)
    Set QvDoc = Qv.OpenDoc(“”)

    QvDoc.GetSheetObject(“<objectID")
    Set fz = QvDoc.Fields("objectname")
    for each v in listbox3
    fz.ToggleSelect v
    next

    But whenever I try to access an expression from a list box, it will have an objectID but wont have an object name ? How can I solve for this problem ?

  36. San says:

    Hello Luciancotea,

    This is really informative. Thank you for sharing with us.

    Here I have scenario, I have two different folders, one is FTP folder other one is a QV Source folder, Client will update some excel files at FTP folder. As a developer, I have to check the FTP folder, check for the latest file, if there is any. Then I need to copy it to the data source folder and rename it to the standard format. This is a completely manual process. Here I want to do automation. When even my qlikview does the reload, it needs to check boathfolders, based on the latest modified time, Excel file needs to copy to the QV\Datasource folder. How to do that? What is the best practice through QV macros.

    Appreciate your help.

    Thanks
    San

    • luciancotea says:

      Hi San,

      You don’t need macros to achieve that. Set up a daily task that will automatically download the files from the FTP using a tool like WinSPC.

      Then, in your script, you can determine if the file contains new data using various techniques, usually checking a date field.

      For more information, search for “incremental load”.

      Lucian

  37. Shyam says:

    Hi Lucian,

    First of all thank you very much for the effort you have put in to gather these scripts, This is highly appreciable.

    I hope you could quickly suggest on my requirement. I am trying to reload a dashboard with one of the scripts in this post. However, the script says Macro parse failed. Functionality was lost. and mentions ActiveX component can’t create object:QlikTech.QlikView

    Could you please help with this.

    Set MyApp = CreateObject(“QlikTech.QlikView”)
    Set MyDoc = MyApp.OpenDoc (“O:\Office\Admin & Misc\NSWTEAM\Mainpac_DWS\QVSource\TestQVSource.qvw”,””,””)
    Set ActiveDocument = MyDoc
    ActiveDocument.Reload
    Set Button1 = ActiveDocument.GetSheetObject(“BU01”)
    Button1.Press
    MyDoc.GetApplication.Quit
    Set MyDoc = Nothing
    Set MyApp = Nothing

    • luciancotea says:

      If you can’t create the ActiveX object, it could be a security setting (usually on Win Server). Try to identify the problem in script by running only the initial lines.

      • Shyam says:

        Hi Lucian,

        Thank you very much for the quick reply

        Yes, after allowing the system access I got rid of that error. Now I modified my code to something like below:

        set fso = CreateObject(“Scripting.FileSystemObject”)
        dim CurrentDirectory
        CurrentDirectory = fso.GetParentFolderName(ScriptFullName)
        dim qvDocName
        qvDocName = fso.BuildPath(CurrentDirectory, “TestQVSource.qvw”)
        Set MyApp = CreateObject(“QlikTech.QlikView”)
        Set MyDoc = MyApp.OpenDoc (“qvDocName”,””,””)
        Set ActiveDocument = MyDoc
        ActiveDocument.Reload
        Set Button1 = ActiveDocument.GetSheetObject(“BU01”)
        Button1.Press
        MyDoc.GetApplication.Quit
        Set MyDoc = Nothing
        Set MyApp = Nothing

        But this throws an error ‘Wrong number of arguments or invalid property assignment: ‘ActiveDocument’.

        Can you please suggest.

      • luciancotea says:

        Are you sure that fso holds the right value? I am not in front of a computer. ..

  38. Shyam says:

    Hi Lucian,

    I request your apologies. I am new to the VB scripts and forgive me for my poor knowledge.
    Meanwhile I was quickly trying the code that you posted and found the problem as the double codes are different when copy pasted the code.

    Now below is the code that I use and I still get the exact error:Wrong number of arguments or invalid property assignment: ‘ActiveDocument’.

    Set MyApp = CreateObject(“QlikTech.QlikView”)
    Set MyDoc = MyApp.OpenDoc (“O:\Office\Admin & Misc\NSWTEAM\Mainpac_DWS\QVSource\TestQVSource.qvw”,””,””)
    Set ActiveDocument = MyDoc
    ActiveDocument.Reload
    Set Button1 = ActiveDocument.GetSheetObject(“BU01”)
    Button1.Press
    MyDoc.GetApplication.Quit
    Set MyDoc = Nothing
    Set MyApp = Nothing

    I appreciate your help on this.

    Regards,
    Shyam

  39. santosh says:

    Can anyone tell me how to call a URL with one parameter and get the response from that and store the result in one variable.

  40. everest says:

    is there micro to zoom only object not the window

  41. André says:

    Thanks a Lot !

    As you have a macro to export chart to Excel, can you have a such macro to send Graph as picture in a powerpoint presentation.

    Thansk again !

  42. Marcos says:

    The macro posted here to send an email using Gmail doesnt work, I get this error:
    The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response was not available
    Can you please help?
    Thanks!

  43. Jürgen says:

    I used a very simple code from yours, ticked VBscript and put System Security to System access:
    SUB SaveAndQuit
    ActiveDocument.Save
    ActiveDocument.GetApplication.Quit
    END SUB
    But nothing happens. When I put after the code: msgbox (“hello”) It will show a textbox
    Is there another box to tick somewhere? Its so weird. After hitting “Test” is should save & close the document. Using the Personal Edition 12.0
    Any idea?

  44. Pankaj says:

    Great post!!!

    I am using macros to show/hide TabRow but can you tell me the macros for show/hide textobject in QV??

  45. Marcel says:

    Hi

    First of all thanks for your list of VBscript examples.

    I need to create an Generic list of type String, that I want to fill with some values.

    I tried the following statements

    Dim sList
    Set sList = CreateObject(“System.Collections.Generic.List[String]”)
    sList.Add(“Read”)
    sList.Add(“Stop”)

    This is not working.

    Can you please provide me with the correct code?

  46. jean duluc says:

    Is there any way to access AxQlikOCXLib the way you have shown way to access QlikView like CreateObject(“QlikTech.QlikView”)?

  47. Ravikiran says:

    Is there any way that i can save & close the user session by clicking a button on a dashboard

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: