Qlik Community

QlikView Documents

Documents for QlikView related information.

Useful Qlikview Macros

MVP & Luminary
MVP & Luminary

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

😎 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
szeconku
New Contributor

I think you need to specify the one selected value you want to select.

Try something like:

Sub AlwaysOneSelected 

  set obj = ActiveDocument.GetSheetObject("MB06") . Select "YourValueHere"

  set mbp=obj.GetField 

  set fprop = mbp.GetProperties 

  fprop.OneAndOnlyOne = True 

  mbp.SetProperties fprop 

End Sub 

0 Likes
leowang88
Visitor II

Hi Jagan,

It's very useful for me, but i have one question how to export all variables of qv into a file?

In above script , you had mentioned how to get one variable with variable name , is there a way that i can export all variables, don't input any variables name?

0 Likes
Chanty4u
Esteemed Contributor III

if not macro try this

Export Variables.zip

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