"Paste Link" to Excel spreadsheet

6.18K views
0

I’m exploring the ability to copy a Quantrix view and “paste link” into Excel. This will be very useful because it allows us to dump data into Excel from Quantrix without having to go through the export process. Plus, it updates just by opening the Excel file.

When I paste an entire view into Excel, it works fine. However, I can also copy a single cell and paste link into Excel. This gives me an #N/A error in Excel. Any ideas? The formula in Excel looks like it is referencing the correct part of the matrix.

0

A couple of things I’ve discovered in the last few days. All of these are using Quantrix with Windows XP and Microsoft Office 2003.

– The most reliable way to link Quantrix data into Excel is to paste link as text. However, item group names don’t export, just item names, so any items with the same names but different groups will look identical. Also, if the structure of your view changes after the link is made, the link can break.

– There appears to be no way to paste link to Excel and preserve Quantrix formatting, other than as an image. In Word, you can paste link as HTML, which creates a formatted table.

– Paste link to an image in Excel and Word is only allowed if the image is small enough to fit on the printable sheet. The option to paste link as image will not appear if the view has too many rows.

– Pasting a single cell from Quantrix to Excel returns #N/A at first. I found that if I go to the Edit > Links menu in Excel and update the link, the number appears.

I’ve spent some time investigating the underlying technology that allows Quantrix to link to other applications. It appears that Quantrix is using DDE or OLE to set up a server / client link. For those that work in Excel VBA there are commands for creating and manipulating DDE relationships, but I haven’t yet found a method for making this practical within an Excel macro. This would allow some limited “scripting” of Quantrix assuming it could be done.

You are viewing 1 out of 10 answers, click here to view all answers.

Latest Questions