Back in 2007 I started work on an Excel Add-in which allowed QlikView objects to be placed into Excel worksheets and the data from those objects (specifically chart and table objects) to be channelled into Excel cells for further manipulation and analysis.
At the time there was no 'drag and drop' from QlikView to Office applications - I was working with the QlikOCX and QlikX controls and having to create a fair bit of UI and code to allow users to connect to local and server based QlikView documents, select objects to be added into the worksheets and further manipulate and manage those objects.
We actually released a limited beta which ran in both Excel and PowerPoint (the PowerPoint version just provided an interface for adding QlikView objects into presentations) - some readers might even have tried this out - but unfortunately the QlikOCX never seemed to play that well in the Office environment and in the end the product was just too unstable to continue development on.
Fortunately, since then things have moved on a great deal and drag and drop from QlikView to Office applications in now a built in feature of QlikView - so I thought it was time to dust off the Excel Connector project and build a new version, this time supporting both Excel 2003 and 2007.
The ability to drag and drop (or copy and paste) objects from QlikView into Excel has allowed this new iteration of the Add-in to be simplified considerably - almost all of the UI and code which was previously required to instantiate and manage the QlikView OLE objects has been removed - it always feels good to delete unnecessary code.
Another feature of the Add-in is that you get additional buttons in the Add-in's toolbar which allow standard navigation type actions in the QlikView document (e.g. back, forward, clear, lock cells). I think these functions would typically be required and having them in the toolbar saves creating redundant buttons in the QlikView application and dragging them into specific sheets within Excel.
This post then marks the official 'relaunch' of the QlikView Connector Add-In for Excel and if anybody would like to try it out I have put the latest beta download here (new version and post here), and here are a few instructions on getting started:
1) Open up a QlikView file in either the QlikView plugin or desktop client.
2) Drag any required QlikView objects from QlikView into your Excel worksheets (the Add-in currently supports transferring the data from table, chart and text type objects to Excel - although you may want to add additional listboxes and other controls to make selections).
3) Press the 'Refresh Links' button in the Add-in's toolbar.
4) A sheet should be automatically added named 'QlikView Connector Mgt' and the object IDs of any chart or table based objects you have dragged into you workbook should be listed (if not you can add them manually).
5) Against any object id's on 'QlikView Connector Mgt', enter the target sheet and cell where you would like the objects data transferred - this must be set as a formula and of the format SheetName!COLUMNROW (e.g. '=Sheet1!B2'). The easiest way to set this is to enter an '=' sign (as though you are starting to type a formula) and then navigating to the sheet then clicking on the cell where you would like the data to appear.
6) When you start making selections, the data for that object should now start appearing in the cell you set. You can also use the back/foward/clear/lock/unlock buttons in the Add-in's toolbar to help with selections and navigation.
7) If you drag new objects into the Excel file be sure to press the 'Refresh Links' button again.
NOTE:The Add-in is currently only tested with Excel 2003 and 2007 and QlikView 8.5. You should also ensure you have the QlikView plugin installed in order to be able to drag objects from either this plugin or the full QlikView desktop client into Excel (this plugin is typically installed to 'C:\Program Files\QlikView\Server\QvClients\QvPlugin').
(UPDATE (26/6/09): It appears drag and drop is broken in QlikView 9 and will be fixed in the first service release due some time in September 2009. I'll post a version of the Excel Connector for this version when it's released.)
(UPDATE 2 (5/10/09): Drag and drop has been fixed with QlikView 9 SR1! - You can grab the appropriate QlikView Excel Connector installer for your version of QlikView here.
I would be really interested in your comments on whether this is a useful tool or how it could be extended or changed to make it so. If the feedback is generally constructive and encouraging we will develop another iteration, otherwise it might be resigned to the code attic again.
Please feel free to leave any suggestions in the comments. Oh, and here's a screen shot...
(In this screen shot you can see the 'QlikView' ribbon area at the top containing a few buttons to navigate back and forward through the QlikView selections and clear/lock/unlock selections. In the sheet the columns of data in the cells are automatically extracted from the chart object allowing further manipulation and analysis in Excel.)
