Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
alan_grn
Creator II
Creator II
‎2010-11-04 01:40 AM

Qlikview with VBA

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.

9,987 Views
0 Likes
3 Replies
Not applicable
‎2010-11-04 06:00 AM

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

1,286 Views
0 Likes
Not applicable
‎2010-11-04 06:00 AM

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

1,286 Views
0 Likes
chrisbrain
Partner - Specialist II
Partner - Specialist II
‎2010-11-04 12:47 PM

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

beeido.com - BI | Software | Qlik Integration Services
GitFirst - A CI/CD solution for Qlik Sense
1,286 Views
0 Likes