edit & update linked excel charts & spreadsheets

[shinymcshire]

I'm tried recording a macro that selects all of the linked objects (excel charts and spreadsheets) on a Word doc, edits them, force recalculates the entire source excel workbook (I have all of the linked objects in one workbook), then update all of the links in Word.

I kept the vba project window up while I recorded, and I noticed that when I clicked on "Edit Link", that nothing happened (in addition to pressing Ctrl-Alt-F9 to force recalculate the entire excel workbook).

My thought is that this is one of those instances where a macro recorder will not do, and while I do have an excel vba book from John Walkenbach, I do not have one for Word, so I have half of the pieces to the puzzle.

My initial thought is that I'll need to write a sub that calls other subs (one that selects all of the linked charts and spreadsheets on the word doc, then next a sub from excel that will recalculate the entire workbook, then updating the links.

I would appreciate any feedback that you would be able to provide, as I am a vba "greenhorn". Thanks!:dunno

[shinymcshire]

Update:

Okay, so what I really need to get to know is about Automation. Based on what I have read so far, this is the vba code I have come up with in the Word VBA module:

Sub EditUpdate()

Dim ExcelApp As Object
Set ExcelApp = GetObject("v:\FY 08-10 Budget Schedules\Budget Schedules Workbook.xls")
Selection.WholeStory
ExcelApp.CalculateFull
Selection.Fields.Update


End Sub

The problem is that I get a "Run Time Error 438 - Object doesn't support this property or method." It is referring to the CalculateFull command. Any ideas?

[shinymcshire]

So thanks to the word.mvps website, I have the vba code for recalcing the source workbook. Now all I need help with is

1. Making Word the active window.

2. Selecting all of the linked objects and updating them.

Here is the updated code:

Sub WorkOnAWorkbook()
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
Dim ExcelWasNotRunning As Boolean
Dim WorkbookToWorkOn As String
'specify the workbook to work on
WorkbookToWorkOn = "v:\FY 08-10 Budget Schedules\Budget Schedules Workbook.xls"
'If Excel is running, get a handle on it; otherwise start a new instance of Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")
If Err Then
ExcelWasNotRunning = True
Set oXL = New Excel.Application
End If
On Error GoTo Err_Handler
'If you want Excel to be visible, you could add the line: oXL.Visible = True here; but your code will run faster if you don't make it visible
oXL.Visible = True
'Open the workbook
Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)

oXL.CalculateFull

If ExcelWasNotRunning Then
oXL.Quit
End If
'Make sure you release object references.
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing
'quit
Exit Sub
Err_Handler:
MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description, vbCritical, _
"Error: " & Err.Number
If ExcelWasNotRunning Then
oXL.Quit
End If

Word.Application.ActiveDocument
Selection.WholeStory
Selection.Fields.Update

End Sub

I get a "Compile error: Invalid Use of Property" in regards to the "Word.Application.ActiveDocument" code.

Again, any help you can provide is appreciated. Thank you!