Solved: Replace Existing Bookmark Text from Access

[JustJerry]

I have an Access database that I run some code to fill in Bookmarks in a Word Document. My code runs fine to create a new document, and open an existing document as long as no changes were made in Access.

My trouble starts when I try to do the EDIT process to replace the text already in the Word Document at specific Bookmarks. For now, I am only using one bookmark until I get this to work, that bookmark is called "custname".

In word, it is like this -------------> Attention: [ <-------"custname", so when the document is created the first time, it would end up like this:

Attention: [Joe Smith. **Bookmark is Not deleted, and I don't want it to be.

If the user changes the contact name while In Access, and then opens up the document, I want to be able to replace JOE SMITH with the new contact name. Here is my code thus far:

[vba]
Private Sub RunWordTemplate_Click()
On Error GoTo Err_RunWordTemplate_Click
Dim oApp As Object 'Variable for Word
Dim sFilename As String 'Variable for Auto-Save file name
Dim strTemplateName As String 'Variable for Word Template to be used
Dim objWORDdoc As Object

'Save Record
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

'Create path to Quotation Template
strTemplateName = "\\Sharppc\serverfiles\ProjectData\Quotations\QuotationTemp.dot"
'Create default SaveName for New Quotation being written
sFilename = "\\Sharppc\serverfiles\ProjectData\Quotations\" & Me.WONum.Value & " - " & Me.CustomerID.Value & ".doc"

Set oApp = CreateObject("Word.Application")
oApp.Visible = True

If Dir(sFilename) = "" Then 'Test to see if created filename already exists
'and if not, open Template to fill in date
Set oApp = CreateObject("word.basic") 'otherwise just open that filename already
With oApp

.filenew Template:=strTemplateName
'Set bookmarks in QuotationTemp to equal values of new Quoation Number created
.EditBookmark Name:="quotedate", GoTo:=True
.Insert (Format(Me.WODate, "mmmm dd, yyyy")) 'insert date of quote
.EditBookmark Name:="quotenum", GoTo:=True
.Insert (CStr(Me.WONum)) 'insert work order number
.EditBookmark Name:="companyname", GoTo:=True
.Insert (CStr(Me.CompanyName)) 'insert company name

'This code will make sure that if the street address has two lines, both are inserted
If IsNull(DLookup("[CustAdd2]", "ContactsTbl", "[Contact]='" & _
Me.Contact.Value & "'")) Or DLookup("[CustAdd2]", "ContactsTbl", "[Contact]='" & _
Me.Contact.Value & "'") = 0 Then
.EditBookmark Name:="address", GoTo:=True 'insert Line 1 address
.Insert ((CStr(DLookup("[CustAdd1]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))))
Else
.EditBookmark Name:="address", GoTo:=True 'insert Line 1 & 2 address
.Insert (CStr(DLookup("[CustAdd1]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))) & _
vbCrLf & (CStr(DLookup("[CustAdd2]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'")))
End If

.EditBookmark Name:="citystate", GoTo:=True
.Insert ((CStr(DLookup("[CustCity]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))) & _
", " & (CStr(DLookup("[StateID]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))) & _
" " & (CStr(DLookup("[CustZip]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))))
.EditBookmark Name:="custname", GoTo:=True
.Insert (CStr(Me.Contact))
.EditBookmark Name:="subject", GoTo:=True
.Insert (CStr(Me.WODesc))
.EditBookmark Name:="firstname", GoTo:=True
.Insert (CStr(DLookup("[fName]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'")))

.filesaveas Name:=sFilename 'save Quotation with auto save name

End With

Else

If Me.lblChangeWarn.Visible = True Then

Set oApp = CreateObject("word.basic")
oApp.FileOpen sFilename
oApp.EditBookmark "custname", GoTo:=True
oApp.Selection.Delete Unit:=wdCharacter, Count:=1
oApp.InsertAfter (CStr(Me.Contact))
oApp.Bookmarks.Add Range:=Selection.Range, Name:="custname"
Else
oApp.Documents.Open sFilename
End If

End If
Exit_RunWordTemplate_Click:
Exit Sub
Err_RunWordTemplate_Click:
MsgBox Err.Description
Resume Exit_RunWordTemplate_Click

End Sub[/vba]

This is the part I am trying to figure out:

[vba] If Me.lblChangeWarn.Visible = True Then

Set oApp = CreateObject("word.basic")
oApp.FileOpen sFilename
oApp.EditBookmark "custname", Goto:=True
oApp.Selection.Delete Unit:=wdCharacter, Count:=1
oApp.InsertAfter (CStr(Me.Contact))
oApp.Bookmarks.Add Range:=Selection.Range, Name:="custname"
Else
oApp.Documents.Open sFilename
End If[/vba]

I am unsure how to make this work so that the Code select's "Joe Smith" and replaces it with whatever the new name is. I have other bookmarks to do this to as well, but can basically use the same code for the other few bookmarks I'll need to update.

I have read alot of things on the MSDN sites, and through help, but can not figure it out.

Any help is MUCH appreciated!!

Edit by Lucas: Line breaks added to code....Jerry...some in the word forum have small monitors and this causes them some grief....please use line breaks when posting code.

[lucas]

Here is an example from an entry by Steiner that puts the text in the bookmark but does not replace it so you can call it again and change the text at the same bookmark:
[vba]Option Explicit
Public Sub WB(ByVal BName As String, ByVal inhalt As String)
If ActiveDocument.Bookmarks.Exists(BName) Then
Dim r As Range
Set r = ActiveDocument.Bookmarks(BName).Range
r.Text = inhalt
ActiveDocument.Bookmarks.Add BName, r
Else
Debug.Print "Bookmark not found: " & BName
End If
End Sub
Sub a()
WB "bm_Test", "Hello World"
End Sub
[/vba]

Call the sub WB from the sub a and change the text after you run it once..


Edit: link to Steiners kb entry: http://vbaexpress.com/kb/getarticle.php?kb_id=126

[JustJerry]

Thanks Lucas, I appreciate the input. However, I was trying to run the code from Access instead of in Word itself. Not sure if it matters or not, but my Text in Word is inserted AFTER the bookmark and not part of the bookmark itself.

I done it that way to try and avoid the people in the office from mistakenly deleting the bookmarks should they delete the Text itself.

[lucas]

You might want to post this in the access forum for more specialized help....give them a link to this thread so you don't have people in both places working on it and not knowing what progress is made in the other forum.

[JustJerry]

Ok, will do

[Ken Puls]

Humour me here... why are you editing a document based on a template? Why not just toss it and start over fresh with the updated data from Access?

[JustJerry]

Hey Ken,

Well, the 'orignal' document is created using the Template, then saved using an auto-save filename specific to the Work Order and Company as a Document. I am trying to make this a bit simpler for the two who are going to use this program. You can go bald just even explaining things to certain people, if you know what I mean.

If they, for whatever reason, create an original document, and they type it all up, I have no way of recreating what they've typed. What I fill in from Access is merely a starting point for the whole document, they will need to type in much more than is in Access.

In the occurance that after they made their original document, and then in Access they changed the 'Contact' name, I was just trying to update that information without causing too much grief.

This way, whenever Work Orders are looked at in Access, they can pull up the Word Document associated with it at any given time. All from one location, all from one button.

Make sense?

[Ken Puls]

Sure, it does, although I haven't really looked at your code in depth.

Personally, I'd probably look at adapting Steiner's script to work. At least that way you've got the text at a place that you can easily update it. By putting the text after the bookmark you'll have to try and figure out what the previous text was, which could be a pain.

I can see maybe just using a Document_Open event in word to requery the database for each updated bookmark or something... (i.e. using Word to pull, rather than Access to push in this case.)

[fumei]

If the text is not IN the bookmark, then really, the bookmark is not all that relevant. If the text is after the bookmark, then the question is:

How do I replace the text after a bookmark?

It could be done, but it would be easier to use the bookmark text itself. Yes, if the user deletes the text the bookmark will go.

To do it with text after, you will need to determine the logic. Will it always be two words? Charles Whoha. Could it be three words? Charles K. Whoha.

And there's the rub. You can certainly go to any bookmark and get the following text...but what is the logic of it?

[JustJerry]

I merely thought it was a simple process I wasn't completely understanding how to make happen. I'll have to just figure out a different process and go from there. Thanks.

[Ken Puls]

Thanks, Gerry. That's what I was thinking as well, although I didn't take the time to really spell it out like you did.

[fumei]

Thanks Ken.

JustJerry - it CAN be simple. You can use the text of the bookmark itself, in which case a version of Steiner's code will do the trick. Comment: cerating and using a Range object is not technically required to make it work.

OR, you can do with the text after, as that is what you have got. IT CAN be done, and it CAN be simple...as long as the logic is simple.

And the logic is: by what crieria do I replace the text?

The next two words after the bookmark?
The next three words?
The next word?
Do I include any space that may be before the first word?
Do I trim off any space that may be before the first word?

All of these come down to: WHAT do I replace?

Code is never stupid (although some Object Models may seem that way). It does, and ONLY does, what you tell it to do.

Word is not going to know that the previous text was Joe Smith. You must tell Word what to do.

See the line under Ken's name.