Paste Special Link
Excel and Windows allow dynamic connections to be made between one Excel worksheet and another, or between Excel worksheets and other applications such as Word, with Edit Copy and Edit Paste Special Links.
I can’t find any equivalent functionality in Quantrix v3, beyond (so far as I can see in forum messages) loading a JDBC driver, which isn’t installed by default, and naming bits of the spreadsheet, which means altering Excel files that don’t belong to me.
Is there any cleaner way of achieving this kind of linkage? It’s hard to do without when the world keeps its numbers in Excel files.
I’ve also confronted this issue extensively at our company. We have a limited number of Quantrix licenses and a lot of highly adept Excel users. There are really two issues: how to link Excel data into Quantrix, and how to get Quantrix data linked back out to Excel.
As you mentioned, linking Excel data into Quantrix requires that the Excel ranges be named, and this is a problem with spreadsheets you don’t own. The best way to overcome this is to first create a central Excel workbook that links to all the other Excel data sources you have. For instance, I have a spreadsheet with four tabs that link to four different Excel documents created by other people. I create a named range around the linked data. When I want to update Quantrix, I open my central Excel workbook, update links, then go to Quantrix and update DataLinks. This method allows you to do some fancier work on the Excel side (such as using queries to gather the data instead of cell links).
When you want to get the data back into Excel, you can obviously export each tab as text or Excel files, but this is tedious (one at a time). I have built a simple Quantrix plugin that exports all the files I want at one time to a specified folder, and then I can link to or query these files in Excel. I find text files are the best, because there is no problem with formatting as with the Excel export.
I’ve also experimented with an XML extraction tool in Excel that would extract XML data from a Quantrix .xmodel file and map it directly to Excel. This is a complicated procedure because of the way Quantrix stores XML data, and it only saves you one step (exporting the Quantrix data to text).
Some day it would be nice to see an Excel add-in for Quantrix that makes these operations easier (like many of the BI packages have). Until then we’ve made use of the solutions I mention above with good success.