Excel to word bookmark problem 1

[davefish]

I have some code that I wrote in Excel 2000 that opens a word document and places data from a specifc cell to a bookmark in word.

I need to use that code but with office 2003 and I can't get it to work. It fails when trying to Goto the bookmark no matter what I try. I recorded a macro in word and used this in Excel with the ActiveDocument pre-fix, but to no avail. Here's my code: -
Private Sub CommandButton1_Click()
Const wdWindowStateMaximize As Integer = 1
Const wdNormalView As Integer = 3
Const wdAlignParagraphCenter As Integer = 1
Const wdPageFitFullPage As Integer = 1
Const wdGoToLine As Integer = 3

Dim WordDoc As Object
Set WordApp = CreateObject("Word.Application")
With WordApp
.Visible = True
.WindowState = wdWindowStateMaximize
WordApp.Documents.Open Filename:="C:\Documents and Settings\dfishkin\Desktop\Test quotes\Dave.doc"
Set WordDoc = .Activedocument
End With



WordDoc.ActiveWindow.View = wdNormalView
WordDoc.ActiveWindow.Selection.GoTo What:=wdGoToBookmark, Name:="Dave"

'Stops here with an application defined or object defined error

Selection.Find.ClearFormatting
With Selection.Find
.Text = ""
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With

End Sub

Any help appreciated

 

[fumei]

Change:

WordDoc.ActiveWindow.Selection.GoTo What:=wdGoToBookmark, Name:="Dave"

to:

WordDoc.Selection.GoTo What:=wdGoToBookmark, Name:="Dave"

Gerry

 

[PHV]

And add this:
Const wdGoToBookmark = -1

Tip: use the Option Explicit instruction.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fumei]

Can't stress enough PHV's comment re: Option Explicit

Gerry

 

[davefish]

Hi Guy's sorry for not getting back earlier. I tried changing the code and adding the Constant + Option Explicit, but now the code hangs on the Set WordApp = CreateObject("Word.Application") line and states that the variable is not defined?? If I get rid of option explicit then it creates the word document but fails again on the goto command. Have I done something wrong?

DaveFish

Option Explicit
Private Sub CommandButton1_Click()
Const wdWindowStateMaximize As Integer = 1
Const wdNormalView As Integer = 3
Const wdAlignParagraphCenter As Integer = 1
Const wdPageFitFullPage As Integer = 1
Const wdGoToLine As Integer = 3
Const wdGoToBookmark = -1


Dim WordDoc As Object
Set WordApp = CreateObject("Word.Application")
With WordApp
.Visible = True
.WindowState = wdWindowStateMaximize
WordApp.Documents.Open Filename:="C:\Documents and Settings\dfishkin\Desktop\Test quotes\Dave.doc"
Set WordDoc = .Activedocument
End With

WordDoc.ActiveWindow.View = wdNormalView
WordDoc.Selection.GoTo What:=wdGoToBookmark, Name:="Dave"
Selection.Find.ClearFormatting
With Selection.Find
.Text = ""
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With

End Sub

 

[PHV]

You may try the following corrections:
Option Explicit
Private Sub CommandButton1_Click()
Const wdWindowStateMaximize As Integer = 1
Const wdNormalView As Integer = 3
Const wdAlignParagraphCenter As Integer = 1
Const wdPageFitFullPage As Integer = 1
Const wdGoToLine As Integer = 3
Const wdGoToBookmark = -1
[!]Const wdFindContinue = 1[/!]

Dim [!]WordApp As Object,[/!] WordDoc As Object
Set WordApp = CreateObject("Word.Application")
With WordApp
.Visible = True
.WindowState = wdWindowStateMaximize
.Documents.Open FileName:="C:\Documents and Settings\dfishkin\Desktop\Test quotes\Dave.doc"
Set WordDoc = .ActiveDocument
End With
WordDoc.ActiveWindow.View = wdNormalView
WordDoc.Selection.GoTo What:=wdGoToBookmark, Name:="Dave"
[!]WordDoc.[/!]Selection.Find.ClearFormatting
With [!]WordDoc.[/!]Selection.Find
.Text = ""
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fumei]

Option Explicit, but now the code hangs on the Set WordApp = CreateObject("Word.Application") line and states that the variable is not defined??

That is PRECISELY the point of using Option Explicit. It tells you very interesting things like - "variable not declared". WordApp was not declared as a variable.....so trying to Set it for something = error...variable not declared. Option Explicit requires all variables to be declared.

In the long run, this is rather handy. It also is VERY handy finding syntax errors, and spelling errors....because they cause errors too. For example, say you are using a variable file_path to store...uh, path to a files, but you accidentally type it once as files_path (with an added "s"):

file[COLOR=red]s[/color red]_path = "C:\blahblah\"
....other stuff...
Documents.Open Filename:=file_path & "that.doc"

This would be NO error, and you would have to search for it. If the error location is WAY back there, or even in another module, this can be a pain to find. On the other hand:

Option Explicit
Dim file_name As String
file[COLOR=red]s[/color red]_path = "C:\blahblah\"
....other stuff...
Documents.Open Filename:=file_path & "that.doc"

WOULD generate an error when the VBA parser, uh, parses the code. It would highlight the offending variable, not declared.

Gerry

 

[davefish]

Thanks for that. I still have a problem though. The variable defined issue is now sorted, but the code still hangs on (WordDoc.Selection.GoTo What:=wdGoToBookmark, Name:="Dave") with an error stating the "Object doesn't support the property or method" However, when I copy this code minus the wordDoc. into Word VBA it works? So what am I missing here?

Regadrs

DaveFish

 

[PHV]

Sorry for the typos:
Word[!]App[/!].Selection.GoTo What:=wdGoToBookmark, Name:="Dave"
Word[!]App[/!].Selection.Find.ClearFormatting
With Word[!]App[/!].Selection.Find

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Codman]

Thanks PH that sorted the issue.

Regards

DaveFish