[frankwright1]

I have created an SQL database with a table called tblREP which has a single line of fields that I would now like to use to populate a microsoft word document.

In visual studio, I have created a button with the following code

        Dim w As New Word.Application
        Dim d As Word.Document
        Dim sqlstr As String
        sqlstr = "SELECT * FROM tblRep"
        d = w.Documents.Open("C:\...AIA.docx")
        Dim strConnection As String
        strConnection = "Data Source=*****\SQLEXPRESS;Initial Catalog=tree1;Persist Security Info=True;User ID=**;Password=******"
        d.MailMerge.OpenDataSource(strConnection, sqlstr)
        d.MailMerge.Execute()
        d.Close(False)
        w.Visible = True

However, I am getting this error:

This file could not be found.
(...\SQLEXPRESS;Initial Catalog=tree1;...)

Does anyone know where I am going wrong?

THanks
Frank

[andrewsw]

The first argument for OpenDataSource is the Name of the data-source, an .odc or .qry filename.

The named arguments would be:

' Set the mail merge data source.
objWord.MailMerge.OpenDataSource Name:="", _
Connection:=strConnection, _
SQLStatement:="SELECT * FROM TABLENAME"

but you can supply them by their argument-order; something like:

d.MailMerge.OpenDataSource("", ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref strConnection, ref sqlstr, ref oMissing, ref oMissing, ref oMissing)

although, I just copied this as an example and I think the argument order is incorrect. Should be easy enough to correct!

The connection and query-string are the 12th and 13th arguments. Note that you need to supply the Name argument: use an empty string "".

You can define the missing variable as:

Dim missing = System.Reflection.Missing.Value

This post has been edited by andrewsw: 16 February 2013 - 10:54 AM

[frankwright1]

Thanks Andrew, so if I understand correctly, should I be exporting the table into a .odc or .qry file first?

[andrewsw]

frankwright1, on 16 February 2013 - 10:58 AM, said:

Thanks Andrew, so if I understand correctly, should I be exporting the table into a .odc or .qry file first?

No, not unless you want to use (and create) an .odc or .qry file to supply the connection information. If you don't know what these are then just use your connection and sql strings, but supply them by their parameter-name, or in the correct order, using the code I supplied as a guide.

[frankwright1]

Thanks Andrew, this is much more complex than I though it would be and probably beyond my capabilities, I now have the following code which gives an unhandled COM exception...

  Dim w As New Word.Application
        Dim d As Word.Document
        d = w.Documents.Open("C:\...\AIA.docm")
        Dim missing = System.Reflection.Missing.Value

        Dim sqlstr As String
        sqlstr = "SELECT * FROM tblRep"
        d = w.Documents.Open("C:\...\AIA.docm")
        Dim strConnection As String
        strConnection = "Data Source=***\SQLEXPRESS;Initial Catalog=tree1;Persist Security Info=True;User ID=******;Password=********"
        d.MailMerge.OpenDataSource("", missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, strConnection, sqlstr, missing, missing, missing)


        d.MailMerge.Execute()
        d.Close(False)
        w.Visible = True

This post has been edited by frankwright1: 16 February 2013 - 11:30 AM

[andrewsw]

Show the exact error message content and confirm which line(s) this corresponds to in your posted code.

[andrewsw]

Have you imported the Interop library and added a reference for it to your Project?

[andrewsw]

Try also with

Dim missing = System.Reflection.Missing
'or
Dim missing As Object = System.Reflection.Missing

rather than .Value - COM should convert it to the appropriate value.

Alternatively, use the named-arguments (using :=) version I mentioned which should work in VB.

This post has been edited by andrewsw: 16 February 2013 - 01:07 PM

[frankwright1]

I changed to the named arguments, but still get the error:

System.Runtime.InteropServices.COMException was unhandled
ErrorCode=-2146824090
HelpLink=C:\Program Files\Microsoft Office\Office12\1033\WDMAIN11.CHM#36966
Message=Command failed
Source=Microsoft Word
StackTrace:
at Microsoft.Office.Interop.Word.MailMerge.OpenDataSource(String Name, Object& Format, Object& ConfirmConversions, Object& ReadOnly, Object& LinkToSource, Object& AddToRecentFiles, Object& PasswordDocument, Object& PasswordTemplate, Object& Revert, Object& WritePasswordDocument, Object& WritePasswordTemplate, Object& Connection, Object& SQLStatement, Object& SQLStatement1, Object& OpenExclusive, Object& SubType)
at tree1.frmTop.Button4_Click(Object sender, EventArgs e) in C:\****\tree1\frmTop.vb:line 148
at System.Windows.Forms.Control.onclick(EventArgs e)
at System.Windows.Forms.Button.onclick(EventArgs e)
at System.Windows.Forms.Button.onmouseup(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativewindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativewindow.WndProc(Message& m)
at System.Windows.Forms.Nativewindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at tree1.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:

Thanks for your help by the way - here are my imports:

Imports Word = Microsoft.Office.Interop.Word
Imports System.Runtime.InteropServices

[andrewsw]

Erm, is your sql-server running?

Try establishing a connection without, at the same time, trying to initiate the mailmerge; this will show whether your connection string is correct.

[frankwright1]

Hi Andrew - this works if that's what you mean?

  'open database connection
        Dim cs As New SqlConnection("Data Source=***\SQLEXPRESS;Initial Catalog=tree1;Persist Security Info=True;User ID=*****;Password=******")
        cs.Open()
        If cs.State = 1 Then
            MsgBox("connected")
        End If