Use Word bookmark in Excel spreadsheet

[xlhelpme]

I have an open Word template containing multiple bookmarks, I would like to add 2 specific bookmarks to an Excel spreadsheet when the spreadsheet is opened, the bookmarks names are “Text6” and “Text9”, the spreadsheet is launched from a button in the word template, with the following code:-
Sub OpenSheet()
ActiveDocument.FollowHyperlink _
Address:="C:\Common\Structure\Project\Working Area\Spreadsheet 2.0.xls", _
NewWindow:=True, AddHistory:=True
End Sub

I am using Word & Excel 2003, any suggestions would be appreciated as all the examples I can find open a pre-saved word document before copying bookmarks, will I have to save the Word doc to a temp location prior to locating and copying the bookmarks??

Thanks in anticipation.

 

[Worf]

Hi
See if this example is useful for you:

Option Explicit
Dim xlApp As Excel.Application, xlWB As Excel.Workbook
Private Sub CommandButton1_Click()
Set xlApp = CreateObject("Excel.application")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open(ThisDocument.Path & "\Spreadsheet1.xls")
With xlWB.Worksheets(1)
    .Cells(6, 2).Value = ActiveDocument.Bookmarks("Text6").Range.Text
    .Cells(9, 2).Value = ActiveDocument.Bookmarks("Text9").Range.Text
End With
MsgBox "Excel file will now be closed..."
xlWB.Close          ' prompts to save changes
xlApp.Quit           ' close Excel instance
Set xlWB = Nothing
Set xlApp = Nothing
End Sub