topic Re: Qlikview with VBA in QlikView

Qlikview with VBA

alan_grn — Mon, 26 Jan 2026 18:19:17 GMT

I am trying to create apps that use the OCX to display Qlikview data in an Excel spreadsheet

Where can I find resources on this?.

For example I am trying to connect Excel to Qlikview and then display a list of available fields in the spreadsheet.

I would liek the code to be in Excel as opposed to Qlikview creating an Excel document.

Qlikview with VBA

Thu, 04 Nov 2010 10:00:43 GMT

Hi,
The below is some code cut from a fairly large macro that I have several Users working with at the moment. It is for Qlikview to Excel; I did start Excel to Qlikview and back but that was just too painful and this works so for 'our' purposes has been fine.
Hopefully, it will give you a flavour of what you can achieve. As for resources, you should have an APIguide.qvw installed within the 'Documentation' directory of Qlikview, but bear in mind that what you will be creating is a mix of Qlikview and vba. Anyway, good luck.....................

Sub Excel_Table_Export
set s=ActiveDocument.Sheets("Intro")

set XLApp = CreateObject("Excel.Application")
set XLDOC = XLApp.Workbooks.Open ("C:\Work.xlsx")
XLApp.Visible = True

set s=ActiveDocument.Sheets("Exports")
ActiveDocument.Sheets("Exports").Activate
ActiveDocument.ClearCache
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.GetSheetObject("CH38").Restore
ActiveDocument.GetSheetObject("CH38").CopyTableToClipboard true
set XLSheet = XLDOC.Worksheets("sheet1")
XLSheet.Paste XLSheet.Range("A1")

Set XLActiveCell = XLSheet.Range("A1")
XLActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Insert
Set XLActiveCell = XLSheet.Range("A2")
XLActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Set XLActiveCell = XLSheet.Range("B2")
XLActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Set XLActiveCell = XLSheet.Range("C2")
XLActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Set XLActiveCell = XLSheet.Range("D2")
XLActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Set XLActiveCell = XLSheet.Range("E2")
XLActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"

Const XLPasteValues = -4163
Const xlShiftUp = -4162

Set XLActiveCell = XLSheet.Range("A1")
Set XLSelection = XLActiveCell.Offset(1, 0).Range("A1:E1")'.Copy
XLSelection.Copy
XLSelection.PasteSpecial(XLPasteValues)
XLApp.CutCopyMode = False
XLSelection.Copy
XLSheet.Paste XLSheet.Range("A1")
Set XLRowDelete = XLActiveCell.Offset(1, 0).Range("A1:E1")
XLRowDelete.Delete(xlShiftUp)

XLSheet.Cells.EntireColumn.AutoFit
ActiveDocument.GetSheetObject("CH38").Minimize
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.ClearCache

set s=ActiveDocument.Sheets("Exports")
ActiveDocument.Sheets("Exports").Activate
ActiveDocument.ClearCache
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.GetSheetObject("CH40").Restore
ActiveDocument.GetSheetObject("CH40").CopyTableToClipboard true
set XLSheet = XLDOC.Worksheets("sheet2")
XLSheet.Paste XLSheet.Range("A1")
XLSheet.Cells.EntireColumn.AutoFit

Set XLActiveCell = XLSheet.Range("A1")
XLActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Insert
Set XLActiveCell = XLSheet.Range("A2")
XLActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Set XLActiveCell = XLSheet.Range("B2")
XLActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Set XLActiveCell = XLSheet.Range("C2")
XLActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Set XLActiveCell = XLSheet.Range("D2")
XLActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Set XLActiveCell = XLSheet.Range("E2")
XLActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Set XLActiveCell = XLSheet.Range("F2")
XLActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Set XLActiveCell = XLSheet.Range("G2")
XLActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"

Set XLActiveCell = XLSheet.Range("A1")
Set XLSelection = XLActiveCell.Offset(1, 0).Range("A1:G1")'.Copy
XLSelection.Copy
XLSelection.PasteSpecial(XLPasteValues)
XLApp.CutCopyMode = False
XLSelection.Copy
XLSheet.Paste XLSheet.Range("A1")
Set XLRowDelete = XLActiveCell.Offset(1, 0).Range("A1:G1")
XLRowDelete.Delete(xlShiftUp)

ActiveDocument.GetSheetObject("CH40").Minimize
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.ClearCache

set s=ActiveDocument.Sheets("Exports")
ActiveDocument.Sheets("Exports").Activate
ActiveDocument.ClearCache
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.GetSheetObject("CH41").Restore
ActiveDocument.GetSheetObject("CH41").CopyTableToClipboard true
set XLSheet = XLDOC.Worksheets("Sheet3")
XLSheet.Paste XLSheet.Range("A1")

Set XLActiveCell = XLSheet.Range("A1")
XLActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Insert
Set XLActiveCell = XLSheet.Range("A2")
XLActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Set XLActiveCell = XLSheet.Range("B2")
XLActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Set XLActiveCell = XLSheet.Range("C2")
XLActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Set XLActiveCell = XLSheet.Range("D2")
XLActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Set XLActiveCell = XLSheet.Range("E2")
XLActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Set XLActiveCell = XLSheet.Range("F2")
XLActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"

Set XLActiveCell = XLSheet.Range("A1")
Set XLSelection = XLActiveCell.Offset(1, 0).Range("A1:F1")'.Copy
XLSelection.Copy
XLSelection.PasteSpecial(XLPasteValues)
XLApp.CutCopyMode = False
XLSelection.Copy
XLSheet.Paste XLSheet.Range("A1")
Set XLRowDelete = XLActiveCell.Offset(1, 0).Range("A1:F1")
XLRowDelete.Delete(xlShiftUp)

XLSheet.Cells.EntireColumn.AutoFit
ActiveDocument.GetSheetObject("CH41").Minimize
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.ClearCache

set s=ActiveDocument.Sheets("Exports")
ActiveDocument.Sheets("Exports").Activate
ActiveDocument.ClearCache
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.GetSheetObject("CH42").Restore
ActiveDocument.GetSheetObject("CH42").CopyTableToClipboard true
set XLSheet = XLDOC.Worksheets("Sheet4")
XLSheet.Paste XLSheet.Range("A1")

Set XLActiveCell = XLSheet.Range("A1")
XLActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Insert
Set XLActiveCell = XLSheet.Range("A2")
XLActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Set XLActiveCell = XLSheet.Range("B2")
XLActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Set XLActiveCell = XLSheet.Range("C2")
XLActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Set XLActiveCell = XLSheet.Range("D2")
XLActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Set XLActiveCell = XLSheet.Range("E2")
XLActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"
Set XLActiveCell = XLSheet.Range("F2")
XLActiveCell.FormulaR1C1 = "=TRIM(R[-1]C)"

Set XLActiveCell = XLSheet.Range("A1")
Set XLSelection = XLActiveCell.Offset(1, 0).Range("A1:F1")'.Copy
XLSelection.Copy
XLSelection.PasteSpecial(XLPasteValues)
XLApp.CutCopyMode = False
XLSelection.Copy
XLSheet.Paste XLSheet.Range("A1")
Set XLRowDelete = XLActiveCell.Offset(1, 0).Range("A1:F1")
XLRowDelete.Delete(xlShiftUp)

XLSheet.Cells.EntireColumn.AutoFit
ActiveDocument.GetSheetObject("CH42").Minimize
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.ClearCache
set XLSheet = XLDOC.Worksheets("Sheet4")
Set XLActiveCell = XLSheet.Range("G3")
XLActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-6],'Sheet8'!C[-6]:C[-2],5,0)),"""",VLOOKUP(RC[-6],'Sheet8'!C[-6]:C[-2],5,0))"
Set XLActiveCell = XLSheet.Range("H3")
XLActiveCell.FormulaR1C1 = "=IF(AND(RC[-5]<R3C[-5],RC[-4]="""",RC[-1]=""""),1,0)"
Set XLActiveCell = XLSheet.Range("I3")
xlActiveCell.FormulaR1C1 = "=IF(AND(RC[-6]<=R3C[-6],RC[-5]="""",RC[-2]=""""),RC[-6]*R3C[-4]/R3C[-6],0)"
Set XLActiveCell = XLSheet.Range("J3")
XLActiveCell.FormulaR1C1 = "=IF(RANK(RC[-1],C[-1])<=Check!R4C[-8],RC[-1],IF(AND(RC[-3]="""",RC[-5]>0),RC[-5],""""))"

XLSheet.SaveAs "C:\Work - " & [mynamevar] & " - " & [mydatevar] & ".xlsx"

set XLApp = Nothing
set XLDOC = Nothing
set XLSheet = Nothing
set XLRange = Nothing
set XLActiveCell = Nothing
set XLRowDelete = Nothing
set XLRowInsert = Nothing
set XLSelection = Nothing
set XLCol = Nothing
set XLCol1 = Nothing

End Sub

Qlikview with VBA

Thu, 04 Nov 2010 10:00:45 GMT

Re: Qlikview with VBA

chrisbrain — Thu, 04 Nov 2010 16:47:44 GMT

If you just need a solution to extract fields and other data into your Excel files and don't need the OCX you could also take a look at:

http://www.QVExcel.com

UPDATE: We also now have a much expanded VBA API in QVExcel meaning you can write powerful macros to extract QlikView data, make selections and distribute your reports directly from Excel macros.

http://www.qvexcel.com/vbaapi