April 14, 2011, 2:21 PM — One of the handiest things you can do with Visual Basic for Applications (VBA) in Office 2011 is to share data between two different Office apps. For example, let's say you want to embed data from Excel in a sales report you're writing in Word. VBA is the tool you'll want to use to make that happen.
More specifically, in this example VBA could automatically: identify the current month based on some text in the Word document; find the sales data for that month within the Excel spreadsheet; and paste that data into your Word document. Even if you don't need to reproduce this specific project, the scripts it uses are great examples of what VBA can do without a lot of work or expertise on your part.
Find a word in Word
VBA has several tools for finding text strings in Word documents. In this example, suppose the first line in your sales reports will always be Sales Report for June or Sales Report for July--the fourth word of the report is always the name of the month. Here are two lines of VBA code that will extract that fourth word:
Dim Month As String
Month = ActiveDocument.Words(4).Text
The first line creates a string variable named Month. The second line finds the fourth word in the document and assigns its value to that variable. If the first line in your document is Sales Report for June, then the string stored in the Month variable will be June.
Fetch the data
The next thing to do is get the data from Excel, based on the month string in the Word document. VBA's Select Case statement is perfect for this; it lets you say, in effect, "In case the month is June, do this. In case it's July, do that." The basic structure would look like this:
Select Case Month
Case Is ="June"
Go get June data from Excel
Case Is ="July"
Go get July data from Excel
End Select
There's one potential gotcha here: If the word you're seeking is in the middle of a sentence and so has a following space, you need to include that space in the Case Is string.
Now you need to identify and access the Excel workbook that has your data. That takes only one line:
Set xlSheet = GetObject("Macintosh_ HD:Users:chris:Documents:Monthly Sales Report.xlsx")
The text inside the quotes inside the parentheses is the full path to the file. The GetObject() method doesn't care whether the Excel sheet is open or closed; however, if it's closed when you run this script and it contains macros, you'll get a dialog box asking you if you want to enable them. You can choose to either enable or disable those macros, and your macro will still do its stuff.
Home in on the Range
Now you need to specify which part of the Excel sheet you want to use. Let's say the data you want inside Monthly Sales Report.xlsx is in a worksheet (i.e. tab) named Sales and it looks like this:
The best way to succinctly identify a range of cells in Excel is to give that range a name. For example, you could select all four cells in the Feb column and then type the name Feb in Excel's name box, at the far left of the formula bar. (The name must have no spaces in it.)
Doing this for each month in the Monthly Sales Report worksheet gives you an easy way to identify and copy the data for the report; you can access the named range for a statement like this:
xlSheet.Worksheet("sales").Range("Jan")
Moving from left to right in this statement: xlSheet creates an object that points to the Excel document, Worksheet("sales") refers to the sales tab in the Excel workbook and Range("Jan") refers to the named range for January.
The Range object has a handy method--Copy--that you can tack on to the end of that reference: xlSheet.Worksheets("sales").Range("Jan").Copy. The method copies your data to the clipboard so you can paste it into the Word report. There are certainly other ways to move data from one document to another using VBA, but Copy and Paste may be the easiest.
To do that pasting in Word, you can use the Selection object: it refers to selected text in the document or, if nothing is selected, to the insertion point. That means you can paste the contents of the clipboard into your report at the insertion point with Selection.Paste. The Paste method pastes the Excel cells into your Word report as cells in a table. If you'd only copied the contents of a single cell, it would paste that into your document as text.
Put it all together
When you combine those individual lines, the subroutine looks like this:
Sub GetMonthlySalesData()
Dim Month As String
Month = ActiveDocument.Words(4).Text
