JustJerry
05-02-2007, 02:22 PM
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:
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 (file://\\Sharppc\serverfiles\ProjectData\Quotations\QuotationTemp.dot)"
'Create default SaveName for New Quotation being written
sFilename = "\\Sharppc\serverfiles\ProjectData\Quotations\ (file://\\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
This is the part I am trying to figure out:
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
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!!:banghead:
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.
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:
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 (file://\\Sharppc\serverfiles\ProjectData\Quotations\QuotationTemp.dot)"
'Create default SaveName for New Quotation being written
sFilename = "\\Sharppc\serverfiles\ProjectData\Quotations\ (file://\\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
This is the part I am trying to figure out:
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
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!!:banghead:
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.