is there any way to "pull" data from qlikview objects into excel (without nprinting)?

[desertmatt]

Dear All

As discussed with Petter in another thread, I'm starting here a separate discussion to make this more visible for everybody.

I need to pull data from a Qlikview chart/straight table into excel or an Access table by VBA - maybe a button in Access which will run the code and updates the Access table with the data from Qlikview.

Petter was mentioning COM objects - but I don't know anything about this (but I do know some basics in VBA)

Any guidance would be appreciated. (I have one Qlikview desktop license - no server)

kind regards

Matt

[petter · Accepted solution]

Here is a step-by-step practical how-to without too much explanations. I have used QlikView 12.10 SR4 and Excel 2016. (It should work similar for most QlikView 11 and 12 versions and the last Excel versions for the past 10-15 years)

1) Start QlikView Desktop and bring up the example "Data Visualization" application that comes with QlikView Desktop.

2) Start Excel

3) Press ALT+F11 being in Excel with a spreadsheet open will bring you into the VBA Integrated Development Environment (IDE) so you can create VBA-code that will become a part of your Excel-file.

4) Press Tools and References:

5) Scroll down to "QlikView 12.0 Type Library" and check the checkbox to the left and press the OK button:

6) Bring up the "Object Browser" and tile it alongside your code window - it will be very helpful to get to know the entire QlikView object hierarchy and library:

7) Input the following code:

Sub PullQlikViewData()

    Dim qApp As New QlikView.Application    'this will automatically connect to the current running QlikView Desktop instance

    Dim qDoc As QlikView.ActiveDocument
    Dim qObj As QlikView.SheetObject
    Dim qSheet As QlikView.Sheet
    Dim qStraightTableBox As QlikView.StraightTableBox
    Dim qStraightTableBoxes() As QlikView.StraightTableBox

    Set qDoc = qApp.ActiveDocument

    ' Sheets(9) is the 10th sheet for the QlikView application called "Data Visualization" that is available via the QlikView startpage
    Set qStraightTableBox = qDoc.Sheets(9).GetStraightTableBoxes(0)
    's = qStraightTableBox.GetTableAsText(True)

    IsSuccess = qStraightTableBox.CopyValuesToClipboard

    Sheet1.Paste
End Sub

The run it from by pressing either F8 and single step through the code or just press PLAY (F5) and if everything works alright you should get a result like this:

Please feel free to ask questions on anything that is unclear...

[desertmatt]

Dear Petter,

this is excellent! Thank you so much!!!

Is there method how to pull the data based on the object ID - as I have several straight tables on one sheet?

Thank you very much again - need to experiment more with your code though...

Kind regards

Matt

[petter]

Your welcome - glad to be of help.

Yes you can get the object by Object ID like this:

Set qStraightTableBox = qDoc.GetSheetObject("CH26")

[petter]

There is also an API-guide for the COM object model of QlikView:

http://help.qlik.com/en-US/qlikview-developer/12.1/Subsystems/Automation/Content/automation-introduc...

A direct download link to the zip-file containing the QVW is here:

http://help.qlik.com/en-US/qlikview-developer/12.1/Subsystems/Automation/Content/APIGuide/QlikViewCo...

Unfortuanately Qlik hep-page maintainers have managed to put an old version (version 10) of the documentation there so I provide you also with the latest version I have available as an attachment (version 11).

There is very little if anything that has changed I believe with QlikView 12.

[desertmatt]

Wow Petter I got much more than I was hoping for!!!

Thank you so much for all your efforts and proper guidance - really really helpful!

But now I have a lot of homework and tinkering to do...;-)

Thank you very much again and kind regards

Matt

[Anil_Babu_Samineni]

petter-sI am following same, But i got error? Can we talk little more.

[petter]

the 'Doc' object is it really a QlikView document at all?

[Anil_Babu_Samineni]

Yes, It is. But Does really needed instance of Qlikview because currently i am using QV 12 only

[petter]

I am not sure that I understand... But this approach can be used with both QlikView 11 and with QlikView 12. You just have to select the right QlikView 11 Type Library or the QlikView 12 Type Library.