Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automating Mail merge between Word 2003 and Access 2003 using VB.NET
Hi all!
I am using VB.Net Pro to open a mail merge word document and use an MS Access 2003 table. The thing is the mail merge is working, but I want to automate the process of selecting the table. Right now, it opens the correct word document, and looks at the correct database, but the thing is the use is PROMPTED to select the table. I want to know how i can automate this. I have been looking for a solution for the past week and still not successful! - the table i want to select is the customer table. The code i am using is the following: Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Dim wrdApp As New Word.Application Dim wrdDoc As Word.Document Dim wrdMerger As Word.MailMerge Dim strTemplateName As String Dim strDataSourceDB As String Dim strDataSourceTable As String Dim testCmd As New OleDbCommand("SELECT * FROM Customer") strDataSourceDB = Application.StartupPath.ToString & "\East-West Travel Agents.mdb" strTemplateName = Application.StartupPath.ToString & "\testMergeLetter.doc" strDataSourceTable = "Customer" wrdDoc = wrdApp.Documents.Add(strTemplateName) wrdMerger = wrdDoc.MailMerge With wrdMerger .MainDocumentType = Word.WdMailMergeMainDocType.wdFormLetters .OpenDataSource(Name:=strDataSourceDB, _ linktosource:=True, addtorecentfiles:=False, _ connection:="TABLE " & "'" & strDataSourceTable & "'") .Destination = Word.WdMailMergeDestination.wdSendToPrinter .Execute() wrdDoc.Close(False) wrdApp.Quit() End With End Sub Thank you in advance for any help!!! ) |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automating Mail merge between Word 2003 and Access 2003 using VB.NET
Try
..OpenDataSource(Name:=strDataSourceDB, _ SQLStatement:="SELECT * FROM [" & strDataSourceTable & "]", _ SubType:=wdMergeSubTypeAccess) (I haven't checked in VB.NET but those are the parameters you need in VBA - In this case the connection type will be OLEDB, and you don't need a connection string. Many of the parameters to OpenDataSource have no effect, even in some cases when the documentation suggests they do. The values of Name (obviously), Connection, SQLStatement and SubType are usually the only ones you need) However, you may also need to take account of the following KB Article: http://support.microsoft.com/kb/825765/en-us Peter Jamieson wrote in message ups.com... Hi all! I am using VB.Net Pro to open a mail merge word document and use an MS Access 2003 table. The thing is the mail merge is working, but I want to automate the process of selecting the table. Right now, it opens the correct word document, and looks at the correct database, but the thing is the use is PROMPTED to select the table. I want to know how i can automate this. I have been looking for a solution for the past week and still not successful! - the table i want to select is the customer table. The code i am using is the following: Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Dim wrdApp As New Word.Application Dim wrdDoc As Word.Document Dim wrdMerger As Word.MailMerge Dim strTemplateName As String Dim strDataSourceDB As String Dim strDataSourceTable As String Dim testCmd As New OleDbCommand("SELECT * FROM Customer") strDataSourceDB = Application.StartupPath.ToString & "\East-West Travel Agents.mdb" strTemplateName = Application.StartupPath.ToString & "\testMergeLetter.doc" strDataSourceTable = "Customer" wrdDoc = wrdApp.Documents.Add(strTemplateName) wrdMerger = wrdDoc.MailMerge With wrdMerger .MainDocumentType = Word.WdMailMergeMainDocType.wdFormLetters .OpenDataSource(Name:=strDataSourceDB, _ linktosource:=True, addtorecentfiles:=False, _ connection:="TABLE " & "'" & strDataSourceTable & "'") .Destination = Word.WdMailMergeDestination.wdSendToPrinter .Execute() wrdDoc.Close(False) wrdApp.Quit() End With End Sub Thank you in advance for any help!!! ) |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automating Mail merge between Word 2003 and Access 2003 using VB.NET
I believe that
.OpenDataSource(Name:=strDataSourceDB, _ linktosource:=True, addtorecentfiles:=False, _ connection:="TABLE " & "'" & strDataSourceTable & "'") Should be: .OpenDataSource Name:=strDataSourceDB, _ linktosource:=True, addtorecentfiles:=False, _ connection:="TABLE " & & strDataSourceTable -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP wrote in message ups.com... Hi all! I am using VB.Net Pro to open a mail merge word document and use an MS Access 2003 table. The thing is the mail merge is working, but I want to automate the process of selecting the table. Right now, it opens the correct word document, and looks at the correct database, but the thing is the use is PROMPTED to select the table. I want to know how i can automate this. I have been looking for a solution for the past week and still not successful! - the table i want to select is the customer table. The code i am using is the following: Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Dim wrdApp As New Word.Application Dim wrdDoc As Word.Document Dim wrdMerger As Word.MailMerge Dim strTemplateName As String Dim strDataSourceDB As String Dim strDataSourceTable As String Dim testCmd As New OleDbCommand("SELECT * FROM Customer") strDataSourceDB = Application.StartupPath.ToString & "\East-West Travel Agents.mdb" strTemplateName = Application.StartupPath.ToString & "\testMergeLetter.doc" strDataSourceTable = "Customer" wrdDoc = wrdApp.Documents.Add(strTemplateName) wrdMerger = wrdDoc.MailMerge With wrdMerger .MainDocumentType = Word.WdMailMergeMainDocType.wdFormLetters .OpenDataSource(Name:=strDataSourceDB, _ linktosource:=True, addtorecentfiles:=False, _ connection:="TABLE " & "'" & strDataSourceTable & "'") .Destination = Word.WdMailMergeDestination.wdSendToPrinter .Execute() wrdDoc.Close(False) wrdApp.Quit() End With End Sub Thank you in advance for any help!!! ) |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automating Mail merge between Word 2003 and Access 2003 using VB.NET
That's excellent Peter, it worked just perfect!! Thanks a lot for your help!! ) Aparna On Oct 17, 12:48 pm, "Peter Jamieson" wrote: Try .OpenDataSource(Name:=strDataSourceDB, _ SQLStatement:="SELECT * FROM [" & strDataSourceTable & "]", _ SubType:=wdMergeSubTypeAccess) (I haven't checked in VB.NET but those are the parameters you need in VBA - In this case the connection type will be OLEDB, and you don't need a connection string. Many of the parameters to OpenDataSource have no effect, even in some cases when the documentation suggests they do. The values of Name (obviously), Connection, SQLStatement and SubType are usually the only ones you need) However, you may also need to take account of the following KB Article: http://support.microsoft.com/kb/825765/en-us Peter Jamieson wrote in oglegroups.com... Hi all! I am using VB.Net Pro to open a mail merge word document and use an MS Access 2003 table. The thing is the mail merge is working, but I want to automate the process of selecting the table. Right now, it opens the correct word document, and looks at the correct database, but the thing is the use is PROMPTED to select the table. I want to know how i can automate this. I have been looking for a solution for the past week and still not successful! - the table i want to select is the customer table. The code i am using is the following: Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Dim wrdApp As New Word.Application Dim wrdDoc As Word.Document Dim wrdMerger As Word.MailMerge Dim strTemplateName As String Dim strDataSourceDB As String Dim strDataSourceTable As String Dim testCmd As New OleDbCommand("SELECT * FROM Customer") strDataSourceDB = Application.StartupPath.ToString & "\East-West Travel Agents.mdb" strTemplateName = Application.StartupPath.ToString & "\testMergeLetter.doc" strDataSourceTable = "Customer" wrdDoc = wrdApp.Documents.Add(strTemplateName) wrdMerger = wrdDoc.MailMerge With wrdMerger .MainDocumentType = Word.WdMailMergeMainDocType.wdFormLetters .OpenDataSource(Name:=strDataSourceDB, _ linktosource:=True, addtorecentfiles:=False, _ connection:="TABLE " & "'" & strDataSourceTable & "'") .Destination = Word.WdMailMergeDestination.wdSendToPrinter .Execute() wrdDoc.Close(False) wrdApp.Quit() End With End Sub Thank you in advance for any help!!! )- Hide quoted text -- Show quoted text - |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automating Mail merge between Word 2003 and Access 2003 using VB.NET
Hi all
wich code should i add if the Access Bd has a password??? Thank you in advance. Manuel Joćo escreveu na mensagem ups.com... Hi all! I am using VB.Net Pro to open a mail merge word document and use an MS Access 2003 table. The thing is the mail merge is working, but I want to automate the process of selecting the table. Right now, it opens the correct word document, and looks at the correct database, but the thing is the use is PROMPTED to select the table. I want to know how i can automate this. I have been looking for a solution for the past week and still not successful! - the table i want to select is the customer table. The code i am using is the following: Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Dim wrdApp As New Word.Application Dim wrdDoc As Word.Document Dim wrdMerger As Word.MailMerge Dim strTemplateName As String Dim strDataSourceDB As String Dim strDataSourceTable As String Dim testCmd As New OleDbCommand("SELECT * FROM Customer") strDataSourceDB = Application.StartupPath.ToString & "\East-West Travel Agents.mdb" strTemplateName = Application.StartupPath.ToString & "\testMergeLetter.doc" strDataSourceTable = "Customer" wrdDoc = wrdApp.Documents.Add(strTemplateName) wrdMerger = wrdDoc.MailMerge With wrdMerger .MainDocumentType = Word.WdMailMergeMainDocType.wdFormLetters .OpenDataSource(Name:=strDataSourceDB, _ linktosource:=True, addtorecentfiles:=False, _ connection:="TABLE " & "'" & strDataSourceTable & "'") .Destination = Word.WdMailMergeDestination.wdSendToPrinter .Execute() wrdDoc.Close(False) wrdApp.Quit() End With End Sub Thank you in advance for any help!!! ) |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automating Mail merge between Word 2003 and Access 2003 using VB.NET
Unfortunately, if you have an old-style database password, I think your
options are limited. You can go back to the DDE connection type: Access will open, and the user will be prompted for the database password. But let's not go down that route. You can create an Office Database Connection file (.odc) but you will have to specify that you want to persist the security information, and specify the password in plain text using Jet OLEDBatabase Password. The only way I know to avoid storing the database password in plain text is to create a UDL file (all it needs is the name of the OLEDB provider, so you can use it for any Jet .mdb pathname of the database, then open the .udl instead of the .mdb. Then in the OpenDataSource, you have to specify the database password. So for example, if the database is at c:\a\apw.mdb and has password "mypw", you can create a .udl called c:\a\jet.udl containing the following: [oledb] ; Everything after this line is an OLE DB initstring Provider=Microsoft.Jet.OLEDB.4.0; Then your OpenDataSource has to be more like: strDataSourceUDL = "c:\a\jet.udl" strDataSourceDB = "c:\a\apw.mdb" ' Ideally, you do not embed your password in the code, but ' it is up to you to find a way to avoid doing that strDatabasePassword = "mypw" strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDataSourceDB & _ & ";;Jet OLEDBatabase Password=" & strDatabasePassword & ";" ..OpenDataSource(Name:=strDataSourceUDL, _ COnnection:=strConnection, _ SQLStatement:="SELECT * FROM [" & strDataSourceTable & "]", _ SubType:=wdMergeSubTypeOther) (Sorry if I got the VB.NET syntax wrong, and you may need to experiment to get this exactly right). You may also be able to do it using an ODBC DSN of some kind and a similar approach, but the problems are similar. Unfortunately, if you try to use exactly the same connection string but specify Name:=strDataSourceDB instead of Name:=strDataSourceUDL, the method fails, despite the fact that it looks as if you are providing exactly the same information. I think that if you specify a .mdb or .odc in the Name parameter, Word just ignores the Connection string. But I'm not sure. Also, if you get it wrong, Word pops up its datalink dialog box, then if it still cannot connect with OLEDB, it pops up its ODBC dialog (and probably selects the wrong database initially), and if that fails, it tries to connect via DDE. So if anything goes worng, it is a very poor experience for the user. Peter Jamieson "Manuel Joćo" wrote in message ... Hi all wich code should i add if the Access Bd has a password??? Thank you in advance. Manuel Joćo escreveu na mensagem ups.com... Hi all! I am using VB.Net Pro to open a mail merge word document and use an MS Access 2003 table. The thing is the mail merge is working, but I want to automate the process of selecting the table. Right now, it opens the correct word document, and looks at the correct database, but the thing is the use is PROMPTED to select the table. I want to know how i can automate this. I have been looking for a solution for the past week and still not successful! - the table i want to select is the customer table. The code i am using is the following: Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Dim wrdApp As New Word.Application Dim wrdDoc As Word.Document Dim wrdMerger As Word.MailMerge Dim strTemplateName As String Dim strDataSourceDB As String Dim strDataSourceTable As String Dim testCmd As New OleDbCommand("SELECT * FROM Customer") strDataSourceDB = Application.StartupPath.ToString & "\East-West Travel Agents.mdb" strTemplateName = Application.StartupPath.ToString & "\testMergeLetter.doc" strDataSourceTable = "Customer" wrdDoc = wrdApp.Documents.Add(strTemplateName) wrdMerger = wrdDoc.MailMerge With wrdMerger .MainDocumentType = Word.WdMailMergeMainDocType.wdFormLetters .OpenDataSource(Name:=strDataSourceDB, _ linktosource:=True, addtorecentfiles:=False, _ connection:="TABLE " & "'" & strDataSourceTable & "'") .Destination = Word.WdMailMergeDestination.wdSendToPrinter .Execute() wrdDoc.Close(False) wrdApp.Quit() End With End Sub Thank you in advance for any help!!! ) |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't get Word 2003 to keep data file with mail merge main documen | Mailmerge | |||
Mail Merge - Word 2003 & Access 97 | Microsoft Word Help | |||
Can Word 2003 mail merge an Access value from a look-up table? | Mailmerge | |||
Using Mail Merge in Word 2003 with Access 2000 | Mailmerge | |||
Word 2003 and Access 2003 Mail Merge question | Microsoft Word Help |