• Word 365 Mail Merge Problem re DDE connection error

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

    Author
    Topic
    #2287240

    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.

    Viewing 4 reply threads
    Author
    Replies
    • #2287434

      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

      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

      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

        Sorry, not a SQL programmer.

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

        cheers, Paul

    • #2288686

      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

      > 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 4 reply threads
    Reply To: Word 365 Mail Merge Problem re DDE connection error

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

    Your information: