Send Record to Word Document

[shenty]

OK - bit of a newbie here & maybe this question has been asked a thousand times but i can't find any answers that cover this question properly.

Basically i am writing an access database that stores contract information, contact details, orders etc etc. I want to take the Order Number field, along with the Client Name & Address etc and post it into a Word Mail Merge type document, then save that file as ordernumber.doc after checking to see whether it already exists.

I have successfully managed to send the information into word in a mail merge document & get it to save the file using the ordernumber.doc format, however it is saving it to mydocuments folder and i want it in a subfolder called orders (that does exist). It is also not checking to see if that file already exist and overwrites it if it does.

I want to change the path to where the file is being saved, check whether it exists already, then ask me whether i want to overwrite it or save as something else.

Any help would be most appreciated. BTW I am using Word & Access 2003.

*****THIS IS THE CODE I AM USING*****
Private Sub NewEternit_Click()
On Error GoTo NewEternit_Err

Dim objWord As Object

'Start Microsoft Word.
Set objWord = CreateObject("Word.Application")

With objWord
'Make the application visible.
.Visible = True

'Open the document.
.Documents.Add ("\\shent\c\Documents and Settings\John.SHENT\My Documents\Templates\Eternit Order Merge.dot")

'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("orderno").Select
.Selection.Text = (CStr(Forms!frmOrderDetails!ContractNo) & "/" & (Forms!frmOrderDetails!OrderNo))
.ActiveDocument.Bookmarks("Date").Select
.Selection.Text = (CStr(Forms!frmOrderDetails!Date))

If Me.chkDeliverYard = False Then

.ActiveDocument.Bookmarks("ClientName").Select
.Selection.Text = (CStr(Forms!frmOrderDetails!ClientName))
.ActiveDocument.Bookmarks("SiteReference").Select
.Selection.Text = (CStr(Forms!frmOrderDetails!SiteReference))
.ActiveDocument.Bookmarks("SiteAddress1").Select
.Selection.Text = (CStr(Forms!frmOrderDetails!SiteAddress1))
.ActiveDocument.Bookmarks("SiteAddress2").Select
.Selection.Text = (CStr(Forms!frmOrderDetails!SiteAddress2))
.ActiveDocument.Bookmarks("SiteAddress3").Select
.Selection.Text = (CStr(Forms!frmOrderDetails!SiteAddress3))
.ActiveDocument.Bookmarks("Town").Select
.Selection.Text = (CStr(Forms!frmOrderDetails!Town))
.ActiveDocument.Bookmarks("City").Select
.Selection.Text = (CStr(Forms!frmOrderDetails!City))
.ActiveDocument.Bookmarks("Postcode").Select
.Selection.Text = (CStr(Forms!frmOrderDetails!Postcode))

Else

.ActiveDocument.Bookmarks("ClientName").Select
.Selection.Text = "OUR YARD"
.ActiveDocument.Bookmarks("SiteReference").Select
.Selection.Text = ""
.ActiveDocument.Bookmarks("SiteAddress1").Select
.Selection.Text = ""
.ActiveDocument.Bookmarks("SiteAddress2").Select
.Selection.Text = ""
.ActiveDocument.Bookmarks("SiteAddress3").Select
.Selection.Text = ""
.ActiveDocument.Bookmarks("Town").Select
.Selection.Text = ""
.ActiveDocument.Bookmarks("City").Select
.Selection.Text = ""
.ActiveDocument.Bookmarks("Postcode").Select
.Selection.Text = ""

End If

'Print the document in the foreground so Microsoft Word will not close
'until the document finishes printing.
'objWord.ActiveDocument.PrintOut Background:=False

' Save the file using orderno field
Dim FName As String
FName = Forms!frmOrderDetails!OrderNo & ".doc"

'Quit Microsoft Word and release the object variable.
'objWord.Quit
'Set objWord = Nothing

End With


Exit Sub
**** END OF CODE****

 

[The_Doc_Man]

Look into the help files regarding an objWord.ActiveDocument.SaveAs method. I don't see where you actually do the save of the merged file. I see where you create the name, but you don't appear to use it.

 

[shenty]

Yeh, i worked out how to set the filename to save to - i did forget i'd pasted the code before i added the line to actually save the file.

But what i need to know is the code to check if it exists before saving and if it is then ask me to do a saveas.

Thanks for your reply.

 

[Moniker]

This will check for a valid path. You can easily modify it to check for a name instead of a path.

Function VerifyPath(CheckPath As String) As Boolean

    Dim fso As New FileSystemObject
    Dim fldrCheck As Folders
    
    On Error GoTo Verify_Error
    
    VerifyPath = True
    Set fldrCheck = fso.GetFolder(CheckPath)
    Set fldrCheck = Nothing
        
Verify_Error:
    Select Case Err.Number
        Case 13
            Resume Next
        Case 76
            VerifyPath = False
    End Select
    
End Function

 

[shenty]

Thanks for all ya help guys - between you i've managed to cobble something up that works.

For those interested the code is below.

Thanks Again

*******************************
Private Sub NewEternit_Click()
On Error GoTo NewEternit_Err

Dim objWord As Object
Dim FName As String
Dim SName As String

'Check whether order exists
FName = "\\Shent\c\Documents and Settings\John.SHENT\My Documents\Orders\" & Forms!frmOrderDetails!OrderNo & ".doc"

'If it does then message box
If Len(Dir(FName)) > 0 Then
MsgBox "Order already exists! Please use the 'Open Order in Word' icon to modify."

'If it doesn't then process

Else
'MsgBox "Ready to create new order, have you checked Delivery Address & Colour ?", vbOKOnly, "Create Order"

'set the template name to use
SName = "\\shent\c\Documents and Settings\John.SHENT\My Documents\Templates\" & Forms!frmOrderDetails!SentTo & " Order Merge.dot"

'Start Microsoft Word.
Set objWord = CreateObject("Word.Application")

With objWord
'Make the application visible.
.Visible = True

'Open the document template
.Documents.Add SName

'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("orderno").Select
.Selection.Text = (CStr(Forms!frmOrderDetails!ContractNo) & "/" & (Forms!frmOrderDetails!OrderNo))
.ActiveDocument.Bookmarks("Date").Select
.Selection.Text = (CStr(Forms!frmOrderDetails!Date))
.ActiveDocument.Bookmarks("SentTo").Select
.Selection.Text = (CStr(Forms!frmOrderDetails!SentTo))
.ActiveDocument.Bookmarks("ClientName").Select
.Selection.Text = (CStr(Forms!frmOrderDetails!ClientName))
.ActiveDocument.Bookmarks("SiteReference").Select
.Selection.Text = (CStr(Forms!frmOrderDetails!SiteReference))
.ActiveDocument.Bookmarks("SiteAddress1").Select
.Selection.Text = (CStr(Forms!frmOrderDetails!SiteAddress1))
.ActiveDocument.Bookmarks("SiteAddress2").Select
.Selection.Text = (CStr(Forms!frmOrderDetails!SiteAddress2))
.ActiveDocument.Bookmarks("SiteAddress3").Select
.Selection.Text = (CStr(Forms!frmOrderDetails!SiteAddress3))
.ActiveDocument.Bookmarks("Town").Select
.Selection.Text = (CStr(Forms!frmOrderDetails!Town))
.ActiveDocument.Bookmarks("City").Select
.Selection.Text = (CStr(Forms!frmOrderDetails!City))
.ActiveDocument.Bookmarks("Postcode").Select
.Selection.Text = (CStr(Forms!frmOrderDetails!Postcode))

'Save the document
objWord.ActiveDocument.SaveAs FileName:=FName

Set objWord = Nothing

End With
End If

Exit Sub

NewEternit_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
Else
MsgBox Err.Number & vbCr & Err.Description
End If

Exit Sub

End Sub