Microsoft Office Forums Unexpected Big Problem With Mail Merge via Excel VBA - DataLink Problem

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-24-2019, 05:40 AM
JennEx JennEx is offline Unexpected Big Problem With Mail Merge via Excel VBA - DataLink Problem Windows XP Unexpected Big Problem With Mail Merge via Excel VBA - DataLink Problem Office 2013
Competent Performer
Unexpected Big Problem With Mail Merge via Excel VBA - DataLink Problem
 
Join Date: May 2010
Posts: 158
JennEx is on a distinguished road
Default Unexpected Big Problem With Mail Merge via Excel VBA - DataLink Problem

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.
Reply With Quote
  #2  
Old 05-24-2019, 07:41 AM
JennEx JennEx is offline Unexpected Big Problem With Mail Merge via Excel VBA - DataLink Problem Windows XP Unexpected Big Problem With Mail Merge via Excel VBA - DataLink Problem Office 2013
Competent Performer
Unexpected Big Problem With Mail Merge via Excel VBA - DataLink Problem
 
Join Date: May 2010
Posts: 158
JennEx is on a distinguished road
Default Solution Maybe ...

Sometimes it pays to Google.

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

SO far seems ok, but will need some testing.
Reply With Quote
  #3  
Old 05-24-2019, 08:25 AM
JennEx JennEx is offline Unexpected Big Problem With Mail Merge via Excel VBA - DataLink Problem Windows XP Unexpected Big Problem With Mail Merge via Excel VBA - DataLink Problem Office 2013
Competent Performer
Unexpected Big Problem With Mail Merge via Excel VBA - DataLink Problem
 
Join Date: May 2010
Posts: 158
JennEx is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 05-24-2019, 04:12 PM
macropod's Avatar
macropod macropod is offline Unexpected Big Problem With Mail Merge via Excel VBA - DataLink Problem Windows 7 64bit Unexpected Big Problem With Mail Merge via Excel VBA - DataLink Problem Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,577
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 05-25-2019, 12:33 PM
JennEx JennEx is offline Unexpected Big Problem With Mail Merge via Excel VBA - DataLink Problem Windows XP Unexpected Big Problem With Mail Merge via Excel VBA - DataLink Problem Office 2013
Competent Performer
Unexpected Big Problem With Mail Merge via Excel VBA - DataLink Problem
 
Join Date: May 2010
Posts: 158
JennEx is on a distinguished road
Default Stumped.

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.
Reply With Quote
  #6  
Old 05-25-2019, 02:46 PM
macropod's Avatar
macropod macropod is offline Unexpected Big Problem With Mail Merge via Excel VBA - DataLink Problem Windows 7 64bit Unexpected Big Problem With Mail Merge via Excel VBA - DataLink Problem Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,577
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 05-26-2019, 06:08 AM
JennEx JennEx is offline Unexpected Big Problem With Mail Merge via Excel VBA - DataLink Problem Windows XP Unexpected Big Problem With Mail Merge via Excel VBA - DataLink Problem Office 2013
Competent Performer
Unexpected Big Problem With Mail Merge via Excel VBA - DataLink Problem
 
Join Date: May 2010
Posts: 158
JennEx is on a distinguished road
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


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
Unexpected Big Problem With Mail Merge via Excel VBA - DataLink Problem Mail Merge View problem drgt Mail Merge 2 02-14-2017 04:31 AM
Mail Merge Problem Hook Mail Merge 5 01-11-2012 10:05 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


All times are GMT -7. The time now is 09:28 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft