我正在尝试向电子表格添加一个按钮,当点击该按钮时,可以将特定的URL复制到我的剪贴板。
我对Excel VBA有一些了解,但已经有一段时间了,我感到困难重重。
编辑 - MSForms已经过时,因此您不应再使用我的答案。请改为使用此答案:https://dev59.com/b2Yq5IYBdhLWcg3wwDRA#60896244
我仅在此处保留我的原始答案供参考:
Sub CopyText(Text As String)
'VBA Macro using late binding to copy text to clipboard.
'By Justin Kay, 8/15/2014
Dim MSForms_DataObject As Object
Set MSForms_DataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
MSForms_DataObject.SetText Text
MSForms_DataObject.PutInClipboard
Set MSForms_DataObject = Nothing
End Sub
使用方法:
Sub CopySelection()
CopyText Selection.Text
End Sub
??
或\xEF\xBF\xBF\xEF\xBF\xBF
的情况。 - ChrisB要将文本写入(或从 Windows 剪贴板读取文本),请使用此 VBA 函数:
Function Clipboard$(Optional s$)
Dim v: v = s 'Cast to variant for 64-bit VBA support
With CreateObject("htmlfile")
With .parentWindow.clipboardData
Select Case True
Case Len(s): .setData "text", v
Case Else: Clipboard = .getData("text")
End Select
End With
End With
End Function
'Three examples of copying text to the clipboard:
Clipboard "Excel Hero was here."
Clipboard var1 & vbLF & var2
Clipboard 123
'To read text from the clipboard:
MsgBox Clipboard
这是一种不使用MS Forms或Win32 API的解决方案。相反,它使用微软HTML对象库,该库快速且普及度高,并且不像MS Forms那样被微软弃用。此解决方案还尊重换行符。此解决方案也适用于64位Office。最后,此解决方案允许同时写入和读取Windows剪贴板。本页面上没有其他解决方案具有这些优点。
$
表示字符串。 - Excel HeroFunction Clipboard() As String
。 - Excel Hero最简单的(非Win32)方法是将一个用户窗体添加到您的VBA项目中(如果没有的话),或者另一种方法是添加对Microsoft Forms 2 Object Library的引用,然后从一个工作表/模块中可以简单地执行以下操作:
With New MSForms.DataObject
.SetText "http://zombo.com"
.PutInClipboard
End With
如果URL在工作簿中的单元格中,您可以直接复制该单元格中的值:
Private Sub CommandButton1_Click()
Sheets("Sheet1").Range("A1").Copy
End Sub
通过使用开发者选项卡添加按钮。如果未显示选项卡,则自定义选项卡。
如果URL不在工作簿中,可以使用Windows API。下面的代码可以在此处找到:http://support.microsoft.com/kb/210216
在添加了下面的API调用之后,更改按钮背后的代码以复制到剪贴板:
Private Sub CommandButton1_Click()
ClipBoard_SetData ("http:\\stackoverflow.com")
End Sub
将一个新模块添加到您的工作簿中,并粘贴下面的代码:
Option Explicit
Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) _
As Long
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) _
As Long
Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _
ByVal dwBytes As Long) As Long
Declare Function CloseClipboard Lib "User32" () As Long
Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) _
As Long
Declare Function EmptyClipboard Lib "User32" () As Long
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
ByVal lpString2 As Any) As Long
Declare Function SetClipboardData Lib "User32" (ByVal wFormat _
As Long, ByVal hMem As Long) As Long
Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096
Function ClipBoard_SetData(MyString As String)
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
Range.Copy
)不会复制文本,而是单元格:Excel将在文本的开头和结尾添加引号(“),并用双引号替换引号。 - Martin Rosenau添加对Microsoft Forms 2.0对象库的引用并尝试此代码。它仅适用于文本,而不适用于其他数据类型。
Dim DataObj As New MSForms.DataObject
'Put a string in the clipboard
DataObj.SetText "Hello!"
DataObj.PutInClipboard
'Get a string from the clipboard
DataObj.GetFromClipboard
Debug.Print DataObj.GetText
如果您想使用即时窗口将变量的值放入剪贴板,可以使用以下一行代码轻松地在代码中设置断点:
Set MSForms_DataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}"): MSForms_DataObject.SetText VARIABLENAME: MSForms_DataObject.PutInClipboard: Set MSForms_DataObject = Nothing
我在Excel 365中测试了这段代码,它可以正常工作。
Dim str as String
str = "Hello Copied"
Windows.Parent.Clipboard str
注意:我创建了这个变量,因为代码无法处理字符串连接
Sub copyClip()
Dim yourtext As String
yourtext = "abdc43"
CreateObject("htmlfile").parentWindow.clipboardData.setData "text", yourtext
End Sub
Sub PutToClip()
Dim txt As String: txt = "This works!"
Call Shell("cmd.exe /k" & "echo " & txt & " | clip", vbHide)
End Sub
Sheets(1).Range("A1000").Value = string
Sheets(1).Range("A1000").Copy
MsgBox "Paste before closing this dialog."
Sheets(1).Range("A1000").Value = ""