Populating Ms Word from Access - code no longer working in 2013 - HELP!

[Moss100]

Hello I have used the code below for years in Access 2003 (with Word 2003)

When attempting to use it in Office 2013, it causes Access to crash.

Could someone please help me to modify the code so I can continue using it.

Basically it populates the open word document (which has previously been saved with the Access field names inserted between slashes).

It works well and makes available all fields from the query bound to the Access form.

For example I would create a word document with the text: Dear Mr ///FullName/// When the document is opened, the code looks for the field names between the slashes and would produce Dear Mr John Smith.

Many thanks for any assistance. Regards Mark

Public Function CreateWordDocument(ByVal strWordFilename As String, ByVal frmCurrentForm As Form)
    
    Dim appWordApp As WORD.Application
    Dim appWordDoc As WORD.Document
    Dim appWordStory As WORD.Range
    Dim strFieldName As String
    
    If Dir(strWordFilename) <> "" And strWordFilename <> "" Then
        
        'setup word application
        Set appWordApp = New WORD.Application
        Set appWordDoc = appWordApp.Documents.Open(strWordFilename)
        
        'setup recordset and get current record from form
        Dim rsCurrentForm As DAO.Recordset
        Set rsCurrentForm = frmCurrentForm.Recordset
        rsCurrentForm.Bookmark = frmCurrentForm.Bookmark
        
            'replace in word , each story range
            For Each appWordStory In appWordDoc.StoryRanges
                    
                With appWordStory.Find
                    .ClearFormatting
                    .Forward = True
                    .MatchCase = False
                    .MatchWildcards = True
                    .Wrap = wdFindContinue
                                                            
                    Do
                    
                        .Text = "///*///"
                        .Execute
                        
                        If .Found Then
                        
                            strFieldName = Replace(Mid(appWordStory, 4, Len(appWordStory) - 6), Chr(146), Chr(39))
                            
                            On Error Resume Next
                            strCurrentfield = CStr(Nz(rsCurrentForm.Fields(strFieldName).Value))
                            
                            If Err = 0 Then
                                
                                strCurrentfield = Replace(strCurrentfield, Chr(13) + Chr(10), Chr(13))
                                appWordStory.Text = strCurrentfield
                            
                            Else
                            
                                appWordStory.Text = ""
                            
                            End If
                            
                            Err = 0
                            On Error GoTo 0
                        
                        End If
                    
                   Loop Until Not .Found
                                
                End With
            
            Next
        
        'show word
        appWordDoc.Parent.Visible = True
        
    End If

End Function

 

[1DMF]

Is there a reason you are using text replace to perform what appears to be a standard mail merge?

I'm not sure why or what error you are getting, but it might be worth looking at using the functionality built into word / access for performing this type of task.

This is what I use...

    Dim sTable, sXLS, sSourceFile, sTargetFile As String
    Dim WordApp as Object

    sTable = "String_Of_Worksheet_Table_Name"
    sXLS = "Path_And_Filename_To_Data_Source.xls"
    sSourceFile = "Path_And_Filename_To_MailMerge_Template.dot"
    sTargetFile = "Path_And_Filename_For_MailMerge_Result.doc"

    Set WordApp = CreateObject("Word.Application")
    
    'Open document
    WordApp.Documents.Open sSourceFile 
    WordApp.Visible = False

    'issue mailmerge command
    With WordApp.ActiveDocument.MailMerge
        .MainDocumentType = wdFormLetters
        .OpenDataSource Name:= sXLS, readonly:=True, Connection:=sTable, SubType:=wdMergeSubTypeWord, _
            SQLStatement:="Select * From [" & sTable & "]"
        .Destination = wdSendToNewDocument
        .Execute
        .SaveAs FileName:=sTargetFile, FileFormat:=wdWordDocument
        .Quit SaveChanges:=wdDoNotSaveChanges
    End With

    Set WordApp = Nothing

Obviously sTable can be passed as an argument along with the XLS datasource and other variables, this is just an example.

All you need to do is create a data source / query from the form data )plus create the templates for mail-merge, and you are good to go


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[Moss100]

That is a great help - thank you.

Unfortunately my VBA is quite poor.

Could someone help me modify the code above so that the record source is set as the current access form recordsource (I will have a button on the access form to launch the code).

To clarify I will not be using the code with Excel, just access.

Thank you Mark

 

[1DMF]

If the form is simply a display of data in the DB, then you can create a query that pulls the data you want and export to XLS.

Which is what I actually do, as said; the above is just an example of performing a mailmerge, my actual code for performing a particular mailmerge based on a user pressing a particular button is much more complicated.

You need to break it into steps and then feed the above code with the parameters needed...

1. design a dynamic query
2. create the mailmerge template
2. run the query exporting to XLS (tranferspreadsheet)
3. perform the mailmerge using the newly created datasource and template

The bit I perform prior to calling the mailmerge is like this...

        If isQuery(sQueryName) Then
        sPath = "C:\Path_To_Access_Templates_Folder\"
        KillFile sPath & oUser.Name & ".xls"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, sQueryName, sPath & oUser.Name & ".xls", True
    Else
        MsgBox "Export to Excel query " & sQueryName & " does not exist."
        Exit Sub
    End If

All users have their own private data source file when ever a mailmerge is required, it's their name taken from my user object.

So you pass in the query you want to run (remember the query could be designed to read data direct from the current open form using the forms collection syntax...

Forms!FormName.FieldName

It runs the query producing a new data source XLS file, that you can then use for the mailmerge code.

It's really simple if you step back, think a little, and take each step at a time.

oh, I also use a little helper sub for deleting files..

Sub KillFile(ByVal sFilename As String)
    On Error Resume Next
    'delete file ignoring any error (like no file there to delete)
    Kill sFilename
End Sub

The reason you need to ensure you delete any existing file is if you pass it an existing XLS, it will add a worksheet to the XLS file not overwrite the file, obviously you could use the name of the worksheet as the sTable value for the select (as that's what that means!). But you might want to wrap your head around the process before delving into the finer points of the XLS/mailmerge features.

Have a play and come back if you get stuck.

Edit:- Having said all this, there may be a way of using the form datasource for the mailmerge, but I don't know how to do that, so perhaps if possible, one of the usual suspects can interject and we both could learn something ;-)


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[Moss100]

Thanks for the update -

My original code did not used spreadsheets and if possible I would like to avoid creating the spreadsheet.

Thank you for all you help - hopefully others will chip in. Regards Mark

 

[1DMF]

This gives you info on the data source options..

https://msdn.microsoft.com/en-us/library/office/ff841005.aspx

It includes examples of attaching it to an MS Access mdb file and selecting a particular table.

Or it say's you can use a .qry file (perhaps you could generate one?)

Just look at the mailmerge object API / protocol and play around with the data source settings.

Personally, I find it so easy to export a query to XLS and attach that as the datasource, that I haven't bothered looking at alternative ways, so be sure to post any code you get working with alternative datasources.




"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music