我正在尝试编写一个宏,将单元格的内容剪切到剪贴板,以便我可以将其粘贴到另一个应用程序。这不起作用(它不会粘贴任何内容)...
Sub Macro5()
'
' Macro5 Macro
'
' Keyboard Shortcut: Ctrl+m
'
Selection.Cut
ActiveCell.FormulaR1C1 = ""
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub
我应该解释一下,普通的剪切粘贴是行不通的,因为它不消除Excel 中的文本仅将其放入您粘贴的位置。
答案1
使用以下内容作为宏。从引用的网页复制定义ClipBoard_SetData
函数的代码
Sub Cut_Text()
Dim txt As String
txt = Selection.Value ' Get value of cell (Note: only single-cell selections supported)
ClipBoard_SetData txt ' Copy contents of cell to clipboard
Selection.Clear ' Clear the contents of the cell
End Sub
此代码仅适用于单个单元格选择。
使用 Windows API 复制到剪贴板
以下是 Microsoft 建议的解决“SetText”错误的 API 解决方法。它有三个部分:API 声明部分、函数例程,然后我使用类似的子例程宏将所需文本放入剪贴板。
更新:我已修改 API 声明,以适用于 64 位和 32 位版本的 Microsoft Office
'Handle 64-bit and 32-bit Office #If VBA7 Then Private Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr Private Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr Private Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As LongPtr, _ ByVal dwBytes As LongPtr) As LongPtr Private Declare PtrSafe Function CloseClipboard Lib "user32" () As LongPtr Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As LongPtr Private Declare PtrSafe Function EmptyClipboard Lib "user32" () As LongPtr Private Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _ ByVal lpString2 As Any) As LongPtr Private Declare PtrSafe Function SetClipboardData Lib "user32" (ByVal wFormat As LongPtr, _ ByVal hMem As LongPtr) As LongPtr #Else Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _ ByVal dwBytes As Long) As Long Private Declare Function CloseClipboard Lib "user32" () As Long Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long Private Declare Function EmptyClipboard Lib "user32" () As Long Private Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _ ByVal lpString2 As Any) As Long Private Declare Function SetClipboardData Lib "user32" (ByVal wFormat _ As Long, ByVal hMem As Long) As Long #End If Const GHND = &H42 Const CF_TEXT = 1 Const MAXSIZE = 4096 Function ClipBoard_SetData(MyString As String) 'PURPOSE: API function to copy text to clipboard 'SOURCE: www.msdn.microsoft.com/en-us/library/office/ff192913.aspx Dim hGlobalMemory As Long, lpGlobalMemory As Long Dim hClipMemory As Long, X As Long 'Allocate moveable global memory hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1) 'Lock the block to get a far pointer to this memory. lpGlobalMemory = GlobalLock(hGlobalMemory) 'Copy the string to this global memory. lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString) 'Unlock the memory. If GlobalUnlock(hGlobalMemory) <> 0 Then MsgBox "Could not unlock memory location. Copy aborted." GoTo OutOfHere2 End If 'Open the Clipboard to copy data to. If OpenClipboard(0&) = 0 Then MsgBox "Could not open the Clipboard. Copy aborted." Exit Function End If 'Clear the Clipboard. X = EmptyClipboard() 'Copy the data to the Clipboard. hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory) OutOfHere2: If CloseClipboard() = 0 Then MsgBox "Could not close Clipboard." End If End Function Sub CopyTextToClipboard() 'PURPOSE: Copy a given text to the clipboard (using Windows API) 'SOURCE: www.TheSpreadsheetGuru.com 'NOTES: Must have above API declaration and ClipBoard_SetData function in your code Dim txt As String 'Put some text inside a string variable txt = "This was copied to the clipboard using VBA!" 'Place text into the Clipboard ClipBoard_SetData txt 'Notify User MsgBox "There is now text copied to your clipboard!", vbInformation End Sub