I am trying to get information from another workbook without using Indirect and with using a cell as a variable in an address, so I can do it without having to cycle open and closed all the workbooks. This is what I have done so far.
The first step is using values in cells concatenated to create a link to the other file. I then take this concatenated text and copy/paste special as values. Then I copy it again and paste it somewhere else with the hope that the target will show up. What I get instead is the exact text.
Now, if I copy the text to Notepad, copy it again and paste it into a cell, I get the value from the target cell, so I think it has something to do with how it's pasted? But I don't know enough about it to see what it is.
The first line there is all the stuff which gets concatenated from A6 to A9. I then copy A6:A9 and paste it special A16:A19. When I copy any of them from A16 to A19 to somewhere else, I get that exact text instead of the value. But, like I said, when I copy it to Notepad then back to another cell, I get the data from the target cell I'm looking for.
This is the really simple code on a different sheet, the final paste destination:
Private Sub CommandButton2_Click()
Worksheets("Data").Range("A6:A9").Copy
Worksheets("Data").Range("A16:A19").PasteSpecial Paste:=xlPasteValues
End Sub
Private Sub CommandButton3_Click()
Worksheets("Data").Range("A16").Copy Range("C2")
Worksheets("Data").Range("A17").Copy Range("D2")
Worksheets("Data").Range("A18").Copy Range("E2")
Worksheets("Data").Range("A19").Copy Range("F2")
End Sub
Any help would be greatly appreciated.
CodePudding user response：
On copy operation, source content can be inserted into the clipboard in more than one format, if author(s) of source application choose to support them. This is also case of Excel. Using tools like Clipboard Viewer 3.1 (or many others) you can see all the data formats currently present on clipboard.
When pasting into the Notepad, it recognizes and pastes its only supported format - plain text. This is how pasting there transforms rich Excel information into text information, not transforming formatting or other Excel features. Then you create a new clipboard content by the second copy operation - now in the Notepad - which populates the clipboard only by copied text, nothing else. When this text is pasted into Excel, it uses certain built-in rules to split text between cells, try to interpret it as formulas first (not as values) and maybe other operations.
You can see that copy and paste inside Excel reads and writes the complete excel format and preserves Excel information. But by giving the content a round-trip using plain text format, completely different way of processing is used and that is exactly why you see different results when you do this.
This is true for most programs, not only Excel. Especially for Office apps. Other programs (e. g. audio editors) do not write a text format into the clipboard, they write for example only audio. This is why Notepad won't paste any sound into its editor. And vice versa, copying a text in the Notepad won't paste anything in sound recording editor.
CodePudding user response：
You guys, now I feel stupid. I was looking for a PasteSpecial option which would be the equivalent of the right-click and Keep Text Only function, as that's how the successful copy and paste from Notepad was working, and I had zero luck. Instead, I found tucked away on another site the Value2 option.
After I had the filepath concatenated and pasted as a value only in A16, in the same sub I put:
Range("C2").Value = Worksheets("Data").Range("A16").Value2
And THAT gave me the value from the target cell and put it where I wanted it. I don't know why this works, but as long as it keeps working, I'm a happy camper.
Now it looks like it would have worked with just Value, and I didn't have to bother with Value2. I am thoroughly confused, still. What a day.