Pinterest Stumbleupon Whatsapp
Ads by Google

put text into clipboard vbaJust when I thought that I was finished with my latest batch of VBA tips and tricks How To Send Emails From An Excel Spreadsheet Using VBA Scripts How To Send Emails From An Excel Spreadsheet Using VBA Scripts In the past, I’ve used email a whole lot in my batch jobs and other automated scripts, just like I’ve described in past articles. These are great for those times when you have a script... Read More , I discovered yet one more really cool technique that anyone can use to easily pass information between two applications that utilize VBA as a back-end scripting language.

The thing that’s great about VBA is that it gives you so many of the features and tools 3 Ways To Open Applications & Windows Maximized With VB Script [Windows] 3 Ways To Open Applications & Windows Maximized With VB Script [Windows] If there is one thing I love to do, it's write VB scripts. Whether it's a Windows Script file or a VBA script inside an application, scripting provides the ability to add functionality to applications... Read More that are normally part of VB applications, albeit in a somewhat watered-down version.

One of the most frustrating parts of working with VBA inside specific applications, is that it’s not always easy to get two applications to “talk” to each other. This is because all of the variables that you’re using are only accessible within the scope of the application itself, not from anywhere else on the computer.

With that said, there are ways to pass information How To Integrate Excel Data Into A Word Document How To Integrate Excel Data Into A Word Document During your work week, there are probably lots of times that you find yourself copying and pasting information from Excel into Word, or the other way around. This is how people often produce written reports... Read More between VBA-based applications like Word or Excel. In many cases, even in a professional environment, programmers resort to communicating between applications with data files. This can work, but it introduces an element of potential tampering or error – like the deletion or modification of those files – to mess up the works.

Another approach you can try for very quick transactions of information between applications is passing data to the clipboard, and then reading that information from the other application.

Passing Information Using the Clipboard

In this example, I’m going to show you how to pass three pieces of text information – values inside 3 text fields – directly to an Excel spreadsheet that the Word macro launches.

Ads by Google

The starting setup is shown below. It’s a Word document where I switched to Design Mode and created 3 labels and 3 text fields for the user to type in information. Clicking on the “Submit” button will launch the data-transfer code.
put text into clipboard vba
Double click the new button you’ve created in Design Mode to get into the VB Editor screen. The first thing you’re going to want to do is add the reference that gives you access to the system clipboard. Click on Tools – References.
vba clipboard
Scroll down the list of references and select “Microsoft Forms 2.0 Object Library”. Once you click Okay, your VB editing session now has access to the methods that allow you to read or write from the clipboard.

vba clipboard

That’s exactly what we’re going to do now. In the function for the “Submit” button, I’ve entered the following code.

Dim strClipText As DataObject
Dim strInputText As String
Dim errCode As Integer

Set strClipText = New DataObject

strInputText = ThisDocument.txtBox1.Value & "," & ThisDocument.txtBox2.Value & "," & ThisDocument.txtBox3.Value

strClipText.SetText strInputText
strClipText.PutInClipboard

'Set objWB = objExcel.Workbooks.Open("c:/temp/MyExcelFile.xlsm")
errCode = Shell("C:\Program Files (x86)\Microsoft Office\Office12\excel.exe c:/temp/MyExcelFile.xlsm")

See how simple it is? The strClipText data object is the clipboard object that you can manipulate. First, read in all of the field values into a single string variable, with each data field delimited with a comma, or whatever works for you.

Next, you’re going to need to create the Excel file that you’re opening in the Shell command above. In Excel, you’re going to want to add the same reference to “Microsoft Forms 2.0 Object Library”, and then in the Workbook.Open() function, you can run the following script.

Dim msObj As MSForms.DataObject
Dim strText As String
Dim strResult() As String
Dim intArrayCount As Integer
Dim x As Integer

Set msObj = New MSForms.DataObject

msObj.GetFromClipboard

strText = msObj.GetText

strResult() = Split(strText, ",")

intArrayCount = Application.CountA(strResult)

For x = 0 To intArrayCount - 1
    ThisWorkbook.Sheets("Sheet1").Cells(x + 1, 2).Value = strResult(x)
Next

Again, when you step through it, this is a simple and very fast script. It creates the MSForms object and gets the most recent data from the clipboard and places it into a string variable. Then, it splits that single, long string variable by whatever delimiter you used into an array of individual strings, and finally it steps through the array (CountA gives you the length of the array) and outputs each value to the sheet in column 2.

Here are the final results.
put text into clipboard vba
Now, with a little bit of creativity, you can use this technique to launch any VBA-based application and “paste” information into it.

You could always do the same thing using the Excel reference library, but in that case you’d be silently opening the Excel file and manipulating the data inside that file. In the case above, you’re literally opening the Excel application and displaying the file, and letting the startup Macro in Excel do the rest of the work.

This is most useful when there are other applications, like many of the operator interface applications in many manufacturing facilities, that are based on VBA but might not have those libraries available to tightly link the two applications. Using the clipboard in an automated way like this is quick, easy, and gets the job done.

Give the scripts above a try and let us know how it works on your system. Did you have to tweak it? Do you have any suggestions for how to make the code even better? Share your thoughts in the comments section below.

Image Credit: File Transfer Image Via Shutterstock

  1. Heather
    February 5, 2015 at 11:51 pm

    I know this is way past when you posted this - but thank you, this was very helpful to me in a project I have been working on between Word and Excel.

  2. L. Vaatainen
    June 20, 2012 at 10:26 am

    Thank you for good advices. Tons of the other posts are useless as those writers didn't say the most important: you need a reference to Forms Object Library and how to find it.

    For those who don't have Forms Object Library ready in the reference list (I didn't had): Press the Browse key and locate FM20.dll from your hard disk. I found it in the System32 directory.

  3. ozdave
    June 11, 2012 at 1:12 pm

    Is there an example that copies data from a website onto the clipboard and then download it into an Excel file?

    • Ryan Dube
      June 16, 2012 at 1:09 am

      Hi ozdave - that's scraping with VBA and beyond the scope of what I did with this article. However - it's an interesting idea and something I may play with... Meanwhile, you might also want to give the folks at MUO Answers a try, there may be some VBA experts there that know how right off the bat. http://www.makeuseof.com/answers/

      Good luck! Meanwhile, I'll start doing some research myself..thanks for the great idea.

  4. beijing massage VU
    May 8, 2012 at 10:35 am

    i really like this blog because it provides lots of useful information from different themes..thanks a lot.

Leave a Reply

Your email address will not be published. Required fields are marked *