VBA: reading file from clipboard

Question

I am trying to load a file into a VBA macro that has been copied, say, from an explorer window.

I can easily get data from the clipboard using DataObject :: GetFromClipboard, but the VBA interface to DataObject does not seem to have methods for working with any other formats than plain text. There are only GetText and SetText methods.

If I cannot get the file stream directly from the DataObject, there will also be file names, so maybe GetText can be forced to return the name of the file placed on the clipboard?

There is very little documentation for VBA anywhere. :(

Maybe someone can point me to an API wrapper class for VBA that has this functionality?

Answer 1

This works for me (in the module);

Private Declare Function IsClipboardFormatAvailable Lib "user32" (ByVal uFormat As Long) As Long Private Declare Function OpenClipboard Lib "user32" (ByVal Hwnd As Long) As Long Private Declare Function GetClipboardData Lib "user32" (ByVal uFormat As Long) As Long Private Declare Function CloseClipboard Lib "user32" () As Long Private Declare Function DragQueryFile Lib "shell32.dll" Alias "DragQueryFileA" (ByVal drop_handle As Long, ByVal UINT As Long, ByVal lpStr As String, ByVal ch As Long) As Long Private Const CF_HDROP As Long = 15 Public Function GetFiles(ByRef fileCount As Long) As String() Dim hDrop As Long, i As Long Dim aFiles() As String, sFileName As String * 1024 fileCount = 0 If Not CBool(IsClipboardFormatAvailable(CF_HDROP)) Then Exit Function If Not CBool(OpenClipboard(0&)) Then Exit Function hDrop = GetClipboardData(CF_HDROP) If Not CBool(hDrop) Then GoTo done fileCount = DragQueryFile(hDrop, -1, vbNullString, 0) ReDim aFiles(fileCount - 1) For i = 0 To fileCount - 1 DragQueryFile hDrop, i, sFileName, Len(sFileName) aFiles(i) = Left$(sFileName, InStr(sFileName, vbNullChar) - 1) Next GetFiles = aFiles done: CloseClipboard End Function 

Using:

 Sub wibble() Dim a() As String, fileCount As Long, i As Long a = GetFiles(fileCount) If (fileCount = 0) Then MsgBox "no files" Else For i = 0 To fileCount - 1 MsgBox "found " & a(i) Next End If End Sub 

Answer 2

It seems like a strange way to try to get a text file. The DataObject class is intended only for working with text strings to and from the clipboard.

Here is a very good resource: http://www.cpearson.com/excel/Clipboard.aspx

If you want to get the file stream of a file, you can look at the FileSystemObject and TextStream classes.

Answer 3

Save the files, if they are on the clipboard, to the destination folder.

 Public Declare PtrSafe Function IsClipboardFormatAvailable Lib "user32" (ByVal wFormat As Long) As Long Public Const CF_HDROP As Long = 15 Public Function SaveFilesFromClipboard(DestinationFolder As String) As Boolean SaveFilesFromClipboard = False If Not CBool(IsClipboardFormatAvailable(CF_HDROP)) Then Exit Function CreateObject("Shell.Application").Namespace(CVar(DestinationFolder)).self.InvokeVerb "Paste" SaveFilesFromClipboard = True End Function