3 Replies Latest reply: May 19, 2011 3:55 AM by Chris Brain RSS

    Qlikview with VBA

    Alan Green

      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
          jonathan.bowen

          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
            jonathan.bowen

            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

            • Re: Qlikview with VBA
              Chris Brain

              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