Using Excel VBA to control Word

Stephen

Wonderful to hear from you. Essentially I am tasked with creating a Marking Model in Excel 2003. The Model simply records the marks awarded by the user to 5 suppliers who have submitted information as Word documents. The user wants to be able to toggle between suppliers' submissions at will, hence the sub routine is replicated below.(Only two included below). The macros are triggered by Form Control Buttons. The marks are recorded and analysed in Excel. Having got the user to Word I bring him back using (AppActivate "Microsoft Excel"). When it works it is fantastic but for some reason even though it picks up the correct file (MyDoc) and finds the correct page it takes the user to previously viewed Word Window. All 5 are open all the time. I adopted your early binding idea I think !. Any help much appreciated.

Rgds

Trevor


Option Explicit

Dim WordApp As Word.Application
Dim wrdDoc As Object
Dim WDoc As String
Dim myDoc As String
Dim Target As Long
Dim selector As Integer
Dim MyNum As Integer
Dim sup As String

Sub Test1()

'supplier 1 searcher

' indicate supplier in use
selector = Range("docsel")
Range("b38") = Range("Sup1")
sup = Range("Sup1")

' pick up word doc name
MyNum = WorksheetFunction.VLookup(sup, Range("SupMaster"), 2, False)
myDoc = WorksheetFunction.VLookup(selector, Range("master"), MyNum, False)

' concatonate into file path
WDoc = "C:\Users\Trevor\Desktop\Police\MarkMaster" & "\" & myDoc & ".doc"

' get page number from lookup function in excel
Target = Range("pagesel1")
' error trap starts
On Error Resume Next
'establish Word application as an object
Set WordApp = GetObject(, "Word.Application")
' continue error trap
If WordApp Is Nothing Then
' no current word application
' open word if it's closed

Set WordApp = CreateObject("Word.application")
Set wrdDoc = WordApp.Documents.Open(WDoc)
WordApp.Visible = True
WordApp.Activate

Else

' if word is already running and the document is open

Documents(myDoc).Activate
WordApp.Selection.Goto What:=wdGoToPage, Count:=Target
WordApp.Visible = True
WordApp.Activate
Application.WindowState = wdWindowStateMaximize


If wrdDoc Is Nothing Then

' if not open - open it

Set wrdDoc = WordApp.Documents.Open(WDoc)
WordApp.Visible = True
WordApp.Activate

End If
End If


End Sub


Sub Test2()

'supplier 2 searcher

' indicate supplier in use
selector = Range("docsel")
Range("b38") = Range("Sup2")
sup = Range("Sup2")

' pick up word doc name
MyNum = WorksheetFunction.VLookup(sup, Range("SupMaster"), 2, False)
myDoc = WorksheetFunction.VLookup(selector, Range("master"), MyNum, False)

' concatonate into file path
WDoc = "C:\Users\Trevor\Desktop\Police\MarkMaster" & "\" & myDoc & ".doc"

' get page number from lookup function in excel
Target = Range("pagesel2")
' error trap starts
On Error Resume Next
'establish Word application as an object
Set WordApp = GetObject(, "Word.Application")
' continue error trap
If WordApp Is Nothing Then
' no current word application
' open word if it's closed

Set WordApp = CreateObject("Word.application")
Set wrdDoc = WordApp.Documents.Open(WDoc)
WordApp.Visible = True
WordApp.Activate

Else

' if word is already running and the document is open

Documents(myDoc).Activate
WordApp.Selection.Goto What:=wdGoToPage, Count:=Target
WordApp.Visible = True
WordApp.Activate
Application.WindowState = wdWindowStateMaximize


If wrdDoc Is Nothing Then

' if not open - open it

Set wrdDoc = WordApp.Documents.Open(WDoc)
WordApp.Visible = True
WordApp.Activate

End If
End If


End Sub