"Paste Link" to Excel spreadsheet

6.25K 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

Thanks very much for your effort. Is there some other function that gives similiar result as Paste link?

0

I have been working with Martina off-forum a bit on this – looks like the linking does not work very well with Excel 2007. I will update this thread if anything new develops… as there could be something deep in Windows or Office 2007 interfering with things.

It is very likely this OLE Linking functionality will not be supported in Quantrix 3.0 – there are just too many outside variables to maintain. We are hopeful that the expanded feature set of Quantrix 3.0 will make linking out of data a less frequent event for Quantrix users.

Thank you for your input everyone.

-Mike

0

Martina, are you pasting the entire matrix or just a selection? I know the latter has never worked correctly in my Excel setup, but I’m still in Office 2003.

One thing to try is to enter the formula editor again (just click in the formula) and hit enter… sometimes this triggers Excel to fix the link.

0

Hi,
I have linked my Quantrix data in Excel with Paste link as text. When I try to refresh my data I get an #REF error in Excel. I’m using Excel 2007. Any ideas?

0

J Scott

I fully agree!

[quote="J Scott":2lfyxa5o]
I have a feeling that the peculiarities around this paste link feature are mostly related to the Windows / Office setup, rather than Quantrix.[/quote:2lfyxa5o]

It is known that the OLE / DDE / COM or whatever connections to Excel are difficult to handle.

Dominik

0

Dominik,

I’ve also experienced the issue you mention:

[quote:10k75vzq]Copy View in Quantrix and Edit -> Paste Special… -> Paste Link -> HTML or Text or Csv in Excel: I get the matrix filled with #VALUE! items.[/quote:10k75vzq]

However if I delete the #VALUE! errors and do it again, strangely it works. Sometimes I just have to try twice before the formulas calculate.

The status of update in Excel is always “Not applicable” for Quantrix links in my experience too.

I have a feeling that the peculiarities around this paste link feature are mostly related to the Windows / Office setup, rather than Quantrix.

0

J Scott

Here are my other two cents to this topic:

Copy View in Quantrix and Edit -> Paste Special… -> Paste Link -> HTML or Text or Csv in Excel:
I get the matrix filled with #VALUE! items. Further, I can’t update the values by choosing Edit -> Links… . The Status is ‘Not applicable’.

The best way to add views to Excel is to Edit -> Paste Special… -> Paste Link -> Picture (…) as Mike has suggested already.

Dominik

[quote="J Scott":xu136pyk]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.[/quote:xu136pyk]

0

J Scott

I am working with Windows XP and Excel 2003 as well.
When I just copy/paste a cell or a selection of cells into Excel I don’t get the #N/A error. But I can’t paste link a single cell or a selection of cells (this option is then not active).

Dominik

[quote="J Scott":e1jssqmw]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.[/quote:e1jssqmw]

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.

0

Hi – Thanks for the post.

In my experience in working with paste link, it works best with image files of entire matrices. Quantrix is not entirely in control of the process, as the paste link relies on Windows operating system technologies, but paste link of images seems to have the most consistent experience.

If anyone else has other experiences with paste link – please feel free to post.

-Mike

Latest Questions