Pass Data Between VBA Applications Using the Clipboard

Facilitating Communication Between VBA Applications

While compiling a collection of VBA techniques, a particularly useful method for exchanging data between applications employing VBA scripting was uncovered.

VBA provides a substantial array of features and tools commonly found in full Visual Basic applications, though often in a streamlined capacity.

The Challenge of Inter-Application Communication

A common difficulty when working with VBA within individual applications lies in establishing communication between them.

Variables utilized in one application are typically confined to its scope, preventing direct access from other programs on the system.

This limitation can make data transfer complex.

Traditional Methods and Their Drawbacks

Traditionally, programmers have often relied on data files to facilitate information exchange between VBA-based applications like Microsoft Word or Excel.

Although functional, this approach introduces vulnerabilities, such as the risk of file deletion or unauthorized modification, potentially disrupting the process.

Leveraging the Clipboard for Data Transfer

For rapid, short-term data transactions, utilizing the system clipboard presents an alternative solution.

Data can be copied to the clipboard from one application and subsequently retrieved by another.

This method offers a quick way to pass information without the complexities of file-based communication.

VBA provides the tools to both write to and read from the clipboard, making this a viable option for many scenarios.

Transferring Data via the Clipboard

This article details a method for transferring textual data – specifically, the contents of three text fields – to an Excel spreadsheet launched by a Word macro.

The initial configuration involves a Word document in Design Mode, featuring three labels and corresponding text fields for user input. Activating the "Submit" button initiates the data transfer process.

To begin, access the Visual Basic Editor by double-clicking the newly created button while in Design Mode. The first step is to incorporate a reference granting access to the system clipboard. Navigate to Tools, then select "References".

Within the references list, locate and select "Microsoft Forms 2.0 Object Library". Confirm your selection by clicking "Okay", thereby enabling your VB editing session to utilize clipboard-related methods.

Now, the code for the "Submit" button can be implemented. The following code snippet demonstrates the data transfer logic.

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")

The process is straightforward. A DataObject, representing the clipboard, is instantiated. Field values are then concatenated into a single string, separated by commas (or a chosen delimiter).

Subsequently, the Excel file specified in the Shell command needs to be created. Within Excel, add the "Microsoft Forms 2.0 Object Library" reference. Then, utilize the following script within the Workbook.Open() function.

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

This script retrieves data from the clipboard, stores it in a string, and then divides it into an array based on the delimiter. Finally, it iterates through the array, populating column 2 of "Sheet1" with each value.

The outcome of this process is demonstrated below.

With some adaptation, this technique can be applied to launch and populate various VBA-based applications.

An alternative approach involves directly manipulating the Excel file using the Excel reference library. However, this method operates silently. The clipboard method, conversely, explicitly opens Excel and allows a startup macro to handle the data transfer.

This is particularly beneficial when interfacing with applications, such as operator interfaces in manufacturing environments, that rely on VBA but may lack the necessary libraries for direct linking. Utilizing the clipboard provides a swift, simple, and effective solution.

Experiment with the provided scripts and share your experiences. Were modifications required? Do you have suggestions for improvement? Please share your feedback in the comments below.

Image Credit: File Transfer Image Via Shutterstock