Hi,
I'm a newbie, so excuse my ignorance... It was very difficult for me to get here! I write mainframe assembler... OO VBA is just a little different!
The objective is to use a VBA macro in Excel to kick off a Word document, and to change some words (placeholders) in this document to values that come from the spreadsheet.
The included code only attempts to make 1 change, and it dies with an error 449 from VBA. Here is the code:
[VBA]
Private Sub WordTest()
Dim rcMsg As Integer
Dim tCell As Long
Dim wrdApp As Word.Application
On Error GoTo errHandler
rcMsg = MsgBox("WordTest 2.3 has been entered")
Set wrdApp = New Word.Application
' wrdApp.Visible = True
rcMsg = MsgBox("WordTestBefore Open")
wrdApp.Documents.Open Filename:="c:\chuck\Recap.doc"
rcMsg = MsgBox("WordTest Before Selection.Find.Clear...!")
wrdApp.Selection.Find.ClearFormatting
rcMsg = MsgBox("WordTest Before FIND.Repl!")
wrdApp.Selection.Find.Replacement.ClearFormatting
rcMsg = MsgBox("WordTest Before WITH!")
With wrdApp.Selection.Find
.Text = "AAAAAQ01"
.Replacement.Text = "Charleston Square"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
rcMsg = MsgBox("WordTest Before Selection.Find.Execute!")
wrdApp.Selection.Find.Execute
rcMsg = MsgBox("WordTest Before With wrdApp.Selection!")
With wrdApp.Selection
If .Find.Forward = True Then
.Collapse Direction:=wdCollapseStart
Else
.Collapse Direction:=wdCollapseEnd
End If
.Find.Execute Replace:=wdReplaceOne
If .Find.Forward = True Then
.Collapse Direction:=wdCollapseEnd
Else
.Collapse Direction:=wdCollapseStart
End If
.Find.Execute
End With
rcMsg = MsgBox("WordTest... Before Prt!")
wrdApp.Application.PrintOut Filename:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
Collate:=True, Background:=True, PrintToFile:=False, PrintZoomColumn:=0, _
PrintZoomRow:=0, PrintZoomPaperWidth:=0, PrintZoomPaperHeight:=0
rcMsg = MsgBox("Wordtest- Before Quit")
wrdApp.Quit savechanges:=False
rcMsg = MsgBox("Wordtst- Before wrdApp Nothing")
Set wrdApp = Nothing
tCell = Cells(1, 1)
rcMsg = MsgBox(tCell) 'Another test
rcMsg = MsgBox("WordTest is ending.")
GoTo endIt
errHandler:
rcMsg = MsgBox("The error handler was entered!")
wrdApp.Quit savechanges:=False
Set wrdApp = Nothing
rcMsg = MsgBox("The error handler issued Quit!")
endIt:
End Sub
[/VBA]
Edit Lucas: VBA tags added to code
-------------------------------------------
The program dies at:
wrdApp.Selection.Find.Replacement.ClearFormatting
The message "WordTest Before FIND.Repl!" is the last message displayed before going into the error handler.
The word VBA came from recording a macro under Word to do the first change (Change "AAAAAQ01" to "Charleston Square"). Then I copied this to my XL macro and added the wrdApp to the object methods. This was the result of another problem, and I think this means the methods could be Word or XL and "wrdApp" makes the decision of which method to use. I may be misusing "method" as my OO terminology is not very good.
It just seems to me that this has to be something pretty simple. If I remove all the "change" code, Word happily prints my document.
The long names "AAAAAQ01" were chosen to make the spacing easier to "guess" on the Word document. Once I get this to work, I'll probably set this to just "Q01". I tried to use "bookmarks" in ".dot" file to no avail in Word. Perhaps my approach could be improved. The vast majority of the 15 changes to be made to the document is numbers that XL is calculating.
Only one change is made in this example.
TIA