Access Form To Word Template - Change Bookmarks(?) 1

[marcus101]

Hello:

I've been using an Access form with a textbox to open a Word template. The idea was to have the user type in a value into the text box and then have that value replace the text at a pre-existing bookmark already set in the template file ahead of time...

Here is a code sample. At the moment, I can get the Word document template file to open, but when I get to selecting the bookmark it doesn't recognize my code as a valid command/function.

Dim sigVal as String
Dim tempDoc as String

sigVal = Me.theSig.Value
tempDoc = "C:\Templates\temp1.dot"

Dim objWordDoc As Object
Set objWordDoc = GetObject(tempDoc, "Word.Document")
    
    'Dim WordApp As Word.Application
    'Dim WordDoc As Word.Document
    
    'Set WordApp = CreateObject("Word.Application")
    'Set WordDoc = WordApp.Documents.Open(tempDoc)
    
    objWordDoc.Application.Visible = True
 
    ' Code to jump to Bookmark Selection then replace?

    If Not IsNull(sigVal) Then

    ' NOTE: This line below is where the code fails... 

    objWordDoc.Selection.GoTo wdGoToBookmark, , , "AgentSig"
    ' objWordDoc.Selection.Find.ClearFormatting ' needed?

    ' TEST: Is this entire section needed?

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

    objWordDoc.Selection.TypeText Text:=sigVal
    objWordDoc.Save

    End If

    objWordDoc.Close SaveChanges:=-1 '-1 = wdSaveChanges
    
    Set objWordDoc = Nothing

Any ideas/suggestions most appreciated...

Thanks,

marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[CautionMP]

marcus101,
A couple of things.[ul]
[li]The [tt]Selection[/tt] object is slow, just refer to your bookmark directly using the collection.[/li]
[li]This will return a [tt]Range[/tt] object that you can set the [tt]Text[/tt] for.[/li][/ul]

If Nz(Me.theSig.Value,"") <> "" Then
  WordDoc.Bookmarks("AgentSig").Range.Text = Me.theSig.Value
End if

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[payback]

Have a look at thread705-1249866 - this works.

 

[marcus101]

Hi Guys:

The other thread doesn't really give me more than I already have, merely just a few instructions on how to set up a Word doc using DIM declarations, which I've already tried in principle.

When I have a bit more time, I'll try it again in more detail, but I still have to get the bookmark change to work, so that is my priority.

With this in mind I will try out the change suggested by CautionMP and let you know how this works, this seems like the best option for now.

Thanks again for your help.


marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[marcus101]

Sorry guys, no luck.

I get an error #5941 when I reference this object this way.

It has nothing to do with NZ. I've already inserted code that ensures that the value is not null when it is passed.

The problem resides in the line:

WordDoc.Bookmarks("AgentSig").Range.Text = value

which I have changed to:

objWordDoc.Bookmarks("AgentSig").Range.Text = value

I'll have to see about going back to the drawing board and doing this from scratch using a more conventional Word as Application reference.

Unless anyone can figure out why I'm getting an error #5941, and can adjust accordingly, of course..

Thanks,

marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[payback]

Search in the Microsoft Knowledgebase for -

+error +5941 +bookmark

There are some results that could help you identify the issue.

Also maybe not related but I select my bookmark fields differently i.e.

   With objWord.ActiveDocument
    
        If .Bookmarks.Exists("LetterDate") = True Then
            .Bookmarks("LetterDate").Select
            If Not IsNull([Forms]![frmCustomerLetters]!LetterDate) Then
                objWord.Selection.Text = (CStr(Format([Forms]![frmCustomerLetters]!LetterDate, "Long Date")))
            Else
                objWord.Selection.Text = ""
            End If
        End If

that is -
1. Check to see if the bookmark exists
2. If so, select it
3. Assign the value to the .text object

 

[marcus101]

Here's the latest code I've been trying, still trying to puzzle out some variations, but I think I'm getting pretty close.

I'm pretty sure there's something wrong with the way I'm calling Word.Application and Word.Document. I never said I was a Word VBA expert, I'm just trying to get the blamed thing working, and let's face it, changing a bookmark shouldn't be THIS hard...

Word Opens, so that's something. "AgentSig" is already defined as a bookmark. The form is set up to get a non-null value to pass into the Word document and CHANGE the bookmark..

But changing the bookmark itself just doesn't work..yet.

Here's the current revised code:

Dim tempdoc as String
tempDoc = "C:\Templates\template1.doc"

Dim WordDoc As Object
Dim sigVal As String
            
sigVal = Me.SigNameVal.Value ' from form..
    
Set WordDoc = CreateObject("Word.Application")
    
WordDoc.Documents.Open (tempDoc)
       
If Not WordDoc.Application.Visible Then
    
     WordDoc.Application.Visible = True
    
End If
     
WordDoc.Bookmarks("AgentSig").Range.Text = sigVal

' the ABOVE line raises an error #438 = object does NOT
' support this property or method...grrrr...
     
WordDoc.Save

Any suggestions welcome.

Thanks,

marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[PHV]

Set WordDoc = CreateObject("Word.Application")
WordDoc.Documents.Open (tempDoc)
WordDoc.Visible = True
WordDoc.ActiveDocument.Bookmarks("AgentSig").Range.Text = sigVal
WordDoc.ActiveDocument.Save

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

 

[payback]

Look at my previous post. I note you are using ".range" but my working code does not.

I also "select" the bookmark first.

 

[PHV]

payback, the main issue is that WordDoc is a Word.Application object, not a Word.Document ...

 

[marcus101]

PHV:

Works beautifully. Thanks a lot.

Now tweaking to add CRs and other nifty stuff....

marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[lespaul]

maybe the Form Fields result property instead? I use this all the time, granted from Delphi, but it's using the VBA/COM interface:

Word.ActiveDocument.FormFields.Item("AgentSig").Result:= sigValue

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[marcus101]

Well, so much for this topic being finished..my bad..

While the CHANGE to the single bookmark technically WORKS, it doesn't WORK as EXPECTED, which was to say, JUST CHANGE the AgentSig bookmark, NOT REMOVE IT, as I plan to re-use this bookmark constantly..

lespaul had a great idea that might have worked, which I tried, but I get an error message not recognizing this "property or collection.", because the Word Doc is not a form, I guess..

So now I am stuck again, this time vainly trying to regenerate a bookmark that frankly, shouldn't have been deleted in the first place - again, I don't know the Word model and I keep wishing it wasn't so picky.

And perhaps because I don't have really "good" access to the Word object model using the rather simple object call I have in place, maybe I can't do what I need to do as quickly as I would like to.

Just to illustrate: I've also been trying to just simply minimize the Word window, and again, I've been hamstrung on that too..

Suggestions, (any and all) welcome..I will even rewrite this procedure using a NEW approach if I have to, just please let me know what approach is best to use for the most complete access to the Word object model...

Here is what I've got so far, and what I've added to that:

' Change bookmark text..
    WordDoc.ActiveDocument.Bookmarks("AgentSig").Range.Text = sigVal

    ' WordDoc.ActiveDocument.FormFields.Item("AgentSig").Result = sigVal2 - lespaul's idea, no luck..
    
'Attempt to Regenerate DELETED bookmark...sigh..
WordDoc.ActiveDocument.Selection.Find.ClearFormatting

' NOTE: This FIND command does NOT work using the current code..in fact, neither of these two lines work...and if I can't at least get a find/replace bookmark thing going, well, the whole thing is useless..
    
With WordDoc.ActiveDocument.Selection.Find
   .Text = sigVal2
   .Replacement.Text = ""
   .Forward = True
   '.Wrap = wdFindContinue - error - does not recognize this
   ' parameter, so commented out..
   .Format = False
   .MatchCase = False
   .MatchWholeWord = False
   .MatchWildcards = False
   .MatchSoundsLike = False
   .MatchAllWordForms = False
End With
    
    WordDoc.ActiveDocument.Selection.Find.Execute
    
    With WordDoc.ActiveDocument.Bookmarks
       .Add Range:=WordDoc.ActiveDocument.Selection.Range, Name:="AgentSig"
       .DefaultSorting = wdSortByName
       .ShowHidden = False
    End With
        
    ' Save current document..
    WordDoc.ActiveDocument.Save
    ' WordDoc.Exit
    
    ' Testing: Way to make Access visible via minimizing Word window??
    WordDoc.WindowState = WordDoc.WindowState.wdWindowStateMinimize
    ' WordDoc.Visible = False

Many thanks for any suggestions in advance.


marcus101
Access/SQL/XML Developer
Ottawa, Canada