Creating Word Document ++ - How? 2

[Schlogg]

I have a situation where we want to generate a quotation in Access and email or fax it to a prospect.
The quotation consists of two sections.
The first section list the (usually one or two) items to be quoted on, includng options. The text for each item is standard and can be kept in the DB. There are some 'standard' items that must be entered manually - freight, delivery time, etc. Also, this section must be locked so the prospect cannot change the prices.
The other pages are PDF brochures giving the specifications for each item quoted.
Is this possible?
After lots of mucking about, we decided that the best bet seemed to be to get a Report (first section) into a Word document and send that. This gives us the opportunity to edit and then lock the document. However, I have no idea how to add the brochure(s).
Also, there are serious restrictions in getting a Report into Word. Is there any way I can generate the Word document from within Access? (The document is, effectively, a thingie generated from a combobox using item numbers to look up the text blocks.)
Any suggestions (especially total code) would be appreciated.


Cheers,
Paul J.

 

[pbail1]

Hi Paul

Creating a Word document is relatively straight forward. The following code creates an adodb recordset, opens a new word document and adds the content of the field "fieldname" into the document.

All you need to do is to ensure that you have word object library and the microsoft activex data objects library in the references (use whatever the latest version of those two library's are in your version of Access)

Sub CreateWordDocument

    Dim wordapp As Word.Application
    Dim STRSQL As String
    Dim Conn as adodb.connection
    Dim RST as adodb.recordset

    Set wordapp = CreateObject("Word.application")

    'Open recordset

    set conn = currentproject.connection
    set RST = new adodb.recordset
    STRSQL = "SELECT * FROM TBLNAME;"

    RSt.open strsql,conn,adOpenForwardOnly ,adLockReadOnly 

    'Produce Document

    wordapp.Visible = True
        
    With wordapp

        .selection.typetext RST!fieldname


    End with
    
    'tidy up
    set rst = nothing
    Set wordapp = nothing

end sub

Adding tables, formating fonts etc and even adding embedded links to your PDF documents can all be done - just need to know how your data is structured and how you want the document to look.

Phil

 

[Schlogg]

Thanks for that Phil. Looks much simpler than 'my' last effort.
The actual quote will look something like this.
LOTS OF HEADER STUFF
Here's your quote.
Item 1. GIZMO1 whirly thingie for going round. $500
Painted add $85
Item 2. GIZMO1a attachments (streamers) each $5
Item 3. DINGL machine for making lots of noise. $750

Freight $200 into your remote town.

Delivery 3-5 days from receipt of order.
LOTS OF ENDING STUFF

The descriptions and prices are in an Access table, keyed by the item code (GIZMO, etc.).
I think I can now see how to get them into the Word doc but am a bit worried about formatting. Attaching the PDFs still looks tricky.



Cheers,
Paul J.

 

[Schlogg]

Forgot to ask, Phil, do you know where I can find this described? There seems to be a dearth of really useful examples of hooking Access to the rest of MS Office. I've searched this forum but there's almost nothing. Can't find anything but info about how good it is on the MS site.

Cheers,
Paul J.

 

[pbail1]

Hi Paul

To be honest I have no idea if there are any decent threads here as I learnt via trial and error. I already knew how to connect to other office products and it took me two minutes to figure out what to do to connect to word and from there on I used the VBA recorder and good old help to learn the various methods and properties Word offers.

If you can knock up an example quote in Word and post it somewhere where I can download it I will knock up the code to produce the document for you - obviously I have no idea of how you structure your data, but I can show you the basics of populating a header, creating a table etc etc.


Phil

 

[payback]

Something probably similar I solved a few years ago worked something like this -

The user created word documents that were stored in a "template" folder. Where data was to be entered, word bookmarks were set.

In Access, the user selected a template, and the data to be copied into Word (names, addresses, standard paragraphs - any number of that had been set up, etc.)

The code then -
1) Created a copy of the "template" under a new name decided by the user (but this could be a sequential or other reference generated by Access)
2) Wrote that document name into a field in an Access table so it could be referenced by customer
3) Opened the document then using the Word bookmarks, automatically amended the Word document using the data from the Access database
4) Allowed the user to then further amend the document as they wanted
5) Saved and printed the document

This should probably be the path you should take as it was quite flexible subject to "some" restrictions.

I probably could find the code again in a few days if necessary.

 

[Schlogg]

Phil and Payback,
Thank you both for your kind offers. Sorry to be so long getting back - been up to my ears.
Our requirements have changed a bit and it sounds like that code of your would be ideal, Payback. Any chance of finding it?
Thanks in advance.

Cheers,
Paul J.

 

[payback]

Give me a few days to hunt it out and create a version without actual client data. Will try and get done this weekend.

 

[payback]

This is the code I created for sending data directly from Access into a word document without using the Merge.

The purpose of this was to allow the Access application to not only have customer billing and payment transactions tracked but also customer correspondence.

I have not included all the form code etc. but you should be able to adapt this to open a word document and feed it field data as required. The word document only receives that which is already calculated, therefore any calculations you need (e.g. totals) would have to be handled in your VBA code.

Function Create_Word_Documents()
On Error GoTo Errorhandler

    Dim dbs As Database
    Dim rst As Recordset
    
    Dim strCriteria As String
    Dim strSearchPath As String
    Dim strSavePath As String
    Dim strDocumentName As String
    Dim strDescription As String
    Dim strNewDescription As String
    Dim strFileName As String
    
    Dim intX As Integer
    
    Dim msg, style, resp
    
    Dim objWord As Object
    Dim objAccess As Object
    
    'Letter Templates are already created for the user to select
    'The templates have bookmarks created to receive data from the database
    strSearchPath = DLookup("LettersPath", "Control_Parameters")
    If IsNull(strSearchPath) Then
        strSearchPath = "C:\Access Databases\My Letter Path\Word Documents"
    End If
    
    strSavePath = DLookup("CustomersLettersPath", "Control_Parameters")
    If IsNull(strSavePath) Then
        strSavePath = "C:\Access Databases\My Letter Path\Customer Letters"
    End If
    
    strDocumentName = OpenWordDocument(strSearchPath)
    
    If IsNull(strDocumentName) Or strDocumentName = "" Then
        msg = "No document has been selected!"
        style = vbOKOnly + vbInformation
        MsgBox msg, style
        Exit Function
    End If
    
    'A simple routine to extract the document name without the path
    For intX = Len(strDocumentName) To 1 Step -1
        If Mid(strDocumentName, intX, 1) = "\" Then
            strDescription = Mid(strDocumentName, intX + 1, 99)
            GoTo End_Name:
        End If
    Next intX
    
End_Name:

    'Can be driven from a number of forms and data
    If Not IsLoaded("frmCustomerLettersExistingAbstracts") Then
        DoCmd.OpenForm "frmCustomerLettersSelect", , , , , acDialog
    End If
    
    DoCmd.OpenForm "frmCustomerLetters", , , , acFormAdd, acDialog
    DoCmd.Close acForm, "frmCustomerLettersSelect"
        
    If Not IsLoaded("frmCustomerLetters") Then
        Exit Function
    End If
    
    ' In this case you could also include paragraphs stored in the database and insert them in the document
    'msg = "Do you want to include any Standard Paragraphs ?"
    'style = vbYesNo + vbQuestion + vbDefaultButton2
    'resp = MsgBox(msg, style)
    
    'If resp = vbYes Then
    '    DoCmd.OpenForm "frmSelectParagraphs", , , , , acDialog
    'End If
    
    'Because this is linked to the database the user could create their own description
    strNewDescription = Mid(strDescription, Len(strDescription) - 3, 4)
    If strNewDescription = ".doc" Then
        strDescription = Mid(strDescription, 1, Len(strDescription) - 4)
    End If
    
    strNewDescription = InputBox("Enter a new Description for this document", , strDescription)
    If strNewDescription = "" Then
        strNewDescription = strDescription
    End If
    
    'The new description is written back to the form and subsequently the database
    'A user could then get all letters written to a customer and view them in word as saved
    If IsLoaded("frmCustomerLetters") Then
        [Forms]![frmCustomerLetters]![Description] = strNewDescription
    End If
    
    strFileName = strSavePath & "\" & [Forms]![frmCustomerLetters]![LetterUniqueNo] & ".doc"
    [Forms]![frmCustomerLetters]![WordDocument] = strFileName
    
    'Takes a copy of the template so it is not overwritten - all changes are to the copy
    FileCopy strDocumentName, strFileName

    'Calls the subroutine below to open word    
    WordDocuments (strFileName)
    
    Do
        Err = 0
        Set objWord = GetObject(, "Word.Application")
    Loop While Err <> 0

    'loads the bookmarks with the data                
    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
        
        If .Bookmarks.Exists("CustomerName") = True Then
            .Bookmarks("CustomerName").Select
            If Not IsNull([Forms]![frmCustomerLetters]!CustomerName) Then
                objWord.Selection.Text = (CStr([Forms]![frmCustomerLetters]!CustomerName))
            Else
                objWord.Selection.Text = ""
            End If
        End If
        
        If .Bookmarks.Exists("AddressLine1") = True Then
            .Bookmarks("AddressLine1").Select
            If Not IsNull([Forms]![frmCustomerLetters]!PostalAddress1) Then
                objWord.Selection.Text = (CStr([Forms]![frmCustomerLetters]!PostalAddress1))
            Else
                objWord.Selection.Text = ""
            End If
        End If
        
        If .Bookmarks.Exists("AddressLine2") = True Then
            .Bookmarks("AddressLine2").Select
            If Not IsNull([Forms]![frmCustomerLetters]!PostalAddress2) Then
                objWord.Selection.Text = (CStr([Forms]![frmCustomerLetters]!PostalAddress2))
            Else
                objWord.Selection.Text = ""
            End If
        End If
        
        If .Bookmarks.Exists("AddressLine3") = True Then
            .Bookmarks("AddressLine3").Select
            If Not IsNull([Forms]![frmCustomerLetters]!PostalAddress3) Then
                objWord.Selection.Text = (CStr([Forms]![frmCustomerLetters]!PostalAddress3))
            Else
                objWord.Selection.Text = ""
            End If
        End If
        
        If .Bookmarks.Exists("AddressLine4") = True Then
            .Bookmarks("AddressLine4").Select
            If Not IsNull([Forms]![frmCustomerLetters]!PostalAddress4) Then
                objWord.Selection.Text = (CStr([Forms]![frmCustomerLetters]!PostalAddress4))
            Else
                objWord.Selection.Text = ""
            End If
        End If
        
        If .Bookmarks.Exists("AuthorName") = True Then
            .Bookmarks("AuthorName").Select
            If Not IsNull([Forms]![frmCustomerLetters]!AbstractAuthor) Then
                objWord.Selection.Text = (CStr("Attention: " & [Forms]![frmCustomerLetters]!AbstractAuthor))
            Else
                objWord.Selection.Text = ""
            End If
        End If
        
        If .Bookmarks.Exists("MatterNo") = True Then
            .Bookmarks("MatterNo").Select
            If Not IsNull([Forms]![frmCustomerLetters]!AbstractClient) Then
                'objWord.Selection.Text = (CStr("RE: " & [Forms]![frmCustomerLetters]!AbstractClient))
                objWord.Selection.Text = (CStr("RE: " & UCase([Forms]![frmCustomerLetters]!AbstractClient)))
            Else
                objWord.Selection.Text = ""
            End If
        End If
        
        If .Bookmarks.Exists("OurRef") = True Then
            .Bookmarks("OurRef").Select
            If Not IsNull([Forms]![frmCustomerLetters]!AbstractNo) Then
                objWord.Selection.Text = (CStr([Forms]![frmCustomerLetters]!AbstractNo))
            Else
                objWord.Selection.Text = ""
            End If
        End If
        
        If .Bookmarks.Exists("StaffName1") = True Then
            .Bookmarks("StaffName1").Select
            If Not IsNull([Forms]![frmCustomerLetters]!StaffName) Then
                objWord.Selection.Text = (CStr([Forms]![frmCustomerLetters]!StaffName))
            Else
                objWord.Selection.Text = ""
            End If
        End If
        
        If .Bookmarks.Exists("StaffName2") = True Then
            .Bookmarks("StaffName2").Select
            'If Not IsNull([Forms]![frmCustomerLetters]!StaffName) Then
            '    objWord.Selection.Text = (CStr([Forms]![frmCustomerLetters]!StaffName))
            'Else
            '    objWord.Selection.Text = ""
            'End If
            If Not IsNull([Forms]![frmCustomerLetters]!StaffDetails) Then
                objWord.Selection.Text = (CStr([Forms]![frmCustomerLetters]!StaffDetails))
            Else
                objWord.Selection.Text = ""
            End If
        End If
        
        If .Bookmarks.Exists("ExpiryDate") = True Then
            .Bookmarks("ExpiryDate").Select
            If Not IsNull([Forms]![frmCustomerLetters]!ExpiryDate) Then
                objWord.Selection.Text = (CStr([Forms]![frmCustomerLetters]!ExpiryDate))
            Else
                objWord.Selection.Text = ""
            End If
        End If
                    
        If .Bookmarks.Exists("ParagraphText") = True Then
            If IsLoaded("frmSelectParagraphs") Then
                Set dbs = CurrentDb
        
                strCriteria = "SELECT Word_Document_Paragraphs.ParagraphText " _
                    & "FROM tmpParagraphSelect LEFT JOIN Word_Document_Paragraphs ON tmpParagraphSelect.ParagraphNo " _
                    & "= Word_Document_Paragraphs.UniqueNo " _
                    & "WHERE tmpParagraphSelect.SelectParagraph = True " _
                    & "ORDER BY tmpParagraphSelect.SortSequence;"
            
                Set rst = dbs.OpenRecordset(strCriteria, dbOpenSnapshot)
        
                objWord.ActiveDocument.Bookmarks("ParagraphText").Select
                objWord.Selection.Text = ""

                Do Until rst.EOF
                    objWord.Selection.InsertAfter rst!ParagraphText
                    objWord.Selection.InsertAfter vbCrLf
                    objWord.Selection.InsertAfter vbCrLf
                    rst.MoveNext
                Loop

                DoCmd.Close acForm, "frmSelectParagraphs"

                rst.Close
                Set dbs = Nothing
            Else
                objWord.ActiveDocument.Bookmarks("ParagraphText").Select
                objWord.Selection.Text = ""
            End If
        End If
    End With
        
    'The document is left open for further modification if necessary and must be saved and closed as a usual document is.
    'Note that word must be closed to have Access as the active object on completion.
    objWord.ActiveWindow.WindowState = wdWindowStateMaximize

    DoCmd.Close acForm, "frmCustomerLetters", acSaveYes
    
    Exit Function
    
Errorhandler:
    Call Error_Display(Err)
        
End Function

Sub WordDocuments(strDocumentName)
On Error GoTo Errorhandler

    Dim objWord As Object
    
    On Error Resume Next
    
    Set objWord = GetObject(strDocumentName)
    
    If Error Then
        Set objWord = CreateObject("Word.Application")
        Set objWord = GetObject(strDocumentName)
    End If
    
    objWord.Application.Visible = True
    
    Exit Sub
    
Errorhandler:
    Call Error_Display(Err)
    
End Sub

Originally written in Access 97 and then upgraded to Access 2000. You might have to fiddle a bit to get it correct for you.