News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Word 365 Mail Merge Problem re DDE connection error

    Posted on shpkmom Comment on the AskWoody Lounge

    Home Forums AskWoody support Microsoft Office by version Office 365 and Click-to-Run Word 365 Mail Merge Problem re DDE connection error

    • This topic has 5 replies, 2 voices, and was last updated 1 month ago.
    Viewing 5 reply threads
    • Author
      Posts
      • #2287240 Reply
        shpkmom
        AskWoody Lounger

        Hi there.  I’m really hoping someone can help and that the solution is relatively simple.

        For years, we have been using a mail merge to connect a SQL database and create file labels.  Now that we have switched to Office 365, we’re getting a DDE connection error.  Basically, the coding is relatively simple.  The macro opens a new document, types the query:

        XLODBC

        1

        driver= SQL Server; SERVER=…

        SELECT …

        It then saves this as :

        ActiveDocument.SaveAs FileName:=STRMACROFILES & “NMLabels.dqy”, fileformat:=wdFormatText, _

        lockcomments:=False, Password:=””, addtorecentfiles:=False, WritePassword:=””, ReadOnlyRecommended:=False, _

        EmbedTrueTypeFonts:=False, savenativepictureformat:=False, SaveFormsData:=False, saveasAOCELetter:=False, _

        Encoding:=1252, InsertLineBreaks:=False, AllowSubstitutions:=False, LineEnding:=wdCRLF

        ActiveDocument.SaveAs FileName:=STRMACROFILES & “NMLabels.dqy”, fileformat:=wdFormatText, _

        lockcomments:=False, Password:=””, addtorecentfiles:=False, WritePassword:=””, ReadOnlyRecommended:=False, _

        EmbedTrueTypeFonts:=False, savenativepictureformat:=False, SaveFormsData:=False, saveasAOCELetter:=False, _

        Encoding:=1252, InsertLineBreaks:=False, AllowSubstitutions:=False, LineEnding:=wdCRLF

        I’ve tried connecting the database directly, but the SQL query is quite long and I get a different error that the string is longer than 255 characters.

        I don’t know why it had to change, but is there an easy way to get the same results in Word 365?  I’m hoping that maybe it’s just a matter of changing the way I save the file containing the query, but I’m having trouble finding anything on-line.

        Any assistance would be very much appreciated.

      • #2287434 Reply
        shpkmom
        AskWoody Lounger

        So I changed the coding to use OLEDB connection but can’t get the SQL query small enough to be less than 255 characters.  Please let me know if I’m on the right path and if there is something else I can do?

        strDatabase = “DRIVER=SQL Server;SERVER=…;DATABASE=son_db”
        strSQL = “SELECT * FROM son_db.dbo.client client, son_db.dbo.matter matter, son_db.dbo.praccode praccode, son_db.dbo.timekeep timekeep, son_db.dbo.udf udf, son_db.dbo.udfdef udfdefWHERE timekeep.tkinit = matter.mbillaty AND matter.mprac = praccode.pcode AND udfdef.udfindex = udf.udfindex”
        strSQL2 = “AND udfdef.udtype = udf.udtype AND matter.mmatter = udf.udjoin AND matter.mclient = client.clnum AND ((matter.mstatus=’OP’) AND (udf.udtype=’MT’) AND (udf.udfindex=41) AND (matter.mmatter = ‘” & strvMatterNumber & “‘))”
        ActiveDocument.MailMerge.OpenDataSource Name:=strDatabase, SQLStatement:=strSQL, SQLStatement1:=strSQL2

      • #2287515 Reply
        Paul T
        AskWoody MVP

        If you can’t make the query small enough you could write a procedure to store in the database, then call that.

        cheers, Paul

      • #2287618 Reply
        shpkmom
        AskWoody Lounger

        Thanks — that’s helpful 🙂

        Do you have some general code you could post that would help guide me in the right direction by any chance?  I am really struggling with this …

        Thank you again!

        • #2287816 Reply
          Paul T
          AskWoody MVP

          Sorry, not a SQL programmer.

          Search for SQL stored procedure to get some ideas, or ask your SQL bods.

          cheers, Paul

      • #2288686 Reply
        shpkmom
        AskWoody Lounger

        Ultimately what I am trying to do is connect a MS Query to a mail merge main document.  The original code I had is here:

        ‘Open a blank document

        Documents.Add documenttype:=wdNewBlankDocument

        On Error GoTo Oops

         

        ‘Manually type the query and save

        With Selection

        .TypeText Text:=”XLODBC”

        .TypeParagraph

        .TypeText Text:=”1″

        .TypeParagraph

        .TypeText Text:=”DRIVER=SQL Server;SERVER=NAME;UID=NAME;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=01234;DATABASE=son_db”

        .TypeParagraph

        .TypeText Text:=”SELECT matter.mmatter, client.clname1, client.clname2, matter.mdesc1, matter.mdesc2, matter.mdesc3, matter.mclient, timekeep.tkinitb, matter.mprac, praccode.pdesc, udf.udvalue  FROM son_db.dbo.client client, son_db.dbo.matter matter, son_db.dbo.praccode praccode, son_db.dbo.timekeep timekeep, son_db.dbo.udf udf, son_db.dbo.udfdef udfdef  WHERE timekeep.tkinit = matter.mbillaty AND matter.mprac = praccode.pcode AND udfdef.udfindex = udf.udfindex AND udfdef.udtype = udf.udtype AND matter.mmatter = udf.udjoin AND matter.mclient = client.clnum AND ((matter.mstatus=’OP’) AND (udf.udtype=’MT’) AND (udf.udfindex=41) AND (matter.mmatter = ‘” & strvMatterNumber & “‘))”

        End With

         

        ‘Save the query text

        ActiveDocument.SaveAs FileName:=STRMACROFILES & “NMLabels.dqy”, fileformat:=wdFormatText, _

        lockcomments:=False, Password:=””, addtorecentfiles:=False, WritePassword:=””, ReadOnlyRecommended:=False, _

        EmbedTrueTypeFonts:=False, savenativepictureformat:=False, SaveFormsData:=False, saveasAOCELetter:=False, _

        Encoding:=1252, InsertLineBreaks:=False, AllowSubstitutions:=False, LineEnding:=wdCRLF

        ActiveDocument.Close

        Application.ScreenUpdating = False

         

        ‘Open the template and set as main document

         

        Documents.Add Template:=STRMRTEMPLATES & “Open NEW Matter Labels.dotx”, newtemplate:=False

        Set MainDoc = ActiveDocument

        ActiveDocument.MailMerge.OpenDataSource Name:=STRMACROFILES & “NMLabels.dqy”, ConfirmConversions:=False

         

        Since we switched to Word 365, when this macro is run it has a message at the bottom saying connecting to MS Query, it takes a long time and then returns a DDE error.  I would like to know how to fix it so that I can still attach this query to a mail merge document without the error.

         

        Does anyone have any ideas?

      • #2288717 Reply
        anonymous
        Guest

        > Does anyone have any ideas?

        This seems like a long shot, but is there any chance your change to Office/Word 365 included a quiet change to a blocking security setting? Maybe double-check current Word security settings?

        Hope this helps.

    Viewing 5 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Word 365 Mail Merge Problem re DDE connection error

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.