From version 1.2.7 of QVExcel onwards VBA API has been added. This API is documented below.
Using the API it is possible to extract data from QlikView, run expressions, make selections in listboxes as well as export value only versions of your reports which could then be emailed or distributed to file shares.
It is recommended that you ensure you have a copy of the 'QVExcel VBA API Demo.xls' demo file which illustrates the API in action.
In order to communicate programatically with QVExcel you first need to get a handle on the Add-in using the following code:
Set qvExcel = Application.COMAddIns("IndustrialCodeBox.QVExcel").Object
The remainder of this page illustrates the functionality which is then supported through the VBA interface.
The Reconnect function will attempt to reconnect QVExcel to the QlikView document which has been configured for the current Excel file (i.e. the server and application name configured on the QVExcel_Settings sheet). It is similar to pressing the 'Connect' button in the QVExcel user interface.
Sub Reconnect()
Set qvExcel = Application.COMAddIns("IndustrialCodeBox.QVExcel").Object
qvExcel.Reconnect
End Sub
This is useful, for example, if you would like to reconnect each time a certain Excel file is opened. To do this you could place the following code in the ThisWorkbook VBA module.
Private Sub Workbook_Open()
Set qvExcel = Application.COMAddIns("IndustrialCodeBox.QVExcel").Object
qvExcel.Reconnect
End Sub
Calling the UpdateReport function is equivalent to pressing the 'Update WorkBook' button in the QVExcel user interface.
Sub UpdateReport()
Set qvExcel = Application.COMAddIns("IndustrialCodeBox.QVExcel").Object
qvExcel.UpdateReport
End Sub
The following code illustrates how a listbox selection can be made.
Sub MakeSelection()
Set qvExcel = Application.COMAddIns("IndustrialCodeBox.QVExcel").Object
Set lb = qvExcel.GetListBox("LB1457")
Set Item = lb.FindItem("Alan Alda")
lb.Select (Item)
End Sub
The following code illustrates how to loop through each item in a listbox.
Sub WalkingThroughAListBox()
Set qvExcel = Application.COMAddIns("IndustrialCodeBox.QVExcel").Object
Set lb = qvExcel.GetListBox("LB1457")
Dim res As String
For i = 0 To lb.NoItems - 1
Set Item = lb.Item(i)
' Could make a selection here
'lb.Select (Item)
res = res & ", " & Item.Text
Next i
MsgBox res
End Sub
The following code illustrates how to clear just the selections in a particular listbox.
Sub ClearDirector()
Set qvExcel = Application.COMAddIns("IndustrialCodeBox.QVExcel").Object
Set lb = qvExcel.GetListBox("LB1457")
lb.Clear
End Sub
To clear the selections in all listboxes use the following.
Sub ClearAll()
Set qvExcel = Application.COMAddIns("IndustrialCodeBox.QVExcel").Object
qvExcel.ClearAll
End Sub
Any QlikView expression can be evaluated from VBA using the following method.
Sub ExecuteExpression()
Set qvExcel = Application.COMAddIns("IndustrialCodeBox.QVExcel").Object
result = qvExcel.ExecuteExpression("getcurrentselections('#')")
MsgBox result
End Sub
Entire objects can be extracted using VBA similar to the following. This should work on most object types.
Sub ExtractTable()
objectId = "CH03"
Application.StatusBar = "Extracting..."
Application.ScreenUpdating = False
Dim qvExcel, RowCount, ColCount, startRowIndex, startColIndex, r, c, calcMode
Set qvExcel = Application.COMAddIns("IndustrialCodeBox.QVExcel").Object
RowCount = qvExcel.GetRowCount(objectId)
ColCount = qvExcel.GetColumnCount(objectId)
startRowIndex = 28
startColIndex = 5
Set ws = Worksheets("Report")
ws.Cells(startRowIndex, startColIndex).CurrentRegion.Clear
calcMode = Application.Calculation
Application.Calculation = xlCalculationManual
For r = 1 To RowCount
For c = 1 To ColCount
ws.Cells(r + startRowIndex, c + startColIndex - 1).Value = qvExcel.GetCellValue(objectId, r - 1, c - 1)
Next c
Next r
Application.Calculation = calcMode
Application.Calculate
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub
Using the QVExcel user interface it is possible to create 'detached' versions of the reports you create which can be viewed (but not interacted with) by users who do not have QVExcel installed. The same functionality is now available through the VBA API as illustrated below. This creates a copy of the Excel file in the location you specify with any QVExcel dependent formulas converted to values. Sheets can also be removed from the file as illustrated below. Using this technique it is also then possible to send these reports as email attachments to your users or distribute them to file shares.
Sub ExportReport()
Set qvExcel = Application.COMAddIns("IndustrialCodeBox.QVExcel").Object
Dim sheetsToRemove(1) As String
sheetsToRemove(1) = "QVExcel_Settings"
Set qvExcel = Application.COMAddIns("IndustrialCodeBox.QVExcel").Object
' The second argument to this function can take the following values
' ConvertAllFormulasToValues = 1
' ConvertQVExcelFormulasToValues = 2
' DoNothing = 3
'
Call qvExcel.CreateCopyWithValues("c:\myreport.xls", 1, sheetsToRemove)
End Sub
You may like to run your own VBA routines after a QVExcel related update has been carried out. To do this simply create a sub in the ThisWorkbook module of your Excel file and it will fire after the user has clicked either of the update buttons in the QVExcel panel.
Sub OnQVExcelUpdated()
MsgBox "Update WorkBook or Update Sheet Clicked"
End Sub
If you have any questions regarding the VB API or suggestions for functionality you would like to see added then please contact us.