Pass Any Information Between VBA Applications Using The Clipboard

Pass Any Information Between VBA Applications Using The Clipboard:

Just when I thought that I was finished with my latest batch of VBA tips and tricks, 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 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 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.

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.



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.



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.

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.



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