Merge from a Command button - RunTime Error 9105 - String is longer than 255 characters

PerksP
PerksP used Ask the Experts™
on
I have a piece of code which I adapted from suggestions on this site.

It merges a letter in Word to an excel spreadsheet. It has been working fantastically but I have just changed the path and am getting the error message RunTime Error 9105 - String is longer than 255 characters.  Does anyone have any ideas what is going wrong?

I'd really appreciate any help you can give me
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "P:\CareTeamDatabase\DataSources\SourceFamily.xls", ConfirmConversions:= _
        False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
        PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
        WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
        Connection:= _
        "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=P:\CareTeamDatabase\DataSources\SourceFamily.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Je" _
        , SQLStatement:="SELECT * FROM `Letters_Family$`", SQLStatement1:="", _
        SubType:=wdMergeSubTypeAccess
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
GrahamSkanRetired
Top Expert 2012

Commented:
Does it work in manual mode (from the Word interface)?

If so, you could save the main document with the latest path, and if you don't need to change any details in your code,
you could simply automate the merge with:

ActiveDocument.MailMerge.Execute




try removing the un necessary arguments from this line:

"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=P:\CareTeamDatabase\DataSources\SourceFamily.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Je" _
        , SQLStatement:="SELECT * FROM `Letters_Family$`", SQLStatement1:="", _
        SubType:=wdMergeSubTypeAccess


most of the time this is the issue


Author

Commented:
Thank you so much that did the trick

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial