hi
I've got this code working just the way i want (with help from some clever guys on here). There is one tiny issue however, whenever i run it and it completes successfully; i'm unable to repeat it without first having to close the DB. Can anyone see anything i should add (or take away)? If not, can i build a close app function onto the end of it? (Not ideal but would prevent my colleaugues becoming frustrated with it.
This is the code i'm using:
Private Sub InitialLetter_Click()
On Error GoTo MergeButton_Err
Dim objWord As word.Application
'Start Microsoft Word.
Set objWord = CreateObject("Word.Application")
With objWord
'Make the application visible.
.Visible = True
objWord.Activate
'Open the document.
.Documents.Open ("C:\Users\OR\Desktop\Initial Document.doc")
'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("Titlea").Select
.Selection.Text = (CStr(Forms!DCCVFORM!TITLE))
.ActiveDocument.Bookmarks("FirstName").Select
.Selection.Text = (CStr(Forms!DCCVFORM!FirstName))
.ActiveDocument.Bookmarks("SurNamea").Select
.Selection.Text = (CStr(Forms!DCCVFORM!SurName))
.ActiveDocument.Bookmarks("ADD1").Select
.Selection.Text = (CStr(Forms!DCCVFORM!ADDRESS1))
.ActiveDocument.Bookmarks("TOWN").Select
.Selection.Text = (CStr(Forms!DCCVFORM!ADDRESSTOWN))
.ActiveDocument.Bookmarks("COUNTY").Select
.Selection.Text = (CStr(Forms!DCCVFORM!ADDRESSCOUNTY))
.ActiveDocument.Bookmarks("POSTCODE").Select
.Selection.Text = (CStr(Forms!DCCVFORM!POSTCODE))
.ActiveDocument.Bookmarks("DCCVUN").Select
.Selection.Text = (CStr(Forms!DCCVFORM!DCCVUN))
.ActiveDocument.Bookmarks.Add Name:="DCCVUN", Range:=Selection.Range
.ActiveDocument.Bookmarks("DCCVCASENUMBER").Select
.Selection.Text = (CStr(Forms!DCCVFORM!DCCVCASENUMBER))
.ActiveDocument.Bookmarks.Add Name:="DCCVCASENUMBER", Range:=Selection.Range
.ActiveDocument.Bookmarks("UN").Select
.Selection.Text = (CStr(Forms!DCCVFORM!UN))
.ActiveDocument.Bookmarks("NHSNO").Select
.Selection.Text = (CStr(Forms!DCCVFORM!NHSNo))
.ActiveDocument.Bookmarks("DOB").Select
.Selection.Text = (CStr(Forms!DCCVFORM!DOB))
.ActiveDocument.Bookmarks("TITLE").Select
.Selection.Text = (CStr(Forms!DCCVFORM!TITLE))
.ActiveDocument.Bookmarks("SURNAME").Select
.Selection.Text = (CStr(Forms!DCCVFORM!SurName))
.ActiveDocument.Bookmarks("GP").Select
.Selection.Text = (CStr(Forms!DCCVFORM!GP))
.ActiveDocument.Bookmarks("GPFIRSTLINE").Select
.Selection.Text = (CStr(Forms!DCCVFORM!GPFIRSTLINE))
.ActiveDocument.Bookmarks("GPSECONDLINE").Select
.Selection.Text = (CStr(Forms!DCCVFORM!GPSECONDLINE))
.ActiveDocument.Bookmarks("GPTOWN").Select
.Selection.Text = (CStr(Forms!DCCVFORM!GPTOWN))
.ActiveDocument.Bookmarks("GPCOUNTY").Select
.Selection.Text = (CStr(Forms!DCCVFORM!GPCOUNTY))
.ActiveDocument.Bookmarks("GPPOSTCODE").Select
.Selection.Text = (CStr(Forms!DCCVFORM!GPPOSTCODE))
Dim pStr As String
pStr = "C:\Users\OR\Desktop\"
pStr = pStr + ActiveDocument.Bookmarks("DCCVUN").Range.Text
pStr = pStr + "-"
pStr = pStr + ActiveDocument.Bookmarks("DCCVCASENUMBER").Range.Text
pStr = pStr + "-Initial Letter"
pStr = pStr + "-"
pStr = pStr & Format(Now(), "ddmmyy-hhnnss")
ActiveDocument.SaveAs FileName:=pStr
MergeButton_Err:
'If a field on the form is empty, remove the bookmark text, and
'continue.
If Err.Number = 94 Then
objWord.Selection.Text = ""
Resume Next
End If
Exit Sub
End With
End Sub
Thanks in advance
I've got this code working just the way i want (with help from some clever guys on here). There is one tiny issue however, whenever i run it and it completes successfully; i'm unable to repeat it without first having to close the DB. Can anyone see anything i should add (or take away)? If not, can i build a close app function onto the end of it? (Not ideal but would prevent my colleaugues becoming frustrated with it.
This is the code i'm using:
Private Sub InitialLetter_Click()
On Error GoTo MergeButton_Err
Dim objWord As word.Application
'Start Microsoft Word.
Set objWord = CreateObject("Word.Application")
With objWord
'Make the application visible.
.Visible = True
objWord.Activate
'Open the document.
.Documents.Open ("C:\Users\OR\Desktop\Initial Document.doc")
'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("Titlea").Select
.Selection.Text = (CStr(Forms!DCCVFORM!TITLE))
.ActiveDocument.Bookmarks("FirstName").Select
.Selection.Text = (CStr(Forms!DCCVFORM!FirstName))
.ActiveDocument.Bookmarks("SurNamea").Select
.Selection.Text = (CStr(Forms!DCCVFORM!SurName))
.ActiveDocument.Bookmarks("ADD1").Select
.Selection.Text = (CStr(Forms!DCCVFORM!ADDRESS1))
.ActiveDocument.Bookmarks("TOWN").Select
.Selection.Text = (CStr(Forms!DCCVFORM!ADDRESSTOWN))
.ActiveDocument.Bookmarks("COUNTY").Select
.Selection.Text = (CStr(Forms!DCCVFORM!ADDRESSCOUNTY))
.ActiveDocument.Bookmarks("POSTCODE").Select
.Selection.Text = (CStr(Forms!DCCVFORM!POSTCODE))
.ActiveDocument.Bookmarks("DCCVUN").Select
.Selection.Text = (CStr(Forms!DCCVFORM!DCCVUN))
.ActiveDocument.Bookmarks.Add Name:="DCCVUN", Range:=Selection.Range
.ActiveDocument.Bookmarks("DCCVCASENUMBER").Select
.Selection.Text = (CStr(Forms!DCCVFORM!DCCVCASENUMBER))
.ActiveDocument.Bookmarks.Add Name:="DCCVCASENUMBER", Range:=Selection.Range
.ActiveDocument.Bookmarks("UN").Select
.Selection.Text = (CStr(Forms!DCCVFORM!UN))
.ActiveDocument.Bookmarks("NHSNO").Select
.Selection.Text = (CStr(Forms!DCCVFORM!NHSNo))
.ActiveDocument.Bookmarks("DOB").Select
.Selection.Text = (CStr(Forms!DCCVFORM!DOB))
.ActiveDocument.Bookmarks("TITLE").Select
.Selection.Text = (CStr(Forms!DCCVFORM!TITLE))
.ActiveDocument.Bookmarks("SURNAME").Select
.Selection.Text = (CStr(Forms!DCCVFORM!SurName))
.ActiveDocument.Bookmarks("GP").Select
.Selection.Text = (CStr(Forms!DCCVFORM!GP))
.ActiveDocument.Bookmarks("GPFIRSTLINE").Select
.Selection.Text = (CStr(Forms!DCCVFORM!GPFIRSTLINE))
.ActiveDocument.Bookmarks("GPSECONDLINE").Select
.Selection.Text = (CStr(Forms!DCCVFORM!GPSECONDLINE))
.ActiveDocument.Bookmarks("GPTOWN").Select
.Selection.Text = (CStr(Forms!DCCVFORM!GPTOWN))
.ActiveDocument.Bookmarks("GPCOUNTY").Select
.Selection.Text = (CStr(Forms!DCCVFORM!GPCOUNTY))
.ActiveDocument.Bookmarks("GPPOSTCODE").Select
.Selection.Text = (CStr(Forms!DCCVFORM!GPPOSTCODE))
Dim pStr As String
pStr = "C:\Users\OR\Desktop\"
pStr = pStr + ActiveDocument.Bookmarks("DCCVUN").Range.Text
pStr = pStr + "-"
pStr = pStr + ActiveDocument.Bookmarks("DCCVCASENUMBER").Range.Text
pStr = pStr + "-Initial Letter"
pStr = pStr + "-"
pStr = pStr & Format(Now(), "ddmmyy-hhnnss")
ActiveDocument.SaveAs FileName:=pStr
MergeButton_Err:
'If a field on the form is empty, remove the bookmark text, and
'continue.
If Err.Number = 94 Then
objWord.Selection.Text = ""
Resume Next
End If
Exit Sub
End With
End Sub
Thanks in advance