Qlik Community

QlikView Documents

Documents for QlikView related information.

Useful Qlikview Macros

MVP
MVP

Useful Qlikview Macros

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

8) 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.

Comments
rustyfishbones
Honored Contributor II

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?

thornofcrowns
Valued Contributor II

Does the Export Object to JPG work under QV10 Webview / AJAX Zero Footprint?

angelaecheverri
Contributor

It doesnt work.

I guess is something fo security..

what i can do?

Does someone have a QVW Example?

bumin
Contributor II

put module security to system access

thornofcrowns
Valued Contributor II

Also note that macros do not run under the AJAX ZFC webview.

carbal1952
Contributor II

Macro abuse is a sin !

Not applicable

Hi Jagan,

Really good work , appreciate to your valuable effort on this.

Thanks for sharing good information...

Not applicable

Useful , but this is only for Qlikview desktop version.

It's better if can provide for Qlikview server version.

Not applicable

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.

MVP
MVP

Hi Yan,

Macros are used only in Qlikview Desktop version and not in Qlikview Server.

Regards,

Jagan.

Version history
Revision #:
1 of 1
Last update:
‎09-20-2013 12:11 PM
Updated by: