Opening & Editing an Excel file from within Word

[vodkasoda]

When I am editing a Word Document (2007) I can run the following Macro & it will read data from a specific Excel file that I have updated & automatically update my Word document as required.

This is assuming that I already have the Excel document open & have made my changes.

I then realised that it would be easier if my program actually opened that Excel Document for me & activated the first of the 3 cells that I need to use, then I can enter the 3 fields & continue.

How do I do that, how do I open the Excel file for editing automatically ?

Sub MOTM()

Dim myPath As String
Dim myFullFile As String
Dim myFileName As String
Dim mySession As String
Dim myExcelFile As String
Dim myWorkBook As Excel.Workbook
Dim myWorksheet As Worksheet
Dim myMOTM1 As String
Dim myMOTM2 As String
Dim myMOTM3 As String

myPath = ActiveDocument.FullName
myFullFile = ActiveDocument.Name
myFileName = Split(ActiveDocument.Name, ".")(0)
mySession = Right(myFileName, 2)

If IsNumeric(mySession) Then
'do nothing
Else
mySession = Right(mySession, 1)
End If

myPath = Left(myPath, (Len(myPath)) - (Len(myFullFile)))
myExcelFile = myPath & "Manager Of The Month.xlsx"

Set myWorkBook = GetObject(myExcelFile)
Set myWorksheet = myWorkBook.ActiveSheet

'myWorkBook.Open <<< This is where started to make this change but it throws an error

myWorksheet.Cells(mySession + 1, 2).Activate

myMOTM1 = myWorksheet.Cells(mySession + 1, 2).Value
myMOTM2 = myWorksheet.Cells(mySession + 1, 3).Value
myMOTM3 = myWorksheet.Cells(mySession + 1, 4).Value

myWorkBook.Close

For Ix = 1 To 3
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "D" & Ix & "MOTM"
.Replacement.Text = myMOTM & Ix
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
End With
Next Ix

End Sub