QlikView documentation and resources.
Hope the below macro scripts helps beginners
Source : QlikView Macros – Useful collection | Lucian Cotea
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) Save and exit QlikView
SUB SaveAndQuit ActiveDocument.Save ActiveDocument.GetApplication.Quit END SUB
5) 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 DestinationGroup.SetProperties DestinationGroupProperties ELSE SourceGroupProperties.IsCyclic = true DestinationGroupProperties.SetProperties SourceGroupProperties END IF 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
6) 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
7) 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
😎 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
9) 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
10) Get reports information
function countReports set ri = ActiveDocument.GetDocReportInfo countReports = ri.Count end function function getReportInfo
set ri = ActiveDocument.GetDocReportInfo set r = ri.Item(i) getReportInfo = r.Id & "," & r.Name & "," & r.PageCount & CHR(10) end function
11) 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
12) Changing Font setting of an Object
SUB Font()
set obj = ActiveDocument.GetSheetObject("BU01")
set fnt = obj.GetFrameDef.Font
fnt.PointSize1000 = fnt.PointSize1000 + 1000
fnt.FontName = "Calibri"
fnt.Bold = true
fnt.Italic = true
fnt.Underline = true
obj.SetFont fnt
END SUB
13. To Show and Hide Tab row.
Sub ShowTab
rem Hides tabrow in document properties
set docprop = ActiveDocument.GetProperties
docprop.ShowTabRow=true
ActiveDocument.SetProperties docprop
End Sub
Sub HideTab
rem Hides tabrow in document properties
set docprop = ActiveDocument.GetProperties
docprop.ShowTabRow=false
ActiveDocument.SetProperties docprop
End Sub
14. Always One Selected Enable / Disable setting through Macro
Sub AlwaysOneSelected
set obj = ActiveDocument.GetSheetObject("LB02")
set boxfield=obj.GetField
set fprop = boxfield.GetProperties
fprop.OneAndOnlyOne = True
boxfield.SetProperties fprop
End Sub
Sub RemoveAlwaysOneSelected
set obj = ActiveDocument.GetSheetObject("LB02")
set boxfield=obj.GetField
set fprop = boxfield.GetProperties
fprop.OneAndOnlyOne = False
boxfield.SetProperties fprop
ActiveDocument.ClearAll True
End Sub
15. Reading Rows and Columns in a table object
Sub ReadStraightTable
Set Table = ActiveDocument.GetSheetObject( "CH01" )
For RowIter = 0 to table.GetRowCount-1
For ColIter = 0 to table.GetColumnCount-1
set cell = table.GetCell(RowIter,ColIter)
Msgbox(cell.Text)
Next
Next
End Sub
16. Get number of Rows in a Straight or Pivot tables
function ReadRowsCount
set v = ActiveDocument.GetVariable("variableName")
v.SetContent ActiveDocument.GetSheetObject( "CH01" ).GetRowCount-1, true
end function
17. Get and Set variable values in macros
function setVariable(name, value)
set v = ActiveDocument.GetVariable("variableName")
v.SetContent value,true
end function
function getVariable(name)
set v = ActiveDocument.GetVariable("variableName")
getVariable = v.GetContent.String
end function
18. Export chart data to QVD file, the chart may Bar/Line/StraightTable/Pivot etc.
sub ChartToQVD
set obj = ActiveDocument.GetSheetObject("CH01")
obj.ExportEx "QvdName.qvd", 4
end sub
19. Export Charts as image for each value selection in a Listbox
FUNCTION ExportObjectToJpg( ObjID, fName)
ActiveDocument.GetSheetObject(ObjID).ExportBitmapToFile fName
END FUNCTION
SUB ExportChartByListboxValues
DIM fname, value, filePath, timestamp
filePath = ActiveDocument.Variables("vPDFFlagPath").GetContent.STRING
timestamp = Year(Now()) & DatePart("m", Now()) & DatePart("d", Now()) & DatePart("h", Now()) & DatePart("n", Now()) & DatePart("s", Now())
SET Doc = ActiveDocument
fieldName = "EmployeeID"
SET Field = Doc.Fields(fieldName).GetPossibleValues
FOR index = 0 to Field.Count-1
Doc.Fields(fieldName).Clear
Doc.Fields(fieldName).SELECT Field.Item(index).Text
fileName = Field.Item(index).Text & "_" & timestamp & ".jpg"'Field.Item(index).Text & DateValue
ExportObjectToJpg "CH420", filePath & fileName
NEXT
Doc.Fields(fieldName).Clear
END SUB
20. Checks whether given folder exists if not creates the given folder
Function CheckFolderExists(path)
Set fileSystemObject = CreateObject("Scripting.FileSystemObject")
If Not fileSystemObject.FolderExists(path) Then
fileSystemObject.CreateFolder(path)
End If
End Function
21. Minimize the chart object and move the chart position 20 pixels down and 15 right
Sub MoveChart
set mybox = ActiveDocument.GetSheetObject("CH09")
mybox.Minimize
set fr = mybox.GetFrameDef
pos = fr.MinimizedRect
pos.Top = pos.Top + 20
pos.Left = pos.Left + 15
mybox.SetFrameDef fr
end sub
22. Move Chart Object 20 pixels down and 15 right
Sub MoveChart
set obj = ActiveDocument.GetSheetObject("CH09")
pos = obj.GetRect
pos.Top = pos.Top + 20
pos.Left = pos.Left + 15
obj.SetRect pos
End Sub
23. Export Table charts Side by Side in a single Excel sheet
Function ExportCharts()
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = true
Set xlDoc = xlApp.Workbooks.Add 'open new workbook
nSheetsCount = 0
CALL RemoveDefaultSheet(xlDoc)
nSheetsCount = xlDoc.Sheets.Count
xlDoc.Sheets(nSheetsCount).Select
Set xlSheet = xlDoc.Sheets(nSheetsCount)
CALL ExportRevenueWidgets(xlDoc,xlSheet)
End Function
'Call Export Widgets By Sheet
Function ExportRevenueWidgets(xlDoc,xlSheet)
CALL Export(xlDoc,xlSheet,"CH09", "A")
CALL Export(xlDoc,xlSheet,"CH09", "D")
End Function
'Export Widgets
Function Export(xlDoc, xlSheet,widgetID, columnStart)
nRow = xlSheet.UsedRange.Rows.Count
nRow = 1
Set SheetObj = ActiveDocument.GetSheetObject(widgetID)
'Copy the chart object to clipboard
SheetObj.CopyTableToClipboard true
'Paste the chart object in Excel file
xlSheet.Paste xlSheet.Range(columnStart&nRow)
End Function
'Remove Default Sheets from Excel Files
Sub RemoveDefaultSheet(xlDoc)
Do
nSheetsCount = xlDoc.Sheets.Count
If nSheetsCount = 1 then
Exit Do
Else
xlDoc.Sheets(nSheetsCount).Select
xlDoc.ActiveSheet.Delete
End If
Loop
End Sub
24. Setting Scroll bar of a chart to Right side by default
SUB StartScrollRight
SET chartObject = ActiveDocument.GetSheetObject("CH01")
SET chartProperties = chartObject.GetProperties
chartProperties.ChartProperties.XScrollInitRight = true
chartObject.SetProperties chartProperties
END SUB
25. Show hide expression in Straight / Pivot table
Sub ShowHideExpression()
SET chartObj = ActiveDocument.GetSheetObject("CH01")
SET chartProp= chartObj.GetProperties
SET expr = chartProp.Expressions.Item(1).Item(0).Data.ExpressionData
expr.Enable = False // Hides First expression
SET expr = chartProp.Expressions.Item(2).Item(0).Data.ExpressionData
expr.Enable = True // Displays Second expression
End Sub
26. To reset InputField values
Sub ResetInputField
' Reset the InputField
set fld = ActiveDocument.Fields("InputFieldName")
fld.ResetInputFieldValues 0, 0 ' 0 = All values reset, 1 = Reset Possible value, 2 = Reset single value
End Sub
27. To set InputField values
Sub SetInputField
set fld = ActiveDocument.Fields("Budget")
fld.SetInputFieldValue 0, "999" ' Sets InputField value to 999
End Sub
28. Clear specific Fields
SUB ClearFields
SET Doc = ActiveDocument
Doc.Fields(FieldName1).Clear
Doc.Fields(FieldName2).Clear
Doc.Fields(FieldName3).Clear
Doc.Fields(DateFieldNameN).Clear
END SUB
29. Export chart to CSV
SUB ExportChartToCSV
SET objChart = ActiveDocument.GetSheetObject("CH01")
objChart.Export "C:\Data.CSV", ", "
END SUB
30. Fit zoom to Window
Sub FitZoomToWindow
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.ActiveSheet.FItZoomToWindow
End Sub
31. Macro to get fast change chart type in a variable
10 - Pivot Table
11 - Straight Table
12 - Bar
15 - Line
Sub GetChartType()
set chart = ActiveDocument.getsheetobject("CH01")
set p = chart.GetProperties
set v = ActiveDocument.GetVariable("vFastChangeChartType")
v.SetContent chart.GetObjectType,true
end sub
32. Open IE browser with URL based on a selected Dimension value - Use below macro in Document Properties Field Event Triggers
Create a variable
vSelectedURL : =Only([Image Location])
Sub Browse()
set v = ActiveDocument.GetVariable("vSelectedURL")
Set ie = CreateObject("Internetexplorer.Application")
ie.Visible = True
ie.Navigate v.GetContent.String
End Sub
33. Export multiple chart to Microsoft PowerPoint slides
Sub ExportPPT
Set objPPT = CreateObject("PowerPoint.Application")
objPPT.Visible = True
Set objPresentation = objPPT.Presentations.open("YourPath\ppt.pptx")'file Path
Set PPSlide =objPresentation.Slides.Add(1,12)
ActiveDocument.GetSheetObject("CH1").CopyBitmapToClipboard
PPSlide.Shapes.Paste
PPSlide.Shapes(PPSlide.Shapes.Count).Top = 150 'This sets the top location of the image
PPSlide.Shapes(PPSlide.Shapes.Count).Left = 15 'This sets the left location
PPSlide.Shapes(PPSlide.Shapes.Count).Width = 240
PPSlide.Shapes(PPSlide.Shapes.Count).Height = 250
Set PPSlide = objPresentation.Slides.Add(1,12)
ActiveDocument.GetSheetObject("CH2").CopyBitmapToClipboard
PPSlide.Shapes.Paste
PPSlide.Shapes(PPSlide.Shapes.Count).Top = 150 'This sets the top location of the image
PPSlide.Shapes(PPSlide.Shapes.Count).Left = 15 'This sets the left location
PPSlide.Shapes(PPSlide.Shapes.Count).Width = 100
PPSlide.Shapes(PPSlide.Shapes.Count).Height = 200
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
End Sub
34.Copy to Clip Board -Bit Map Image
Sub CopyObject
ActiveDocument.GetSheetObject("CH01").CopyBitmapToClipboard
End sub
35. Append data to existing Excel file
Sub AppendDataToExcel
dim doc, xlApp, xlDoc, xlSheet, LastRow
Const xlUp = -4162
set doc = ActiveDocument
Set xlApp = CreateObject("Excel.Application")
Set xlDoc = xlApp.Workbooks.Open("C:\Data.xlsx") ' Change filepath
xlapp.Visible = true ' you can also set it to false so that process done in background
Set xlSheet = xlDoc.Worksheets("Sheet1") ' Replace Sheet1 with your sheet name
xlSheet.Activate
LastRow = xlSheet.Cells(xlSheet.Rows.Count, 1).End(xlUp).Row
msgbox LastRow
xlSheet.Cells(LastRow + 1, 1).Select
doc.GetSheetObject("TB01").CopyTableToClipboard true 'Replace TB01 with your chart object ID
xlSheet.Paste
xlDoc.Save
xlDoc.Close
xlApp.Quit
End Sub
36. Export all objects of a Container to Excel
sub Export
set oXL = CreateObject("Excel.Application")
oXL.DisplayAlerts = False
oXL.visible=True
Dim oXLDoc 'as Excel.Workbook
Dim i
Set oXLDoc = oXL.Workbooks.Add
'---------------------------------------
Set ContainerObj = ActiveDocument.GetSheetObject("CT02")
Set ContProp=ContainerObj.GetProperties
aSheetObj=Array("CH02","CH03","CH06")
'---------------------------------------
for i=0 to UBound(aSheetObj)
'ActiveDocument.GetApplication.WaitForIdle
oXL.Sheets.Add
oXL.ActiveSheet.Move ,oXL.Sheets( oXL.Sheets.Count )
ContProp.SingleObjectActiveIndex = i
ContainerObj.SetProperties ContProp
Set oSH = oXL.ActiveSheet
oSH.Range("A1").Select
Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))
obj.CopyTableToClipboard True
oSH.Paste
sCaption=obj.GetCaption.Name.v
set obj=Nothing
oSH.Rows("1:1").Select
oXL.Selection.Font.Bold = True
oSH.Cells.Select
oXL.Selection.Columns.AutoFit
oSH.Range("A1").Select
oSH.Name=left(sCaption,30)
set oSH=Nothing
next
Call Excel_DeleteBlankSheets(oXLDoc)
oXL.DisplayAlerts = True
'// Finally select the first sheet
oXLDoc.Sheets(1).Select
'---------------------------------------
set oXL =Nothing
set oXLDoc =Nothing
end sub
Private Sub Excel_DeleteBlankSheets(ByRef oXLDoc)
For Each ws In oXLDoc.Worksheets
If (not HasOtherObjects(ws)) then
If oXLDoc.Application.WorksheetFunction.CountA(ws.Cells) = 0 Then
On Error Resume Next
Call ws.Delete()
End If
End If
Next
End Sub
'// ****************************************************************
'// Helper function to determine if there are other objects placed
'// on the sheet ...
'// ****************************************************************
Public Function HasOtherObjects(ByRef objSheet) 'As Boolean
Dim c
If (objSheet.ChartObjects.Count > 0) Then
HasOtherObjects = true
Exit function
End If
If (objSheet.Pictures.Count > 0) Then
HasOtherObjects = true
Exit function
End If
If (objSheet.Shapes.Count > 0) Then
HasOtherObjects = true
Exit function
End If
HasOtherObjects = false
End Function
37. Get list of bookmarks in Variable
Sub GetBookmarks
bookmarks = ActiveDocument.GetDocBookmarkNames
dim BM
for i = lbound(bookmarks) to ubound(bookmarks)
if(i=0) then
BM="'"&bookmarks(i)&"'"
else
BM=BM&",'"&bookmarks(i)&"'"
end if
next
set v = ActiveDocument.GetVariable("vVariable")
v.SetContent BM,true
End Sub
38. Export charts for each bookmark in separate excel sheets
Function ExportCharts()
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = true
Set xlDoc = xlApp.Workbooks.Add 'open new workbook
nSheetsCount = 0
CALL RemoveDefaultSheet(xlDoc)
bookmarks = ActiveDocument.GetDocBookmarkNames
for i = lbound(bookmarks) to ubound(bookmarks)
ActiveDocument.RecallDocBookmark bookmarks(i)
nSheetsCount = xlDoc.Sheets.Count
msgbox nSheetsCount
xlDoc.Sheets(nSheetsCount).Select
Set xlSheet = xlDoc.Sheets(nSheetsCount)
msgbox xlSheet.Name
CALL ExportRevenueWidgets(xlDoc,xlSheet)
' set nSheetsCount = nSheetsCount + 1
if i <> ubound(bookmarks) then
xlDoc.Sheets.Add
xlDoc.ActiveSheet.Move ,xlDoc.Sheets( xlDoc.Sheets.Count )
end if
' msgbox xlDoc.Sheets.Count
next
End Function
'Call Export Widgets By Sheet
Function ExportRevenueWidgets(xlDoc,xlSheet)
CALL Export(xlDoc,xlSheet,"CH01", "A")
CALL Export(xlDoc,xlSheet,"CH02", "D")
End Function
'Export Widgets
Function Export(xlDoc, xlSheet,widgetID, columnStart)
nRow = xlSheet.UsedRange.Rows.Count
nRow = 1
Set SheetObj = ActiveDocument.GetSheetObject(widgetID)
'Copy the chart object to clipboard
SheetObj.CopyTableToClipboard true
'Paste the chart object in Excel file
xlSheet.Paste xlSheet.Range(columnStart&nRow)
End Function
'Remove Default Sheets from Excel Files
Sub RemoveDefaultSheet(xlDoc)
Do
nSheetsCount = xlDoc.Sheets.Count
If nSheetsCount = 1 then
Exit Do
Else
xlDoc.Sheets(nSheetsCount).Select
xlDoc.ActiveSheet.Delete
End If
Loop
End Sub
Regards,
jagan.
Hi Jagan,
Do you have a macro that can delete a file, I am managing files and folders using a Qlikview File Tracker.
When I select a file I can open the file of the Directory, I want a macro to delete a file
Do you have one?
Does the Export Object to JPG work under QV10 Webview / AJAX Zero Footprint?
It doesnt work.
I guess is something fo security..
what i can do?
Does someone have a QVW Example?
put module security to system access
Also note that macros do not run under the AJAX ZFC webview.
Macro abuse is a sin !
Hi Jagan,
Really good work , appreciate to your valuable effort on this.
Thanks for sharing good information...
Useful , but this is only for Qlikview desktop version.
It's better if can provide for Qlikview server version.
Buenas soy nuevo en qlikview y quiero saber mas, donde aplico todos estos macros podrían poner un ejemplo si no fuera mucha molestia.
Good I'm new to qlikview and want to know more that I apply all these macros could give an example if it was not too much trouble.
Hi Yan,
Macros are used only in Qlikview Desktop version and not in Qlikview Server.
Regards,
Jagan.