Ms Access Vba Copy To Clipboard
- Initial Setup - Microsoft Forms ii.0 Object Library
- VBA Copy to Clipboard
- Windows vii and earlier
- Windows 8 and afterwards
- Paste from Clipboard
- Clear y'all Clipboard
- Final Thoughts
This VBA tutorial will testify you how to re-create text to your clipboard, paste from your clipboard and articulate the contents of your clipboard. These macros piece of work with 32-bit versions Excel, Outlook, Give-and-take and many more Microsoft Role products.
2018 Update: This tutorial has been updated to teach you how to use VBA to copy text to your clipboard in Windows 8 and Windows ten. In this tutorial, I'll present two solutions. The get-go solution uses the Microsoft Forms 2.0 Object Library to copy text to your clipboard in Windows seven and earlier. With the rollout of Windows 8 and Windows 10, this solution no longer works reliably, so I've presented a second solution that uses Windows API calls to copy and paste using VBA.
Let's take a look at the commencement, and oldest, solution:
Initial Setup
Microsoft Forms 2.0 Object Library
To gain access to the clipboard methods in the clipboard class in older versions of Windows, you could declare a reference to the Microsoft Forms 2.0 Object Library by post-obit these steps:
- Open your VBA editor.
- Click Tools > References.
- Check the box next to "Microsoft Forms 2.0 Object Library."
- Printing "OK."
One time your initial setup is consummate, insert a module and keep reading to discover how to control your clipboard.
VBA Copy To Clipboard
Solution 1: Windows 7 and earlier
To copy to your clipboard in Windows 7 and earlier, y'all'll demand to first declare a variable every bit type MSForms.DataObject. The DataObject will be the bridge betwixt your data and your clipboard.
Copy Cord to Clipboard
Sub CopyToClipboard () Dim clipboard As MSForms . DataObject Dim strSample Every bit String Prepare clipboard = New MSForms . DataObject strSample = "This is a sample string" clipboard . SetText strSample clipboard . PutInClipboard End Sub The SetText method is used to shop a string in a DataObject. The string can exist a VBA variable, every bit demonstrated to a higher place, or a prison cell in your worksheet, as demonstrated below. The PutInClipboard method is the magical piece that places the text in your clipboard.
Re-create Cell to Clipboard
Sub CopyToClipboard2 () Dim clipboard As MSForms . DataObject Set clipboard = New MSForms . DataObject clipboard . SetText ActiveSheet . Range ( "B2" ) clipboard . PutInClipboard End Sub VBA is pretty skillful at converting your data types to strings before storing them in your clipboard, and then yous don't have to worry about using the CStr role to manually convert.
Although I said this is the solution for Windows 7 and before, the following solution as well works for Windows 7 and earlier. Information technology has the added do good of working for newer versions of Windows, as well.
Solution 2: Windows 8 and Windows 10
In that location's a bit of a bug, if you want to telephone call it that, in Windows 8 and Windows ten that prevents the Microsoft Forms 2.0 Object Library solution from copying text to your clipboard. The workaround for this is to use Windows API calls, instead. Although intended to be used in newer versions of Windows, this VBA macro volition successfully copy to your clipboard on all versions of Windows.
These macros are much longer than the previous solution, simply they're however easy to use since all yous have to practise is copy and paste. In many ways, this solution is actually easier to utilise considering you don't need to add a reference to the Microsoft Forms two.0 Object Library to add together text to your clipboard! Yous will still need to add the reference to the Microsoft Forms 2.0 Object Library to paste from your clipboard, so you might also go ahead and add it.
#If Mac Then ' practice nothing # Else #If VBA7 Then Declare PtrSafe Function GlobalUnlock Lib "kernel32" ( ByVal hMem Equally LongPtr ) As LongPtr Declare PtrSafe Function GlobalLock Lib "kernel32" ( ByVal hMem Every bit LongPtr ) As LongPtr Declare PtrSafe Function GlobalAlloc Lib "kernel32" ( ByVal wFlags As Long , _ ByVal dwBytes As LongPtr ) As LongPtr Declare PtrSafe Function CloseClipboard Lib "User32" () As Long Declare PtrSafe Function OpenClipboard Lib "User32" ( ByVal hwnd As LongPtr ) As LongPtr Declare PtrSafe Function EmptyClipboard Lib "User32" () Every bit Long Declare PtrSafe Office lstrcpy Lib "kernel32" ( ByVal lpString1 Every bit Any , _ ByVal lpString2 Every bit Any ) As LongPtr Declare PtrSafe Part SetClipboardData Lib "User32" ( ByVal wFormat _ As Long , ByVal hMem As LongPtr ) As LongPtr # Else Declare Office GlobalUnlock Lib "kernel32" ( ByVal hMem As Long ) As Long Declare Function GlobalLock Lib "kernel32" ( ByVal hMem As Long ) Equally Long Declare Role GlobalAlloc Lib "kernel32" ( ByVal wFlags As Long , _ ByVal dwBytes As Long ) Equally Long Declare Function CloseClipboard Lib "User32" () As Long Declare Role 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 Equally Whatsoever ) As Long Declare Function SetClipboardData Lib "User32" ( ByVal wFormat _ As Long , ByVal hMem As Long ) As Long #Terminate If #End If Public Const GHND = & H42 Public Const CF_TEXT = 1 Public Const MAXSIZE = 4096 Sub ClipBoard_SetData ( MyString Every bit Cord ) #If Mac Then With New MSForms . DataObject . SetText MyString . PutInClipboard End With # Else #If VBA7 Then Dim hGlobalMemory Equally LongPtr Dim hClipMemory Equally LongPtr Dim lpGlobalMemory Equally LongPtr # Else Dim hGlobalMemory As Long Dim hClipMemory Equally Long Dim lpGlobalMemory As Long #End If Dim x As Long ' Allocate moveable global memory. '------------------------------------------- hGlobalMemory = GlobalAlloc ( GHND , Len ( MyString ) + i ) ' Lock the cake to get a far pointer ' to this retention. lpGlobalMemory = GlobalLock ( hGlobalMemory ) ' Copy the string to this global retentiveness. lpGlobalMemory = lstrcpy ( lpGlobalMemory , MyString ) ' Unlock the memory. If GlobalUnlock ( hGlobalMemory ) <> 0 Then MsgBox "Could not unlock memory location. Copy aborted." GoTo PrepareToClose End If ' Open the Clipboard to copy information to. If OpenClipboard ( 0 & ) = 0 Then MsgBox "Could non open the Clipboard. Re-create aborted." Get out Sub Cease If ' Clear the Clipboard. x = EmptyClipboard () ' Copy the data to the Clipboard. hClipMemory = SetClipboardData ( CF_TEXT , hGlobalMemory ) PrepareToClose : If CloseClipboard () = 0 Then MsgBox "Could not close Clipboard." Cease If #End If Terminate Sub Make powerful macros with our free VBA Programmer Kit
It's piece of cake to re-create and paste a macro like this, merely it's harder make one on your own. To help you lot brand macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros total of hundreds of pre-congenital macros to help you lot principal file I/O, arrays, strings and more than - catch your free copy below.
To actually place text on you clipboard, you would just pass the
Sub CopyToClipBoardWindows10 () ClipBoard_SetData "This is a sample string" End Sub This solution was adapted from a macro I saw here.
VBA Paste from Clipboard
One time you know how to copy, pasting is cinch. The syntax is practically identical to the Windows 7 copy examples shown before. Have a look.
Call back Clipboard Content
Sub PasteFromClipboard3 () Dim clipboard As MSForms . DataObject Dim str1 As String Prepare clipboard = New MSForms . DataObject clipboard . GetFromClipboard str1 = clipboard . GetText End Sub The GetFromClipboard method takes information from your clipboard and stores the data in the DataObject. The GetText method takes the cord from the DataObject and puts it in your string variable - in this example, in the string variable
VBA Clear Clipboard
How do you articulate the content stored in the clipboard, y'all ask? There are several means to clear your clipboard. The simplest is to fix your clipboard equal to an empty string.
Gear up Clipboard to Empty String
Windows seven and earlier
Sub ClearClipboard () Dim clipboard As MSForms . DataObject Set clipboard = New MSForms . DataObject clipboard . SetText "" clipboard . PutInClipboard End Sub Windows 8 and later
Sub ClearClipboard2 () ClipBoard_SetData "" Finish Sub A more thorough way of clearing your clipboard with VBA is to utilise our skilful friend, the user32 library.
Clear Entire Clipboard Contents
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! 'These first three declarations are merely needed if yous used Solution one '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Private Declare Function OpenClipboard Lib "user32" ( ByVal hwnd As Long ) Equally Long Private Declare Function EmptyClipboard Lib "user32" () As Long Private Declare Part CloseClipboard Lib "user32" () As Long Sub ClearClipboard3 () OpenClipboard ( 0 & ) EmptyClipboard CloseClipboard End Sub If yous're a long-time follower of the wellsr.com VBA weblog, you'll recognize user32. It's a staple of computer automation, and I've showcased it in several tutorials. In add-on to decision-making your clipboard, user32.dll can be used to movement your mouse and activate windows.
Annotation, the Individual declarations must be pasted at the very pinnacle of your module, before any procedures. If you lot already pasted the big "Solution two" macro for copying and pasting to your clipboard, you won't need to add together the Private declarations again, since they're already there.
Final Thoughts
The power to control your clipboard is a powerful feature of VBA. Y'all can use it to automate complex tasks. For example, in my nuclear engineering career, I regularly piece of work with large ASCII files on UNIX machines. I'll copy the text to my clipboard, run a VBA macro on Windows to perform string manipulation on the text, then store the last content in my desired format back to my clipboard. I'll and so apply this newly formatted cord to continue my work on the UNIX boxes. Clipboard manipulation makes for a wonderful mail service-processor!
For more than VBA tips, techniques, and tactics, subscribe to our VBA Insiders email serial using the form below.
Share this article on Twitter and Facebook, then exit a annotate beneath and allow's accept a discussion.
Ready to do more with VBA?
We put together a behemothic PDF with over 300 pre-congenital macros and nosotros want yous to have it for gratuitous. Enter your email address below and we'll send you a copy along with our VBA Developer Kit, loaded with VBA tips, tricks and shortcuts.
Source: https://wellsr.com/vba/2015/tutorials/vba-copy-to-clipboard-paste-clear/
0 Response to "Ms Access Vba Copy To Clipboard"
Post a Comment