Replies

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?

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!

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