A Linked Spreadsheet in Word
Let's take the case of a linked spreadsheet first. The illustration below shows a linked spreadsheet in Word. When you double click the spreadsheet object, the full Excel application opens for editing.
--------
Click Here to display the illustration
Click the Back button on your browser to return
--------
But suppose you just want to change some values in the spreadsheet in the Word VBA macro and you don't want to confuse your users by opening Excel.
Recall that since the spreadsheet is linked, it really isn't part of your Word document. Only the link is stored in Word. So editing it is done in basically the same way that any Visual Basic program would do it. Here's an example macro where the value in three cells is changed. Add your code (and the path to your Excel document) to do whatever you need done. (Formatting on the page might make the statements wrap onto multiple lines. Copy and Paste should get the right code for you.)
Sub Link01()
Dim objXL As Excel.Application
On Error GoTo ErrorHandler
Set objXL = CreateObject("Excel.Application")
objXL.Application.Workbooks.Open ("<path>\AboutVBLinked.xls")
objXL.ActiveSheet.Cells(2, 4).Value = "About"
objXL.ActiveSheet.Cells(2, 5).Value = "Visual"
objXL.ActiveSheet.Cells(2, 6).Value = "Basic"
Selection.Fields.Update
GoTo ExitMacro
ErrorHandler:
MsgBox ("Failed to Open Excel")
ExitMacro:
objXL.Quit
Set objXL = Nothing
End Sub
Before this will run, you also have to add a Reference to the Microsoft Excel 11.0 Object Library. Select References under the Tools menu, scroll down to find it and check the box. The Reference also has to be added for Embedded spreadsheets.
An Embedded Spreadsheet in Word
If a spreadsheet is embedded instead of being linked, everything changes. You can, of course, still edit a spreadsheet the same way, but since the spreadsheet in the Word document is a separate copy, it doesn't do much good. In fact, you'll notice that the menus in Word change completely between linked and embedded worksheets.
The way a worksheet reacts when you double click it changes too. The response when an object is double clicked is called the "primary verb" for the object. Or, in VBA constant terms: wdOLEVerbPrimary. You'll see that in the code below. An in-place Excel window opens for embedded worksheets as shown in the illustration below.
--------
Click Here to display the illustration
Click the Back button on your browser to return
--------
You can update the spreadsheet in a Word VBA macro just as you did with a linked spreadsheet, however. But you need to use entirely different coding. In fact, there are several choices. Here's one that checks for spreadsheets throughout your Word document. When a worksheet is embedded, Word thinks of it as just another in the collection of InlineShapes. Whenever one of these is an embedded OLE object, the primary verb - opening the spreadsheet using an in-place window - is called. Then very similar statements can be used to edit the spreadsheet. the With block associates the ws variable with each statement starting with a dot.
For Each ws In ActiveDocument.InlineShapes
With ws
If .Type = wdInlineShapeEmbeddedOLEObject Then
.OLEFormat.DoVerb wdOLEVerbPrimary
.OLEFormat.Object.ActiveSheet.Cells(2, 4).Value = "About"
.OLEFormat.Object.ActiveSheet.Cells(2, 5).Value = "Visual"
.OLEFormat.Object.ActiveSheet.Cells(2, 6).Value = "Basic"
End If
End With
Next ws
Here's another way it could be done:
Set objOLE = ActiveDocument.InlineShapes(0).OLEFormat
objOLE.Activate
objOLE.Object.ActiveSheet.Cells(2, 4).Value = "About"
objOLE.Object.ActiveSheet.Cells(2, 5).Value = "Visual"
objOLE.Object.ActiveSheet.Cells(2, 6).Value = "Basic"
If you're following along, you might have noticed that the VBA code opens the Excel edit window and makes the changes, but it doesn't close again. Welcome to the quirky world of OLE! There's no direct, foolproof way to do that. You can fall back on a slightly kludgy SendKeys solution, but it sometimes doesn't work.
SendKeys "{ESC}", True
Selection.MoveRight
Hopefully, Visual Studio Tools for Office - the .NET solution - will be better than this!
