VBA: Read file from clipboard

Question

I'm trying to load a file in a VBA macro that has been copied from, say, an Explorer window.

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

If I can't get a file stream directly from the DataObject, the filename(s) would also do, so maybe GetText could be forced to return the name of a file placed on the clipboard?

There is very little documentation to be found for VBA anywhere. :(

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

Answer 1

This works for me (in a 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

Use:

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

Seems like a strange way to try to get at the textfile. The DataObject class is only for working with text strings to and from the clipboard.

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

If your wanting to get a file stream of a file you can look into the FileSystemObject and TextStream Classes.

Answer 3

Save the files if they are in 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