Chapter 29
Accessing One Application from Another Application

So far, this book has focused on how to work with VBA to perform actions within a VBA host application, such as Word or Access.

But you might sometimes need to communicate between applications as well. This chapter demonstrates four tools you can use to contact and manipulate one application from another: Automation, data objects, Dynamic Data Exchange (DDE), and SendKeys.

Understanding the Tools Used to Communicate Between Applications

The Office applications that this chapter uses as examples offer several tools for communicating with other applications:

  • Automation Formerly known as Object Linking and Embedding (OLE), Automation is usually the most effective method for transferring information from one Windows application to another. If the applications you're using support Automation, use it in preference to the alternatives (DDE and SendKeys).
  • Dynamic Data Exchange (DDE) An older method of transferring information between applications that remains a good fallback when Automation isn't available. DDE is offered in only some applications.
  • SendKeys The oldest and most primitive method of communicating between applications, SendKeys relies on sending keystroke equivalents to the other application. It's an attempt to pretend that someone is typing on the keyboard. But this approach can cause timing and other issues. Nonetheless, even though it's rudimentary by comparison to Automation and DDE, SendKeys can still be effective in some situations. So you need to at least know it's available should the other two approaches be impractical.

Beyond these three communications tools, this chapter discusses the DataObject object, which you can use to store information and to transfer information to and from the Windows Clipboard.

Using Automation to Transfer Information

Automation is the most powerful and efficient way to communicate between applications, so we'll look at it first. Each application that supports Automation offers one or more Component Object Model (COM) objects that you can access programmatically—usually an object representing the application, an object representing the various types of files the application uses, objects representing its major components, and so on.

For any Automation transaction, there's a server application that provides the information or tools and a client application that receives or employs them. (There's also another pair of terms that distinguish between two communicating applications: The server application is also sometimes known as the object application, and the client application is known as the controlling application.)

Automation lets the client application harness the built-in capabilities of the server application. For example, Excel has better calculation features than Word and can generate useful charts, data maps, and so on based on its calculations and data. By using Automation, Word can borrow Excel's calculation engine and then insert the results into a Word document. Or, Word could use Excel to create a chart that it then inserts into a document as well. Word can also take more-limited actions, such as causing Excel to open a workbook, copy a group of cells from a spreadsheet in it, and paste-link them into a document.

To use Automation through VBA, you create an object in VBA that references the application you want to work with. You use the CreateObject function to create a new object in the other application and the GetObject function to retrieve an existing object from the other application.

When using Automation, you can choose whether to display the server application or keep it hidden from the user. For some procedures, you'll need to display it—for example, the user might need to choose a file or a folder or make another choice that requires live intervention. In other situations, it can be best to keep the server application hidden so that the user isn't distracted by an application suddenly launching itself spontaneously and robotically carrying out actions in front of the user's startled eyes. This can make some users uneasy, and conclude that the computer has gotten out of control.

But even if you decide to hide a server application from the user when the procedure runs, in most cases it's helpful to display the server application to yourself while you're writing and testing the procedure. That can make it easier to see what's going wrong if your code doesn't work as expected.

Understanding Early and Late Binding

When you use Automation to access another application, you can choose which type of binding to use—that is, how to establish the connection between the client application and the server application.

Early binding involves adding a reference to the application's object library by using the References dialog box (Tools ➢ References) and then declaring an object at the start of the code by using a Dim statement that declares the specific object class type rather than declaring the object generically As Object.

For example, the following code connects to a slide within a PowerPoint presentation by using early binding:

Dim myPowerPoint As PowerPoint.Application
Dim myPresentation As Presentation
Dim mySlide As Slide
Set myPowerPoint = CreateObject("PowerPoint.Application")
Set myPresentation = myPowerPoint.Presentations.Add
Set mySlide = myPresentation.Slides.Add(Index:=1, Layout:=ppLayoutTitleOnly)

With late binding, you create an object that references the other application on the fly when the code executes. If you declare the object explicitly, you declare it as a generic object—As Object—rather than declare it as a specific object class type.

For example, the following statements declare the Object variable myOutlook and then assign to it a reference to an Outlook.Application object:

Dim myOutlook As Object
Set myOutlook = CreateObject("Outlook.Application")

If the server application you're using supports early binding, use it in preference to late binding. There are three advantages to early binding:

  • Once you've added to the project the reference to the application's object library, you can dynamically work in your code with the outside (server) application's objects, properties, and methods through the VBA Editor in the client application. This makes it much easier to use the Editor's built-in IntelliSense features to find the objects, properties, and methods you need in the application you're referring to, and to avoid mistakes such as typos and missing arguments.
  • Because you specify the particular type of object when you declare the object variable, you're less likely to attempt to work with the wrong object by mistake.
  • Because VBA can compile more information about the object, elements of its methods and properties need not be resolved during runtime. So it runs faster.

On the other hand, late binding can avoid object-library issues such as having to make the right references and other library-version problems.

Creating an Object with the CreateObject Function

The CreateObject function creates and returns a reference to an Automation object exposed to other applications. The syntax is as follows:

CreateObject(class [,servername])

Here, class is a required argument specifying the class (the formal definition) of the object to create. The class argument consists of the name of the library that will provide the object and the type of object to be provided, so it looks like this:

applicationname.objecttype

For example, to specify the Excel Application object as a class, use a class argument of Excel.Application. Here, Excel is the name of the application that provides the object, and Application is the type of object that we want Excel to provide. Likewise, Excel.Sheet would specify a worksheet object in Excel.

servername is an optional string Variant that specifies the name of the network server on which to create the object. If you merely want to connect to an application located on the user's machine (in other words, if both applications—the client and server applications—are located on the same hard drive), omit servername or specify an empty string. To connect with an application located on a remote server machine, you must have DCOM (the Distributed Component Object Model) installed, and the object on the server computer must be configured to allow remote creation.

Typically, you'll use a CreateObject function with a Set statement to assign to an object variable the object that you create. For example, the following statements declare an object variable named myNewSheet and assign an Excel worksheet object to it:

Dim myNewSheet As Object
Set myNewSheet = CreateObject("Excel.Sheet")

Returning an Object with the GetObject Function

The GetObject function returns a reference to an existing Automation object. The syntax is as follows:

GetObject([pathname] [, class])

You can provide either argument—but you must provide one of them. Here, pathname is an optional string Variant specifying the full path and name of the file that contains the object you want to retrieve. pathname is optional, but if you don't specify it, you must specify the class argument. class (which is optional if you specify pathname, but required if you don't) is a string Variant specifying the class of the object you want to return.

As with CreateObject, typically, you'll use a GetObject function with a Set statement to assign to an object variable the object that you return with the GetObject function. For example, in the second of the following statements, the GetObject function returns an object consisting of the workbook Z:FinanceRevenue.xlsm. The Set statement assigns this object to the object variable named Revenue declared in the first statement:

Dim Revenue As Object
Set Revenue = GetObject("Z:FinanceRevenue.xlsm")

Here, the workbook is associated with Excel. When this code runs, VBA starts Excel if it isn't already running and activates the workbook. You can then reference the object by referring to its object variable; in this example, you could manipulate the Revenue object to affect the Z:FinanceRevenue.xlsm workbook.

Examples of Using Automation with the Office Applications

The following sections show three examples of using Automation with Office applications.

TRANSFERRING INFORMATION FROM AN EXCEL SPREADSHEET TO A WORD DOCUMENT

This example transfers information from an Excel spreadsheet to a Word document.

First, you need to add to the target Word project (the client project that will contain the code that accesses Excel) a reference to the Excel object library. Follow these steps:

  1. Start or activate Word, and then press Alt+F11 to launch the VBA Editor.
  2. In the Project Explorer, click the project to which you want to add the reference. For example, if the procedure or procedures will reside in the Normal.dotm template, select the Normal project in the Project Explorer before adding the reference. Or, just choose Insert ➢ Module to create a brand-new module to play around with.
  3. Choose Tools ➢ References to display the References dialog box.
  4. Select the check box for the Microsoft Excel 16.0 Object Library item.
  5. Click the OK button to close the References dialog box.

Once you've added the reference, you can use the VBA Editor's Object Browser to browse Excel objects. Display the Object Browser as usual by pressing F2 or choosing View ➢ Object Browser, and then choose Excel in the Object Browser's Project/Library drop-down list. The Object Browser will display the contents of the Excel object library, as shown in Figure 29.1. You can display the help (code examples, syntax) for a selected Excel object by clicking the Help button (the question-mark icon) in the Object Browser.

Screenshot of the Object Browser from the VBA Editor session launched from Microsoft Word, to view the contents of the loaded Excel object library.

Figure 29.1 Once you've loaded the Excel object library, you can view its contents in the Object Browser from the VBA Editor session launched from the host application (in this case, Microsoft Word).

To create and test the next code example, first set up in Excel the preconditions that this procedure expects: namely, a range object named SalesTotal. To do this,

  1. Open Excel, and right-click a cell anywhere in the displayed sheet in Book1 (the default name of the first blank workbook).
  2. If you don't see a workbook named Book1, choose File ➢ New, and then click the blank workbook icon in the displayed templates.
  3. Type 145 into cell A1.

    It's this value that your macro in Word will pluck from this workbook.

  4. Now click the File tab in the Ribbon, choose Save As, and save this workbook as Book1.xlsx in your C: emp subdirectory. (Note that you're saving it as an .xlsx file.)
  5. Now you can either leave Excel running or just close it.

    It won't matter because your macro will open the file on the hard drive.

Okay, now in Word's VBA Editor, add the code. Because you used early binding, you have available the Editor's IntelliSense assistance and code-completion features. Create the procedure shown in Listing 29.1. This procedure uses the GetObject function to retrieve the information from the specified cell in the Excel spreadsheet you previously created and inserts this data in the active Word document at the current insertion point (where the blinking cursor is).

This macro retrieves one piece of information from an Excel spreadsheet that's on the hard drive in the C: emp directory. Here's what happens in the macro:

  • Line 3 declares the object variable mySpreadsheet of the type Excel.Workbook.
  • Line 4 declares the String variable strSalesTotal.
  • Line 6 uses a Set statement and the GetObject function to make mySpreadsheet reference the spreadsheet C:TempBook1.xlsm.
  • Line 9 assigns to the String variable strSalesTotal the Value property (the actual data) of the Range object cell A1 You defined the SalesTotal range as a single cell, so strSalesTotal receives the value of that cell.
  • Line 11 assigns to the mySpreadsheet object the special value Nothing, releasing the memory it occupied.

    Because the procedure ends almost immediately afterward, this statement isn't necessary here. VBA will destroy it at the end of execution of the procedure. But, it's good practice to free the memory assigned to an object when you no longer need to use the object, just to get into the habit.

  • Line 13 uses the TypeText method of the Selection object in Word to enter a string of text and the strSalesTotal string at the current selection.
  • Line 15 uses the TypeParagraph method to insert a paragraph after the text.

If you have trouble getting this example to work, double-check the following:

  • Choose Tools ➢ References in the Editor to ensure that the check box next to Microsoft Excel 16.0 Object Library is checked.
  • Also, the reference to this library will have moved up to the top of the References list along with whatever other libraries are currently included.
  • If you see an error message stating “Run-time error ‘432’: File name or class name not found during Automation operation,” it means that there's something wrong in this line of code: 
    Set mySpreadsheet = _
    GetObject("C:TempBook1.xlsx")

    Either you've mistyped this path in your code (such as typing C:Docs rather than C:Temp) or you have not saved an Excel file named Book1.xlsx to this folder.

  • If you see an error message stating “Run-time error ‘1004’: Method ‘Range’ of object ‘_Application’ failed,” this is an error in the following line of code: 
    strSalesTotal = mySpreadsheet.Application.ActiveSheet.Range("A1").Value

    If this code fails, you've got a typo in the code, such as specifying the wrong range cell. Ensure it's A 1 (the number 1 not the letter L). Then resave the workbook. Remember, the workbook is opened by this macro as a file on the hard drive—not accessed in the Excel application itself.

TRANSFERRING INFORMATION FROM A WORD DOCUMENT TO AN EXCEL WORKBOOK

We managed to send data from Excel to Word in the previous section. Now let's go the other way.

This next procedure (Listing 29.2) runs as a macro in Word. The procedure requires that Excel be currently running, so the macro checks for the possibility that Excel isn't executing and, if necessary, handles the problem itself by starting Excel. The procedure creates a new Excel workbook and then transfers the current document's word count from Word to the workbook.

For this example to work, you must store a Word .docm file named test.docm in your C: emp directory.

As before, you'll find creating this procedure easier if you first add to the current Word project a reference to the Excel object library. (See the previous section for instructions.)

Here's what happens in Listing 29.2:

  • Line 2 is a spacer. In fact, all blank lines are just spacers—so I won't mention them again.
  • Line 3 declares the Variant variable that will be assigned the number of words in a Word document.

    Later, in line 33, this same variable assigns its value to an Excel cell.

  • Line 4 declares the String variable strPath that will hold the file path to the Word document.
  • Line 5 declares the String variable strFile that will hold the Word document's filename.
  • Line 6 declares the Document variable docCurDoc; it will point to the Word document when it is opened using the Open method of the Documents object.
  • Line 7 declares an Excel.Application object variable myXL, and line 8 declares an Excel.Workbook object variable myXLS.
  • Line 9 declares the constant errExcelNotRunning, setting its value to 429. This error number indicates that the procedure attempted to manipulate Excel while no instance of Excel was currently executing.
  • Line 10 declares the constant errDocNotAvailable, setting its value to 5174. This error number indicates that the Word document your procedure attempted to open could not be found.
  • Line 12 starts error handling for the procedure, directing execution to the code below the label Handle in the event of an error.
  • Line 17 opens the Word document specified by strPath, a backslash, and strFile, assigning the document object to the docCurDoc variable.

    If the document isn't available, an error occurs and execution is transferred to the error-handler code that starts in line 43. This error number matches the constant defined in the procedure as errDocNotAvailable, so a message box informs the user that the Word document wasn't found. Then execution is transferred to the Shutdown label where the two object variables are destroyed and the procedure is exited.

  • Line 22 can also potentially trigger an error condition. It attempts to assign a currently executing instance of Excel to the object variable myXL. However, if this attempt fails, execution is transferred to the Handle label.

    If Excel isn't running at this point, error 429 (“ActiveX component cannot create object”) occurs, so line 44 in the error handler checks for this error by using the constant errExcelNotRunning. If it matches the error number, line 46 assigns to myXL a new instance of Excel that it creates by using the CreateObject function. Line 47 then uses an Err.Clear statement to clear the error, and line 48 contains a Resume Next statement to cause VBA to resume execution back up at the next statement following the offending statement.

  • One way or another, by the time line 24 is executed, myXL refers to a running instance of Excel.
  • Line 24 sets the Visible property of myXL to True so that it appears onscreen.
  • Line 25 assigns to myXLS a new workbook created by using the Add method of the Workbooks object in myXL.
  • Line 26 positions the insertion pointer in the first cell.
  • Line 27 assigns to the active cell in myXL the text Word Count.
  • Line 29 assigns the document's word count value to the variable WordCount. This value is accessed by using the wdPropertyWords property from the BuiltInDocumentProperties collection of docCurDoc.
  • Line 32 moves the insertion cursor down one row in Excel to cell A2.
  • Line 33 displays the word count in that cell.
  • Finally, line 35 closes the Word document without saving any changes that may have been made to it while it was opened for inspection.
  • Line 41 contains an Exit Sub statement to exit the procedure at this point—to avoid permitting execution to continue down into the zone where the error-handling statements are. Using an Exit Sub like this is common when a procedure includes an error handler at the end.

PLACING A POWERPOINT SLIDE IN AN OUTLOOK MESSAGE

The next procedure shows how to communicate between PowerPoint and Outlook. This procedure, run from PowerPoint, returns the existing instance of Outlook or (if there is none) creates a new instance. The procedure then uses PowerPoint to send a message that gives details drawn from the presentation.

Listing 29.3 shows the procedure. There's one complication: Because PowerPoint doesn't have a central macro storage project like Word's Normal.dotm or Excel's Personal Macro Workbook, the code must be stored in an open presentation. This could be the presentation that is the subject of the email, but it is much more convenient to maintain a code-only presentation that you open at the beginning of all PowerPoint sessions that require the use of code. This becomes your own personal macro-storage system.

In any case, you need some slides from which to pick information that will be sent (and you also need to provide your email address), so follow these steps to set up the necessary preconditions for the upcoming example.

First, prepare the target PowerPoint project (the project that will contain the code that accesses Outlook and will contain the slides you're accessing):

  1. Start PowerPoint.
  2. In the search field at the top of PowerPoint's start page, type Photo Album and then press Enter.
  3. Click the Contemporary Photo Album presentation, and then click the Create button to load it into PowerPoint.
  4. Launch the PowerPoint VBA Editor by pressing Alt+F11.
  5. In the VBA Editor, choose Insert ➢ Module to open a code module where you can put a macro.
  6. Choose Tools ➢ References to display the References dialog box.
  7. Select the check box for the Microsoft Outlook 16.0 Object Library item.
  8. Click OK to close the References dialog box.

Now enter the code from Listing 29.3 into the module you inserted in step 3. Be sure to replace my email address in line 23 with your email address.

Here's what happens in Listing 29.3:

  • Line 3 declares a Presentation object variable named myPresentation.
  • Line 4 declares a String variable named strPresentationFilename, which is used for storing the path and filename of the presentation.
  • Line 5 declares a String variable named strPresentationTitle, which is used to store the title of the presentation. Line 6 declares a String variable named strPresentationPresenter, which is used to store the name of the presenter of the presentation.
  • Line 7 declares an Outlook.Application object variable named myOutlook that is used to represent the Outlook application.
  • Line 8 declares an Outlook.MailItem object variable named myMessage that is used to represent the message that the procedure creates.
  • Line 9 declares a constant named errOutlookNotRunning and assigns to it the number 429, the error number returned if no instance of Outlook is available when the GetObject function tries to access it.
  • Line 11 starts error handling for the procedure, directing execution to the label ErrorHandler (in line 36) in the event of an error.
  • Line 13 assigns the active presentation to the myPresentation object variable.
  • Lines 14 through 18 contain a With structure that works with myPresentation:
    • Line 15 assigns the FullName property of myPresentation to strPresentationFilename.
    • Line 16 assigns to strPresentationTitle the Text property of the TextRange object in the TextFrame object in the third Shape object on the first Slide object—in other words, the text from the first placeholder shape on the first slide in the presentation.
    • Similarly, line 17 assigns to strPresentationPresenter the text from the second shape on the second slide.
  • Line 20 assigns to myOutlook the current instance of Outlook, which it returns using the GetObject function. If Outlook isn't running at this point, error 429 (“ActiveX component cannot create object”) occurs, so
    • Line 37 in the error handler checks for this error by using the constant errOutlookNotRunning.
    • If it matches, line 38 assigns to myOutlook a new instance of Outlook that it creates by using the CreateObject function.
    • Line 39 then uses an Err.Clear statement to clear the error.
    • Line 40 contains a Resume Next statement to cause VBA to jump back up in the code and resume execution where it left off (at the statement after the offending statement).
  • Line 21 uses the CreateItem method of the Outlook Application object (represented by myOutlook) to create a new mail item (a new email), which it assigns to myMessage.
  • Lines 22 through 29 contain a With structure that works with myMessage.
  • Line 23 assigns recipients by setting the To property. (You should change this line to your own email address so you can test this code and receive the message it sends.)
  • Line 24 is a placeholder.
  • Line 25 enters text for the Subject property.
  • Line 26 specifies that the message use HTML formatting (.BodyFormat = olFormatHTML).
  • Line 27 assigns text to the body of the message by using the Body property.
  • Line 28 then uses the Send method to send the message.
  • Line 31 uses the Quit method to close myOutlook.
  • Line 33 sets myMessage to Nothing, releasing the memory it occupied.
  • Similarly, line 34 sets myOutlook to Nothing.
  • Line 35 then exits the procedure.
  • As discussed earlier in this list, the primary function of the error handler is to launch an instance of Outlook if none is currently running. If any error other than error 429 occurs, execution branches to the Else statement in line 41, and line 42 displays a message box that gives the error number and description.

If you test this example, be sure to remember to change line 23 from my email address to your email address. When the procedure finishes execution, look in your Inbox in Outlook for the new email message.

Using the Shell Function to Run an Application

Instead of using the CreateObject function to start an application and return a reference to it, you can use the Shell function to run an application. Shell can run any executable program, and its syntax is straightforward:

Shell(pathname[,windowstyle])

Here, pathname is the file path and program name of the program you want the Shell command to execute. Also include in the pathname any necessary command-line switches or arguments required by that program.

This example opens Internet Explorer, maximizes its window, and then switches the focus to it:

Sub OpenIE()
 
Dim id
 
id = Shell("c:program filesinternet exploreriexplore.exe", vbMaximizedFocus)
 
End Sub

windowstyle is an optional integer Variant that you use to specify the type of window in which to run the application and to switch focus to the newly launched application. Table 29.1 lists the constants and values for windowstyle.

TABLE 29.1: Constants and values for the windowstyle argument

CONSTANT VALUE WINDOW STYLE
vbHide 0 Minimized and hidden, but with focus
vbNormalFocus 1 Normal (“restored”) with focus
vbMinimizedFocus 2 Minimized with focus (the default)
vbMaximizedFocus 3 Maximized with focus
vbNormalNoFocus 4 Normal (“restored”) without focus
vbMinimizedNoFocus 6 Minimized without focus

Using Data Objects to Store and Retrieve Information

As you've seen so far in this book, you can store information in many places using VBA. But there's also a uniquely useful data object with the ability to copy information to, and retrieve information from, the Clipboard. This chapter is all about ways to communicate between applications, and the Clipboard is one such way.

A data object is attached to a UserForm object in the Microsoft Forms object model, but you can use a data object by itself with no user form displayed. This is similar to the way that you can create and manipulate a hidden Access database with no visible interface displayed to the user. (This technique is described in the section titled “Opening Multiple Databases at Once” in Chapter 28 .)

A data object, which is represented in VBA by the DataObject object, is used to store data. Each data object can hold multiple pieces of text information, and each piece must be in a defined format. You can create and use multiple data objects to store multiple pieces of data in the same format, or you can be tricky and tell VBA that information is in a different format when really it's not.

At any given time, the Clipboard can contain one text item and one item in another format, such as a picture. If you copy another text item to the Clipboard, that item will overwrite the previous text item, but any graphical item on the Clipboard will remain unscathed. Likewise, if you copy a picture to the Clipboard, it will overwrite any previous graphical item (or indeed any type of non-text item) stored in the Clipboard, but any text item in the Clipboard will be unaffected.

The data object works in a way similar to the Clipboard. However, a data object can't store graphics. It can store multiple pieces of text information, each defined as being in a different format.

Creating a Data Object

To create a data object, declare an object variable of the DataObject type and then use a Set statement to assign a new DataObject object to it. For example, the following statements declare a DataObject variable named myDObj and assign a new DataObject to it:

Dim myDObj As DataObject
Set myDObj = New DataObject

Storing Information in a Data Object

To store information in a data object, use the SetText method, which has the following syntax:

object.SetText(StoreData [,format])

The components of the syntax are as follows:

  • object is a required argument specifying a valid object.
  • StoreData is a required argument specifying the data to store in the data object.
  • format is an optional argument containing an Integer value or a String specifying the format of the information in StoreData. A value of 1 indicates text format; a value other than 1 or a String indicates a user-defined format.

For example, the following statement stores the text Sample text string in the DataObject named myDObj:

myDObj.SetText "Sample text string"

The following statement stores the text Sample formatted text string in the DataObject named myDObj, defining and using the custom format myFormat:

myDObj.SetText "Sample formatted text string", "myFormat"

Once the custom format has been defined and stored in the data object, you can access the data stored in that format by specifying the format. In this case, no formatting is actually involved—the code simply uses the format argument to create and identify a different data slot in the data object so that the new string doesn't overwrite the existing text string. It's a trick.

Returning Information from a Data Object

To return information from a data object, use the GetText method of the DataObject object. The GetText method has the following syntax:

object.GetText([format])

The components of the syntax are as follows:

  • object is a required argument specifying a valid object.
  • format is an optional argument containing a String or an Integer specifying the format of the data to retrieve.

For example, the following statement displays a message box containing the plain-text string stored in the DataObject named myDObj:

MsgBox myDObj.GetText

The following statement assigns to the String variable strTemp the text stored with the myFormat format in the DataObject named myDObj:

strTemp = myDObj.GetText("myFormat")

Here's a working code example that illustrates how to create a data object and then uses it to store and retrieve information. First, choose Tools ➢ References in the Editor to ensure that the check box next to Microsoft Forms 2.0 Object Library is checked. Note that it's likely this library will not be in its correct alphabetic location in the list of libraries in the References dialog box. Instead, it will probably be checked already by default and, thus, found in the first 10 or so libraries at the top of the References list. Recall that once a library has been selected, it moves to the top of the list.

Type this working example into an application's VBA Editor, and press F5 to see it execute:

Sub StoreText()
 
   Dim myDObj As DataObject
 
   Set myDObj = New DataObject
 
   myDObj.SetText "Sample text string"
 
   MsgBox myDObj.GetText
 
 
End Sub

Assigning Information to the Clipboard

To put text into the Clipboard from a data object, use the PutInClipboard method of the DataObject. For example, the following example creates a new data object named myDO, assigns to it the text Nasta Nasta Gomes, and then assigns that text to the Clipboard:

Sub ManageClipboard()
 
Dim myDO As New MSForms.DataObject
 
myDO.SetText "Nasta Gomes"
myDO.PutInClipboard
 
myDO.GetText
MsgBox myDO.GetText
 
 
End Sub

To fetch whatever text information is in the Clipboard and store it in a data object, use the GetFromClipboard method of the DataObject object. The previous example uses the data object referenced by the variable myDO, assigns to it the text from the Clipboard, and then displays the text.

To return formatted information from the Clipboard and store it in a data object, use the GetFormat method of the DataObject object.

Finding Out Whether a Data Object Contains a Given Format

To find out whether a data object contains a given format, use the GetFormat method of the DataObject object. The syntax for the GetFormat method is as follows:

object.GetFormat(format)

Here are the components of the syntax:

  • object is a required argument that returns a valid DataObject object.
  • format is an Integer or String specifying the format you're looking for. If the DataObject contains the format, GetFormat returns True; if not, GetFormat returns False.

For example, the following statement checks to see if the DataObject named myDO contains the format myHTML and assigns the format's contents to the string strHTMLText if it does:

If myDO.GetFormat("myHTML") = True Then _
    strHTMLText = myDO.GetText(Format:="myHTML")

Communicating via DDE

If the application with which you want to communicate doesn't support Automation, you can try Dynamic Data Exchange (DDE). DDE is a protocol that establishes a channel between two applications through which they can automatically exchange data. DDE can be tricky to set up, but once you get it working, it is usually reliable.

Not all applications support DDE. Among the Office applications, Word, Excel, and Access support DDE, but PowerPoint and Outlook do not. What's more, Microsoft warns that DDE is not a secure technology. So use it only in situations where you aren't vulnerable to outside intrusion, where the data is innocuous, or where it's public information like today's weather.

In the following descriptions of DDE statements, I'll use the term method in its more generic, non-OOP sense. Long, long ago when DDE was introduced (in Windows 3.0!), object-oriented programming wasn't yet fashionable.

A typical DDE conversation can contain the following actions:

  • Using the DDEInitiate method to start a DDE connection and establish the channel on which the connection operates
  • Using the DDERequest method to return text from the other application or the DDEPoke method to send text to the other application
  • Using the DDEExecute method to execute a command in the other application
  • Using the DDETerminate method to close the current DDE channel or using the DDETerminateAll method to close all the DDE channels

Using DDEInitiate to Start a DDE Connection

To start a DDE connection, you use the DDEInitiate method, which employs the following syntax:

expression.DDEInitiate(App, Topic)

The components of the syntax are as follows:

  • expression is an optional expression specifying an Application object.
  • App is a required String argument specifying the name of the application with which the DDE connection is to be started.
  • Topic is a required String argument specifying the DDE topic (such as an open file) in the application. To discover the list of topics available for an application, you send a DDE request (via the DDERequest method, discussed in the next section) to the System object in the application.

DDEInitiate returns the number of the DDE channel established. You then use this number for subsequent DDE calls.

For example, the following statements declare the Long variable lngDDEChannel1 and assign to it a DDE channel established with the workbook Sales Results.xlsm in Excel:

Dim lngDDEChannel1 As Long
lngDDEChannel1 = DDEInitiate("Excel", "Sales Results.xlsm")

Using DDERequest to Return Text from Another Application

To return a string of text from another application, you use the DDERequest method, which has the following syntax:

expression.DDERequest(Channel, Item)

The components of the syntax are as follows:

  • expression is an optional expression that returns an Application object.
  • Channel is a required Long argument specifying the DDE channel to use for the request.
  • Item is a required String argument specifying the item requested.

To get the list of topics available via DDE, request the Topics item from the System topic. For example, the following statements establish a DDE channel to FrontPage (by using DDEInitiate) and return the list of DDE topics, assigning the list to the String variable strDDETopics:

Dim lngDDE1 As Long
Dim strDDETopics As String
lngDDE1 = DDEInitiate(App:="FrontPage", Topic:="System")
strDDETopics = DDERequest(Channel:=lngDDE1, Item:="Topics")

Open Excel, click the File tab on the Ribbon, and then click the New option. In the search field at the top, type Monthly Family Meal Planner. Click that template to select it, and then click the Create button.

Now open Word's VBA Editor and type in the following procedure. The following statements establish a DDE channel to the workbook SalesReport1.xlsm in Excel and return the contents of cell C7 (R7C3) in the String variable strResult:

Sub DDEtoExcel()
 
Dim lngDDEChannel1 As Long, strResult As String
lngDDEChannel1 = DDEInitiate("Excel", "Monthly family meal planner1")
strResult = DDERequest(lngDDEChannel1, "R11C4")
MsgBox strResult
DDETerminateAll
 
End Sub

Don't save this workbook. Press F5 to test this, and you should see a message box displaying “Beef and Mushroom Skillet Supper,” which sounds pretty nasty.

For DDE to work, you have to use the correct, full name of the target document as it appears in the title bar of the application. In this case, your target document is an Excel workbook named Monthly family meal planner1.

The previous code works only if you haven't yet saved the Monthly family meal planner1 workbook because before it's saved, a new workbook has no filename extension appended to its name. However, if you have already saved this workbook, you must append whatever filename extension you employed, such as .xlsm. Here's an example:

lngDDEChannel1 = DDEInitiate("Excel", "Monthly family meal planner1.xlsx")

The DDETerminateAll statement is explained shortly.

Using DDEPoke to Send Text to Another Application

To send text to another application, use the DDEPoke method, which has the following syntax:

expression.DDEPoke(Channel, Item, Data)

The components of the syntax are as follows:

  • expression is an optional expression that returns an Application object.
  • Channel is a required Long argument specifying the DDE channel to use.
  • Item is a required String argument specifying the item to which to send the data.
  • Data is a required String argument specifying the data to be sent.

Continuing to use the previous example, the following statements use the DDEPoke method to assign the data Potato Salad Surprise to cell R11 C4 in the worksheet:

Sub DDEPokeExcel()
 
Dim lngDDEChannel1 As Long, strResult As String
 
lngDDEChannel1 = DDEInitiate("Excel", "Monthly family meal planner1")
strResult = DDERequest(lngDDEChannel1, "R11C4")
 
 
DDEPoke Channel:=lngDDEChannel1, Item:="R11C4", _
     Data:="Potato Salad Surprise"
DDETerminateAll
 
End Sub

Now look at the Excel worksheet and you'll see that “Beef and Mushroom Skillet Supper” has been replaced with the even more dubious-sounding “Potato Salad Surprise.”

Using DDEExecute to Have One Application Execute a Command in Another

To execute a command in another application, use the DDEExecute method, which has the following syntax:

expression.DDEExecute(Channel, Command)

The components of the syntax are as follows:

  • expression is an optional expression that returns an Application object.
  • Channel is a required Long argument specifying the DDE channel to use.
  • Command is a required String argument specifying the command or series of commands to execute.

For example, the following statements establish a DDE channel to Excel and issue a Close command to close the active workbook:

Sub DDEExec()
 
Dim lngMyChannel
lngMyChannel = DDEInitiate(App:="Excel", Topic:="System")
DDEExecute lngMyChannel, Command:="[Close]"
 
End Sub

If the workbook you're closing has unsaved data, Excel will display a message box prompting you to save it first—thus, preventing it from closing until the prompt is satisfied.

Using DDETerminate to Close a DDE Channel

When you've finished a DDE communication, use the DDETerminate method to close the DDE channel you opened. The syntax for the DDETerminate method is as follows:

expression.DDETerminate(Channel)

Here are the components of the syntax:

  • expression is an optional expression that returns an Application object.
  • Channel is a required Long argument specifying the DDE channel to close.

The following statements employ the previous example, closing the DDE channel that was opened:

Dim lngMyChannel
lngMyChannel = DDEInitiate(App:="Excel", Topic:="System")
DDEExecute lngMyChannel, Command:="[Close]"
DDETerminate lngMyChannel

Using DDETerminateAll to Close All Open DDE Channels

To close all open DDE channels, use the DDETerminateAll method:

DDETerminateAll

Because VBA doesn't automatically close DDE channels when a procedure ends, it's a good idea to use a DDETerminateAll statement to make sure you haven't inadvertently left any DDE channels open.

Communicating via SendKeys

The SendKeys statement is a basic and limited form of communication between applications. You may find SendKeys useful if neither Automation nor DDE works with the target application. But SendKeys does have shortcomings, as you'll see shortly.

SendKeys transmits specified keystrokes to the destination application. It impersonates someone typing at the keyboard.

For example, to use SendKeys to send the command to create a new file in Notepad, you send the keystrokes for Alt+F, N (to execute Notepad's File ➢ New command), and Notepad reacts as if you had pressed the keys manually. In Office 2019 applications, Alt+F opens the File tab on the Ribbon.

SendKeys works only with currently running Windows applications: You can't use SendKeys to start another application running (for that you need to use the Shell command, as discussed earlier in this chapter).

The syntax for the SendKeys statement is as follows:

SendKeys string[, wait]

Here, string is a required String expression specifying the keystrokes to be sent to the destination application. wait is an optional Boolean value specifying whether to wait after sending the keystrokes until the application has executed them (True) or to immediately return control to the procedure sending the keystrokes (False, the default setting). The True setting, however, can prevent some kinds of timing problems.

Typically, string consists of a series of keystrokes (rather than a single keystroke). All alphanumeric characters that appear on the regular keyboard are represented by the characters themselves: To send the letter H, you specify H in the string, and to send the word Hello, you specify Hello in the string. To denote the movement (arrow) and editing keys, SendKeys uses keywords enclosed within braces ({}), as described in Table 29.2.

TABLE 29.2: SendKeys keywords for movement and editing keys

KEY CODE
Down arrow {DOWN}
Left arrow {LEFT}
Right arrow {RIGHT}
Up arrow {UP}
Backspace {BACKSPACE}, {BS}, or {BKSP}
Break {BREAK}
Caps Lock {CAPSLOCK}
Delete {DELETE} or {DEL}
End {END}
Enter {ENTER}
Esc {ESC}
F1, F2, etc. {F1}, {F2}, etc. (up to {F16})
Help {HELP}
Home {HOME}
Insert {INSERT} or {INS}
NumLock {NUMLOCK}
Page Down {PGDN}
Page Up {PGUP}
Print Screen {PRTSC}
Scroll Lock {SCROLLLOCK}
Tab {TAB}

To send Shift, Control, and Alt, use the symbols shown in Table 29.3.

TABLE 29.3: SendKeys symbols for meta keys

KEY CODE
Shift +
Ctrl ^
Alt %

SendKeys automatically assigns the keystroke after the meta key to the meta key, thereby imitating pressing and holding the Alt key—for example, while simultaneously pressing S.

In other words, to send a Ctrl+O keystroke combination, you would specify ^O, and SendKeys imitates holding down Ctrl while pressing O. Then, the next keystroke after the O is considered to be struck separately. If you need to assign multiple keystrokes to the meta key, enter the keystrokes in parentheses after the meta key. For example, to send Alt+F, I, I, you'd write %(FII), not %FII.

As you can see, SendKeys has special uses for the plus sign (+), caret (^), percent sign (%), and parentheses (). The tilde (˜) gets special treatment as well. To use these characters to merely represent themselves instead of their special uses, enter them within braces: {=} sends a regular = sign, {^} a regular caret, {%} a percent sign, {˜} a tilde, and {()} parentheses. Likewise, you must enclose brackets (which have a special meaning in DDE in some applications) within braces; braces themselves also go within braces.

Using SendKeys is much less complex than these details initially make it appear—but with that reassurance, there's one more trick you should know: To repeat a key, enter the key and the number of repetitions in braces. For example, to send five up-arrow keystrokes, you'd specify {UP 5}; to send 10 zeros, you'd specify {0 10}.

Listing 29.4 shows an example of how to use SendKeys to send some text to Notepad after first starting it with the Shell command.

Here's how the code works:

  • The Send_to_Notepad procedure starts by declaring (in lines 2, 3, and 4) three String variables—strLogDate, strSaveLog, and strMsg—and (in line 5) one Variant variable, appNotepad.
  • Line 6 then assigns to strMsg a sample string of text.
  • Line 7 assigns to strLogDate a date built of the Day, Month, and Year values for Now (which returns the current date and time).

    For example, if the date is July 11, 2019, Month(Now) will return 7, Day(Now) will return 11, and Year(Now) will return 2019, so the strLogDate string will contain 7-11-2019.

  • Line 8 then assigns to the strSaveLog string (which will be used to supply the filename for the log file) text describing the file, the strLogDate string, and the .txt filename extension (to continue our example, Log file for 7-11-2019.txt).
  • In line 9, the procedure finally gets down to business, using the Shell statement to run Notepad in a “normal” (not maximized or minimized) window with focus and storing the task ID of the Notepad session in the variable appNotepad.
  • Line 10 then uses an AppActivate statement to activate Notepad.
  • Line 11 uses a SendKeys statement to send to Notepad the following:
    • The information contained in the String variable strMsg.
    • An Alt+F keystroke (to pull down the File menu), followed by an S keystroke to choose the Save item on the menu. This keystroke displays the Save As dialog box with the File Name text box selected.
    • The strSaveLog String variable, which is entered in the File Name text box.
    • An Enter keystroke to choose the Save button in the Save As dialog box.
    • An Alt+F4 keystroke to quit Notepad.
  • Line 12 ends the procedure.

When you run this procedure (again, remember that you need to run the procedure by pressing F5 rather than stepping into it with F8), you'll see the following:

  1. Notepad springs to life.
  2. The contents of the Msg string appear in the Notepad window.
  3. The Save As dialog box displays itself, enters the filename in the File Name text box, and then dismisses itself.
  4. Notepad closes. The .txt file is saved to the currently active folder on your hard drive.

    To locate the currently active folder, open Notepad by hand and choose File ➢ Save As. You might also find that timing problems have changed the name of the file from the intended Log file for 1-13-2019.txt to something like g file for 1-13-2019.txt.

Because SendKeys was historically most often employed to open an application's menus and select an option from the menus (the way that Notepad still behaves), you might think that applications since Windows Vista—which are largely menu-free and employ the Ribbon instead—would seriously curtail the flexibility of the SendKeys technique. However, this isn't true. Many of the features of the Ribbon, for example, are accessible via key combinations. Try pressing the sequence Alt, W, Q, 2, and the Enter key in Word; it will switch to the View tab on the Ribbon, select the Zoom option, and switch to a 200 percent zoom.

The difference here is that instead of employing the traditional approach of simultaneously pressing the Alt key while pressing other keys (such as Alt+V to open a View menu), in current Windows operating systems you press and release Alt by itself and then you press the W key to switch to the View tab on the Ribbon. At this point, additional keystrokes are possible to activate the various options on the View tab. To exit from this mode, press Esc.

Here's another code example, which illustrates how to manipulate Ribbon-based applications. This time Excel, not Notepad, is the target, and the Ribbon, not a menu, is manipulated. The code sends an Alt key by itself (this activates the shortcut key feature on the Ribbon and the Quick Access Toolbar as well, displaying a variety of keys you can choose from). Then the code switches to the View tab (a W does that), and finally full-screen mode is turned on by sending an E:

Sub Send_to_Excel()
 
    Dim appExcel As Variant
 
    appExcel = Shell("Excel.exe", vbNormalFocus)
    AppActivate appExcel
 
        SendKeys "%", True 'send Alt by itself
    SendKeys "W", True 'W for the View tab
    SendKeys "E", True 'E for full screen mode
 
  End Sub

Before pressing F5 to test this code, close Excel.

Going Beyond VBA

VBA is not limited to its own library of functions. In this chapter, you've seen how to use the Editor's Tools ➢ References feature to make Office applications' object libraries available to VBA's built-in capabilities. But wait! There's more.

VBA can also access the entire Windows API (application programming interface). This isn't as simple as adding a library via Tools ➢ References. And the necessary code is verbose. But if you want to have complete control over Windows' internals to, for example, perfectly manage timing issues such as waiting for an outside application to complete its task and other advanced techniques, the Windows API functions are up to such jobs (and plenty more besides).

Windows API programming is beyond the scope of this book, but if you're interested, copy and paste the sample code from this MSDN web page:

https://msdn.microsoft.com/en-us/library/office/bb258148(v=office.12).aspx

That sample code works fine in Word's or Access's VBA Editors. And the links provided on that web page are your doorways into further, deeper study of the topic. If, like me, you have major geek tendencies, it's great fun to wander around and experiment in an immense compendium like the API. You can make Windows do things you wouldn't believe.

The Bottom Line

  • Use Automation to transfer information. Automation sets up communication between two applications, designating one of them as the server and the other as the client.
    • Master It Of the various ways to communicate between applications, which is generally the most effective?
  • Use the Shell function to run an application. Although the Shell function can prove useful in a variety of inter-application communication situations, Shell can also present the programmer with a timing problem.
    • Master It Describe the timing issues that the Shell function raises, and describe a good solution to this problem.
  • Use data objects to store and retrieve information. This book has described a variety of ways to store and retrieve information when working with the VBA language. Using data objects is one of these useful techniques.
    • Master It How is the data-object technology special as a way of storing and retrieving information? What can a data object do that's unique?
  • Communicate via DDE. Dynamic Data Exchange (DDE) is a technology introduced back in May 1990, with Windows 3.0. Use it if other, more efficient communication technologies are unavailable to the applications you are working with.
    • Master It Not all applications support DDE. Which Office 2019 applications don't support DDE communication?
  • Communicate via SendKeys. Using SendKeys is a fairly simple, but rather awkward and limited, way to communicate between applications. It imitates typing in keystrokes, thereby allowing your code to manipulate an application by accessing some of its features using, for example, Alt+key combinations such as Alt+F to open the File tab on the Ribbon.
    • Master It SendKeys was historically most often employed to open an application's menus and select an option from the menus. Since Vista, Windows applications have largely done away with traditional menus, so is SendKeys of even more limited use now than in the past?
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset