10 Replies - 1445 Views - Last Post: 17 February 2013 - 07:19 AM Rate Topic: -----

#1 frankwright1  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 93
  • Joined: 07-May 12

Mailmerge from vb.net

Posted 16 February 2013 - 10:07 AM

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

Is This A Good Question/Topic? 0
  • +

Replies To: Mailmerge from vb.net

#2 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2875
  • View blog
  • Posts: 9,540
  • Joined: 12-December 12

Re: Mailmerge from vb.net

Posted 16 February 2013 - 10:49 AM

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

Was This Post Helpful? 0
  • +
  • -

#3 frankwright1  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 93
  • Joined: 07-May 12

Re: Mailmerge from vb.net

Posted 16 February 2013 - 10:58 AM

Thanks Andrew, so if I understand correctly, should I be exporting the table into a .odc or .qry file first?
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2875
  • View blog
  • Posts: 9,540
  • Joined: 12-December 12

Re: Mailmerge from vb.net

Posted 16 February 2013 - 11:07 AM

View Postfrankwright1, 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.
Was This Post Helpful? 0
  • +
  • -

#5 frankwright1  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 93
  • Joined: 07-May 12

Re: Mailmerge from vb.net

Posted 16 February 2013 - 11:29 AM

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

Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2875
  • View blog
  • Posts: 9,540
  • Joined: 12-December 12

Re: Mailmerge from vb.net

Posted 16 February 2013 - 12:28 PM

Show the exact error message content and confirm which line(s) this corresponds to in your posted code.
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2875
  • View blog
  • Posts: 9,540
  • Joined: 12-December 12

Re: Mailmerge from vb.net

Posted 16 February 2013 - 12:36 PM

Have you imported the Interop library and added a reference for it to your Project?
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2875
  • View blog
  • Posts: 9,540
  • Joined: 12-December 12

Re: Mailmerge from vb.net

Posted 16 February 2013 - 01:05 PM

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

Was This Post Helpful? 0
  • +
  • -

#9 frankwright1  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 93
  • Joined: 07-May 12

Re: Mailmerge from vb.net

Posted 17 February 2013 - 06:10 AM

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


Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2875
  • View blog
  • Posts: 9,540
  • Joined: 12-December 12

Re: Mailmerge from vb.net

Posted 17 February 2013 - 07:04 AM

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.
Was This Post Helpful? 0
  • +
  • -

#11 frankwright1  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 93
  • Joined: 07-May 12

Re: Mailmerge from vb.net

Posted 17 February 2013 - 07:19 AM

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


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1