Depending on the volume of the data you are copying, this may not be a viable option but...
You could copy the formula text for all of the linked cell without the equals sign with minimal slowdown and, once everything is in place, use CONCATENATE or search and replace to add the equals sign to the beginning of all of those text strings then let it crank out all of those links at once.
Let's say all of the data is in a range adjacent to "NamedCellNumberOne" You could paste "C:\Some folder\someworkbook.xlsm'NamedCellNumberOne" in the first cell then drag the corner of the cursor to "fill series" as needed. Once that is done, search all instances of "C:\Some folder\" and replace them with "=C:\Some folder\" to create all the formulas at once.
For that to work, you have to install the Guest Additions. That will give you a shared clipboard.
After starting your VM, you can install the Guest Additions from the menu. Click on Devices
and then select Insert Guest additions CD image
.
Another way is, when your VM is off, you can attach an ISO image to it from the VirtualBox GUI. It is under the Storage tab.
When you install VirtualBox, it should contain the Guest Additions ISO image as well. If you can't find it, you can download it from here. Make sure it matches the exact version of Virtualbox.
The reason you need to install the Guest Additions to get copy and paste working is that your VM is a separate machine running in its own environment that is unaware of the host machine. You have to install drivers on the guest machine so it can communicate with the Virtualbox instance running on the host.
You can use your mouse without Guest Additions, but then it is either captured by the guest or owned by the host. You transfer control to the Guest by clicking in the Guest window. You have to "release" the mouse from the guest using a key combination (Right-Alt by default) to use it on the host again. See imgur.com/a/Ci5HM
If you have the Guest Additions installed, you mouse moves seamlessly between guest and host.
Note that if you don't have a GUI on your VM, but only a console, then you won't get a mouse pointer. A console system has no mouse. The Bash shell has no clipboard i.e. you can't copy/paste from/to a console-only system.
The easiest way to get around that is to use ssh
to your VM from your host. You can then use copy and paste in the ssh
terminal.
Best Answer
If you want to copy the formulas, just copy them "normally", i.e. with creating the links in the first step. Then try either of the following:
Edit Links
dialog, clickChange Source
and select the new file in which you pasted the formulasCtrl-A
) and then use the Search and Replace (Ctrl-H
) dialog and replace the[old file.xlsx]
with a blank string (make sure to clickReplace All
). Note: do this while the old file is still open. If the file is closed, you need to either open it or replace the full file name including the path (as you see it in the formula).