Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Using Excel VBA to control Word
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Using Excel VBA to control Word
Resolved · Medium Priority · Version 2003
Reply Fri 17 Sep 2010, 15:51Delegate Trevor said...
Trevor has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Access Intermediate course
Excel VBA Advanced course
Using Excel VBA to control Word
Please help !
The following code works fine until the last two lines below. I do not understand Word VBA well enough to change the selection. Having got to the page specified by the range "Target" (which works fine) I need to copy the text at the top of the page. The page is comprised of a table with three columns. I need the text at the top of the third column.
Dim WordApp As Object
Set WordApp = GetObject(, "Word.Application")
Target = Range("page")
Documents("masterdoc").Activate
WordApp.Selection.Goto What:=wdGoToPage, Count:=Target
WordApp.Visible = True
WordApp.Activate
Selection.MoveDown Unit:=wdLine, Count:=1
Selection.MoveRight Unit:=wdCharacter, Count:=6
Thanks and regards
Trevor
For upcoming training course dates see: Pricing & availability
RE: Using Excel VBA to control Word
Hi Trevor
Thanks for your question
Can you give me an idea what it is you are trying to achieve?
One thing I notice is that you are using "late binding", that is not referencing the word object model. You might find that referencing the object model and using early binding will help as you will have access to intellisense and the word object model.
If you can explain what you are trying to do, i will attempt this approach to solve your problem
Thanks
Stephen
Reply Thu 23 Sep 2010, 09:52Delegate Trevor said...
RE: 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
|