2

If the user has copied a file to the clipboard in the file system, I would like the user to return to an Excel spreadsheet and click a button. The code behind this button should paste this file to a predefined folder.

I know it is possible through VBA to read/write a string to/from the clipboard. Is it possible in VBA to read a copied file from the clipboard and place it in a folder (file doesn't need to be opened during this operation)?

Community
  • 1
  • 1
John Steed
  • 599
  • 1
  • 12
  • 31
  • 1
    I think that getting the shell to properly *paste* would be difficult, here is how to fetch the names of the files so that you can `FileCopy()` them: http://stackoverflow.com/a/2913670/246342 – Alex K. Feb 11 '14 at 14:59
  • Don't tag VBA questions as VB6. – Bob77 Feb 11 '14 at 16:10
  • Bob, according to the accepted answer here http://stackoverflow.com/questions/993300/difference-between-visual-basic-and-vba , VBA and VB6 is basically the same language, hence my inclusion of the VB6 tag. I would assume that the solution to my question could be applied to both VBA and VB6. Note my question about the clipboard is not specific to the Excel object model. – John Steed Feb 11 '14 at 17:00
  • In VB6 IIRC you can do this directly via the ClipBoard object, which is not available in VBA. – Alex K. Feb 11 '14 at 18:02

1 Answers1

4

When you copy a file to the clipboard in Explorer, the file itself isn't placed on the clipboard (image what would happen if you copy a file of 200GB :) ). Instead Explorer puts e.g. the file's path on the clipboard in a special clipboard format (i.e. non-textual). You can read more on the formats at MSDN.

I haven't worked closely with these clipboard formats, but it seems that in most cases you would look for the CF_HDROP format. You'd need quite some VBA to massage the data into the file's path, but from there on it's a simple file copy to complete the operation.

Carl Colijn
  • 1,423
  • 9
  • 29