使用WPS VBA、Excel VBA 将多个文件复制到剪切板(ClipBoard)中
最近有个客户想制作一个小功能，希望使用VBA将多个文件（指定路径及文件名），复制到剪切板中，他可能需要将剪切板中的内容复制到其它软件中。
因为之前研究过剪切板，做过类似的，但一时找不到代码了。又不想重做了，只好重新搜索网上相关资源。先记录一下，以免到时又找不到。
出处：Excelhome
代码如下：
Option Explicit
'剪贴版处理函数
Private Declare Function EmptyClipboard Lib "user32" () As Long
Private Declare Function OpenClipboard Lib "user32" (ByVal hWnd _
As Long) As Long
Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function SetClipboardData Lib "user32" (ByVal wFormat _
As Long, ByVal hMem As Long) As Long
Private Declare Function GetClipboardData Lib "user32" (ByVal wFormat _
As Long) As Long
Private Declare Function IsClipboardFormatAvailable Lib "user32" _
(ByVal wFormat As Long) As Long
Private Declare Function DragQueryFile Lib "shell32.dll" Alias _
"DragQueryFileA" (ByVal hDrop As Long, ByVal UINT As Long, _
ByVal lpStr As String, ByVal ch As Long) As Long
Private Declare Function DragQueryPoint Lib "shell32.dll" (ByVal _
hDrop As Long, lpPoint As POINTAPI) As Long
Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags _
As Long, ByVal dwBytes As Long) As Long
Private Declare Function GlobalFree Lib "kernel32" (ByVal hMem As _
Long) As Long
Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As _
Long) As Long
Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As _
Long) As Long
Private Declare Sub CopyMem Lib "kernel32" Alias "RtlMoveMemory" _
(Destination As Any, Source As Any, ByVal Length As Long)
Private Declare Function SHFileOperation Lib "shell32.dll" Alias _
"SHFileOperationA" (lpFileOp As SHFILEOPSTRUCT) As Long
'剪贴版数据格式定义
Private Const CF_TEXT = 1
Private Const CF_BITMAP = 2
Private Const CF_METAFILEPICT = 3
Private Const CF_SYLK = 4
Private Const CF_DIF = 5
Private Const CF_TIFF = 6
Private Const CF_OEMTEXT = 7
Private Const CF_DIB = 8
Private Const CF_PALETTE = 9
Private Const CF_PENDATA = 10
Private Const CF_RIFF = 11
Private Const CF_WAVE = 12
Private Const CF_UNICODETEXT = 13
Private Const CF_ENHMETAFILE = 14
Private Const CF_HDROP = 15
Private Const CF_LOCALE = 16
Private Const CF_MAX = 17
' 内存操作定义
Private Const GMEM_FIXED = &H0
Private Const GMEM_MOVEABLE = &H2
Private Const GMEM_NOCOMPACT = &H10
Private Const GMEM_NODISCARD = &H20
Private Const GMEM_ZEROINIT = &H40
Private Const GMEM_MODIFY = &H80
Private Const GMEM_DISCARDABLE = &H100
Private Const GMEM_NOT_BANKED = &H1000
Private Const GMEM_SHARE = &H2000
Private Const GMEM_DDESHARE = &H2000
Private Const GMEM_NOTIFY = &H4000
Private Const GMEM_LOWER = GMEM_NOT_BANKED
Private Const GMEM_VALID_FLAGS = &H7F72
Private Const GMEM_INVALID_HANDLE = &H8000
Private Const GHND = (GMEM_MOVEABLE Or GMEM_ZEROINIT)
Private Const GPTR = (GMEM_FIXED Or GMEM_ZEROINIT)
Private Const FO_COPY = &H2
Private Type POINTAPI
X As Long
y As Long
End Type
Private Type DROPFILES
pFiles As Long
pt As POINTAPI
fNC As Long
fWide As Long
End Type
Private Type SHFILEOPSTRUCT
hWnd As Long
wFunc As Long
pFrom As String
pTo As String
fFlags As Integer
fAnyOperationsAborted As Long
hNameMappings As Long
lpszProgressTitle As String
End Type
Public Function clipCopyFiles(Files() As String) As Boolean '此功能不稳定, 有些项目无法进行复制到剪切板
Dim Data As String
Dim df As DROPFILES
Dim hGlobal As Long
Dim lpGlobal As Long
Dim i As Long
'清除剪贴版中现存的数据
If OpenClipboard(0&) Then
Call EmptyClipboard
For i = LBound(Files) To UBound(Files)
Data = Data & Files(i) & vbNullChar
Next i
Data = Data & vbNullChar
'为剪贴版拷贝操作分配相应大小的内存
hGlobal = GlobalAlloc(GHND, Len(df) + Len(Data) + 15)
If hGlobal Then
lpGlobal = GlobalLock(hGlobal)
df.pFiles = Len(df)
'将DropFiles结构拷贝到内存中
Call CopyMem(ByVal lpGlobal, df, Len(df))
'将文件全路径名拷贝到分配的内存中。
Call CopyMem(ByVal (lpGlobal + Len(df)), ByVal Data, _
Len(Data) + 15)
Call GlobalUnlock(hGlobal)
'将数据拷贝到剪贴版上
If SetClipboardData(CF_HDROP, hGlobal) Then
clipCopyFiles = True
End If
End If
Call CloseClipboard
End If
End Function
Sub TEST()
Dim aF(0 To 3) As String
Dim af2() As String
'在win7下, 除了少数如*.wri文件与偶尔有个xlsm(怀疑是5M太大了)不能复制
'其它都可以顺利进入剪贴板而后被win系统右键粘贴
'在win10下, 事情变得离奇, EXE与文件夹成功复制粘贴的机率较高, 但也有不成功
'同一个文件夹在不同的位置时也影响成功与否.
aF(0) = ThisWorkbook.Path & "\Test\EXEFILE.EXE"
aF(1) = ThisWorkbook.Path & "\Test\zipFILE.zip"
aF(2) = ThisWorkbook.Path & "\Test\xlsxfile.xlsx"
aF(3) = ThisWorkbook.Path & "\Test\xlsmfile.xlsm"
'aF(0) = ThisWorkbook.Path & "\Test\xlsfile.xls"
'aF(0) = ThisWorkbook.Path & "\Test\Folder"
'aF(0) = ThisWorkbook.Path & "\Test\txtfile.txt"
'aF(0) = ThisWorkbook.Path & "\Test\rarFILE.rar"
Debug.Print (clipCopyFiles(aF))
'Debug.Print (clipPasteFiles(af2))
End Sub
'*****以下为取出操作*****
Public Function clipPasteFiles(Files() As String) As Long
Dim hDrop As Long
Dim nFiles As Long
Dim i As Long
Dim desc As String
Dim filename As String
Dim pt As POINTAPI
Dim tfStr As SHFILEOPSTRUCT
Const MAX_PATH As Long = 260
'确定剪贴版的数据格式是文件，并打开剪贴版
If IsClipboardFormatAvailable(CF_HDROP) Then
If OpenClipboard(0&) Then
hDrop = GetClipboardData(CF_HDROP)
'获得文件数
nFiles = DragQueryFile(hDrop, -1&, "", 0)
ReDim Files(0 To nFiles - 1) As String
filename = Space(MAX_PATH)
'确定执行的操作类型为拷贝操作
tfStr.wFunc = FO_COPY
'目的路径设置为File1指定的路径
tfStr.pTo = "d:\test\234\" 'Form1.File1.Path
For i = 0 To nFiles - 1
'根据获取的每一个文件执行文件拷贝操作
Call DragQueryFile(hDrop, i, filename, Len(filename))
Files(i) = TrimNull(filename)
tfStr.pFrom = Files(i)
SHFileOperation tfStr
Next i
'Form1.File1.Refresh
'Form1.Dir1.Refresh
Call CloseClipboard
End If
clipPasteFiles = nFiles
End If
End Function
Private Function TrimNull(ByVal StrIn As String) As String
Dim nul As Long
nul = InStr(StrIn, vbNullChar)
Select Case nul
Case Is > 1
TrimNull = Left(StrIn, nul - 1)
Case 1
TrimNull = ""
Case 0
TrimNull = Trim(StrIn)
End Select
End Function
Sub TEST1()
Dim X() As String
TESTAA X