Unexpected Big Problem With Mail Merge via Excel VBA

JennEx · #1 05-24-2019, 05:40 AM

I have this code in an Excel VBA project.

Code:

 Set objWord = CreateObject("Word.Application")
    With objWord
        .DisplayAlerts = False
        .Visible = True
        Set oDoc = .Documents.Open(Filename:=fName, ConfirmConversions:=False, _
            ReadOnly:=True, AddToRecentFiles:=False, Visible:=True)
        With oDoc
            With .MailMerge
                .MainDocumentType = wdFormLetters
                .Destination = wdSendtToNewDocument
                .SuppressBlankLines = True
                .OpenDataSource Name:=StrSrc, AddToRecentFiles:=False, LinkToSource:=False, ConfirmConversions:=False, _
                    ReadOnly:=True, format:=wdOpenFormatAuto, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "User ID=Admin;Data Source=" & StrSrc & ";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";", _
                    SQLStatement:=StrSQL, SQLStatement1:="", SubType:=wdMergeSubTypeAccess
                .Execute Pause:=False
            End With
            .Close False
        End With

I use this code to facilitate the mail merge feature of Word from an event in my Excel VBA project. I am getting an error with the line in red.
"The OpenDataSource method or property is not available because this command is not available for reading."

A few interesting observations (to me anyway) that may help diagnose the problem ...
This code was written last year on my work computer and worked flawlessly. I opted to clean up some of my mail merge documents (formatting etc) from home last night and in my testing there did not get that error, and things worked as expected. This morning at work, I get this error.
I have similar different mail merge documents that rely on this code. They all exhibit this behaviour.

If I try to open the mail merge document, unlike working with them at home last night, I get an error opening them ... "An operation cannot be completed because of database engine errors."
Checking on the DataLink Properties, I notice that the Data Source is referring to an old nonexistent file. A check with the "Test Connection" came up with this: "Test connection failed because of an error in initializing provider. Unspecified error"
I manually directed the mailmerge to an appropriate datasource. The merge completed, wrong information in it though, but that may be because I hadn't isolated the recipients. I saved the report and tried executing the code to open it, but got the same initial message.

This is a major setback for my application and my mail merge documents (for a novice) are complex. I am really hesitant to monkey around with stuff I have no idea about fearing damaging the work that so many have helped me with.

I hope someone will be kind enough to walk me through a diagnosis (using information they need to help track it down) to resolve this.

JennEx · #2 05-24-2019, 07:41 AM

Sometimes it pays to Google.

https://clientspace.atlassian.net/wi...+Not+Available

SO far seems ok, but will need some testing.

JennEx · #3 05-24-2019, 08:25 AM

This seems to be working.
My Excel VBA application generates the mail merge documents (reports), saves and displays them. The user can then view the merged documents and edit. However, they are unable to save them after they make the edits. They receive an error : "Cannot Complete the Save Due To File Permission Error"

When this happens, the user has to close the document, and reopen it from it's saved location. Edit changes can then be saved. Are the mail merge documents being saved in a format that doesn't allow them to save after created?

Additionally, all the generated reports are in "Compatibility Mode". Not sure why.

macropod · #4 05-24-2019, 04:12 PM

Since you haven't posted the code that does the save, it's impossible to know what the issue with that aspect might be. For fully functional code, see Run a Mailmerge from Excel, Sending the Output to Individual Files in the Mailmerge Tips & Tricks 'Sticky' thread at the top of this forum: https://www.msofficeforums.com/mail-...ps-tricks.html. Although that code produces the output as individual files, the core logic is the same.

JennEx · #5 05-25-2019, 12:33 PM

This is driving me insane.

Code:

    StrSQL = "SELECT * FROM [DATA$] WHERE [TYPE]='" & itype & "' AND [SIG_CREW]='" & isubresp & "' " & _
        "ORDER BY [STARTS] ASC, [COMPLEX] ASC, [UNIT] ASC"
 
    Set objWord = CreateObject("Word.Application")
    With objWord
        .DisplayAlerts = False
        .Visible = True
        Set oDoc = .Documents.Open(Filename:=fName, ConfirmConversions:=False, _
            ReadOnly:=True, AddToRecentFiles:=False, Visible:=True)
        With oDoc
            With .MailMerge
                .MainDocumentType = wdFormLetters
                .Destination = wdSendtToNewDocument
                .SuppressBlankLines = True
                .OpenDataSource Name:=StrSrc, AddToRecentFiles:=False, LinkToSource:=False, ConfirmConversions:=False, _
                    ReadOnly:=True, format:=wdOpenFormatAuto, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "User ID=Admin;Data Source=" & StrSrc & ";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";", _
                    SQLStatement:=StrSQL, SQLStatement1:="", SubType:=wdMergeSubTypeAccess
                .Execute Pause:=False
            End With
            .Close False
        End With
        .DisplayAlerts = True
        
        'page break routine only for sports reports
        If (Left(itype, 1) <> "G") And (itype <> "DT") Then   'exclude GS reports
            With .activedocument
                If .Sections.count > 1 Then
                    For Each HdFt In .Sections(.Sections.count).Headers
                        If HdFt.Exists Then
                            HdFt.Range.FormattedText = .Sections(1).Headers(HdFt.index).Range.FormattedText
                            HdFt.Range.Characters.Last.Delete
                        End If
                    Next
                    For Each HdFt In .Sections(.Sections.count).Footers
                        If HdFt.Exists Then
                            HdFt.Range.FormattedText = .Sections(1).Footers(HdFt.index).Range.FormattedText
                            HdFt.Range.Characters.Last.Delete
                        End If
                    Next
                End If
                Do While .Sections.count > 1
                    .Sections(1).Range.Characters.Last.Delete
                    DoEvents
                Loop
                .Range.Characters.Last.Delete
            End With
        End If
    
    End With
    
    Set oDoc2 = objWord.activedocument

    'save newly created document
    With oDoc2
        myPath = "u:\PWS\Parks\Parks Operations\Sports\Sports17\WORKORDERS\" & format(ws_vh.Range("B17"), "ddd dd-mmm-yy")
        .SaveAs myPath & "\" & rpt_od & ".docx"
        If dest = 2 Then
            .PrintOut
        End If
        '.Close
    End With
        

    Set oDoc = Nothing: Set oDoc2 = Nothing ': Set objWord = Nothing

Above is the code that is saving the merged documents.

I have no problem creating or working with these reports at home. They create, save, can be opened, editted and resaved without any errors. The only think that sticks out as being unusual is "[Compatibility Mode]"

If I take these same files on my thumbdrive to work, I can access the files but any edits cannot be saved. I am unable to open the documents from within my application, a feature of my excel application I have no issues with at home.

macropod · #6 05-25-2019, 02:46 PM

First, I suggest you use Option Explicit. That way you'll trap errors like:
.Destination = wdSendtToNewDocument

As for the File Permission Error, that suggests you're trying to save to a non-existent folder, a folder you don't have write permissions for, or over a file that your system regards as being in-use.

You might also try using SaveAs2 and incorporating the file format & compatibility parameters. For example:
.SaveAs2 FileName:=myPath & "\" & rpt_od & ".docx", FileFormat:=wdFormatXMLDocument, CompatibilityMode:=wdCurrent
perhaps event with AddToRecentFiles:=False

JennEx · #7 05-26-2019, 06:08 AM

Thanks Paul, still working away at it.

Fixed my spelling mistake. Thank you.
I've chalked up the compatibility issue to an old normal.dotm file, which I renamed and allowed Word to recreate. Looks like that may have been a solution.

I haven't looked yet, but it may also solve the left justification and page numbering proiblems with the one report (mail merge document) I still am struggling with.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel to word Mail Merge problem	mark_kofi	Mail Merge	4	07-17-2018 03:49 PM
Mail Merge View problem	drgt	Mail Merge	2	02-14-2017 04:31 AM
Mail Merge Problem	Hook	Mail Merge	2	01-11-2012 09:36 AM
Mail Merge Problem	JohnRG	Mail Merge	0	11-02-2009 02:50 AM
problem in Mail merge	rjagathe	Mail Merge	0	08-12-2009 08:25 AM