0

I'm trying to paste a user-copied selection (marching ants, not black-bordered) as links, but it won't give me anything but values. I've tried this a number of ways, for example:

     this_ws.Activate
     this_ws.Range("A1").Select
     ActiveSheet.PasteSpecial Link:=True

It does paste, and without error, but it invariably gives me values instead of links.

My ultimate goal is to convert a user-provided selection into a regular range. My research and experiments so far imply the only way to sanely do this (for certain values of "sane") is to paste as links in a temporary tab and then harvest the information necessary to create an equivalent range from those links. (Other, more headache-inducing ways involve DataObject or monitoring a sheet for selection changes.)

Update: According to Microsoft, "If the source data isn't suitable for linking or the source application doesn't support linking, this parameter is ignored." So why would the source data not be "suitable"? It's just another macro-enabled workbook, left open and with strings and ints in the copied selection.

Update2: To clarify, I am looking to duplicate (in VBA) the functionality you get from the GUI when you do a manual copy and then click the "Paste Link(s)" button in the lower left corner of the Paste Special dialog box. If I can Paste Links on a user-provided copy from VBA, I know I can do the rest. But I don't especially care if the clipboard is involved or not in this process; I just want the user to be able to select, do a ctrl-c, alt-tab over to my tool workbook, then hit a button that runs a macro on the range he/she just specified.

Community
  • 1
  • 1
user2744165
  • 11
  • 1
  • 5
  • When you access the clipboard, you are just accessing text data. All the additional information (e.g., knowing that you are copying from a cell in another workbook) is stored by the program where the copying is performed (Excel in this case). The only way you have to communicate with Excel (to know this additional information) is via the object of the corresponding workbook (or equivalent). If you want the user to perform the copying (and thus not having any reference to the original workbook), all what you would get by analysing the pasted data is pure text. – varocarbas Sep 03 '13 at 19:29
  • The only solution I see to your problem is tracking the cells when being copied (by relying on the selection change event; it is pretty easy to use and quite accurate), stored it in the first workbook and, somehow, communicate it to the destination workbook. Other than that you can just analyse text and determine what to do on account of what you find there (but I guess that the user will not be writing the ranges :)). – varocarbas Sep 03 '13 at 19:32

1 Answers1

4

Its the line 

ActiveSheet.PasteSpecial Link:=True

that causes your problem. It should be

ActiveSheet.Paste Link:=True

And yes, it is confusing, given that the Paste Link option is accessed from the Paste Special dialog

chris neilsen
  • 52,446
  • 10
  • 84
  • 123