Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merge Fields won't populate after Datasource change
If this post should be in a different forum, please let me know. I'm trying
to run a Word 2003 Mailmerge to a mailing label template. I've set up the Mailing label mailmerge and my datasource is a stored query in an Access database. Everything works fine with the mailmerge from Word. Now I want to run that mailmerge from a Visual Basic 6 program, but change the datasource. When I change the datasource I don't get anything coming out on the labels. I'm trying to set the datasource to a query statement that I build in the program. For my initial tests the Access database is the same and the query statement I build is identical to the stored query statement in the database. The fields that are returned in the queries will be identical, ultimately the where clause will change so it will select different records. But first I have to get the labels populated. When I run the code in VB and the new Word doc is created with the labels the labels are blank. Here's the code and the query statement. I recorded a Word macro to get the proper syntax for the Opendatasource command. gsDBLocation is the fully qualified path to the database. The msSQL variable holds the query statement. After opening the datasource, firstrecord returns a 1 and lastrecord returns a -16 (there should be 3 records) so it appears that the query is failing, but it's the same as the stored query (which works) so I'm stumped. Any help would be appreciated. Thanks in advance. Query: SELECT distinct REG.*, REGADDR.*, Address.* FROM REG INNER JOIN (Address INNER JOIN REGADDR ON Address.A_ADDRCODE = REGADDR.RA_ADDRCODE) ON REG.CODE = REGADDR.RA_REGCODE WHERE (((REGADDR.WIRE_NOTICE)=True)) or (((REGADDR.WIRE_NOTICE_DUP)=True)); Code: Set objWordApp = CreateObject("Word.application") 'New word.Application If chkLabels.Value = vbChecked Then Set objWordDoc = objWordApp.Documents.Open(C:\Labels5663.doc") End If objWordApp.Visible = vbTrue With objWordDoc.MailMerge .MainDocumentType = wdMailingLabels .SuppressBlankLines = True .OpenDataSource Name:=gsDBLocation, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:= _ "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Data Source=gsDBLocation;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:" _ , SQLStatement:=msSQL, SQLStatement1:="", _ SubType:=wdMergeSubTypeAccess objWordDoc.MailMerge.ViewMailMergeFieldCodes = vbTrue With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False End With |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merge Fields won't populate after Datasource change
If you haven't made the registry change described in
http://support.microsoft.com/kb/825765/en-us try that first. Everything else looks OK (I can't say I've checked in detail, but the SQL statement is only around 248 characters so it should fit into SQLStatement. However, Word does not always record the OpenDataSource statement correctly (e.g. it usually truncates the Connection string). You can also cut a lot of stuff out - I'd try: .OpenDataSource _ Name:=gsDBLocation, _ Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word="""";User ID=Admin;Data Source=gsDBLocation;Mode=Read;", _ SQLStatement:=msSQL, _ SubType:=wdMergeSubTypeAccess (you may be able to get away with .OpenDataSource _ Name:=gsDBLocation, _ SQLStatement:=msSQL ) -- Peter Jamieson http://tips.pjmsn.me.uk "Graywolf" wrote in message ... If this post should be in a different forum, please let me know. I'm trying to run a Word 2003 Mailmerge to a mailing label template. I've set up the Mailing label mailmerge and my datasource is a stored query in an Access database. Everything works fine with the mailmerge from Word. Now I want to run that mailmerge from a Visual Basic 6 program, but change the datasource. When I change the datasource I don't get anything coming out on the labels. I'm trying to set the datasource to a query statement that I build in the program. For my initial tests the Access database is the same and the query statement I build is identical to the stored query statement in the database. The fields that are returned in the queries will be identical, ultimately the where clause will change so it will select different records. But first I have to get the labels populated. When I run the code in VB and the new Word doc is created with the labels the labels are blank. Here's the code and the query statement. I recorded a Word macro to get the proper syntax for the Opendatasource command. gsDBLocation is the fully qualified path to the database. The msSQL variable holds the query statement. After opening the datasource, firstrecord returns a 1 and lastrecord returns a -16 (there should be 3 records) so it appears that the query is failing, but it's the same as the stored query (which works) so I'm stumped. Any help would be appreciated. Thanks in advance. Query: SELECT distinct REG.*, REGADDR.*, Address.* FROM REG INNER JOIN (Address INNER JOIN REGADDR ON Address.A_ADDRCODE = REGADDR.RA_ADDRCODE) ON REG.CODE = REGADDR.RA_REGCODE WHERE (((REGADDR.WIRE_NOTICE)=True)) or (((REGADDR.WIRE_NOTICE_DUP)=True)); Code: Set objWordApp = CreateObject("Word.application") 'New word.Application If chkLabels.Value = vbChecked Then Set objWordDoc = objWordApp.Documents.Open(C:\Labels5663.doc") End If objWordApp.Visible = vbTrue With objWordDoc.MailMerge .MainDocumentType = wdMailingLabels .SuppressBlankLines = True .OpenDataSource Name:=gsDBLocation, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:= _ "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Data Source=gsDBLocation;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:" _ , SQLStatement:=msSQL, SQLStatement1:="", _ SubType:=wdMergeSubTypeAccess objWordDoc.MailMerge.ViewMailMergeFieldCodes = vbTrue With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False End With |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merge Fields won't populate after Datasource change
Thanks for the feedback Peter. I will try the registry change and let you
know what happens. I didn't think I needed everything on the opendatasource line but like I said I just recorded a macro and cut a pasted the line into my VB code changing the arguments where needed. I know the SQL statement is restricted to about 250 chars. Is the second SQL argument restricted to the same length? That might give me problems if it is. Thanks again. Happy New Year!!! "Peter Jamieson" wrote: If you haven't made the registry change described in http://support.microsoft.com/kb/825765/en-us try that first. Everything else looks OK (I can't say I've checked in detail, but the SQL statement is only around 248 characters so it should fit into SQLStatement. However, Word does not always record the OpenDataSource statement correctly (e.g. it usually truncates the Connection string). You can also cut a lot of stuff out - I'd try: .OpenDataSource _ Name:=gsDBLocation, _ Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word="""";User ID=Admin;Data Source=gsDBLocation;Mode=Read;", _ SQLStatement:=msSQL, _ SubType:=wdMergeSubTypeAccess (you may be able to get away with .OpenDataSource _ Name:=gsDBLocation, _ SQLStatement:=msSQL ) -- Peter Jamieson http://tips.pjmsn.me.uk "Graywolf" wrote in message ... If this post should be in a different forum, please let me know. I'm trying to run a Word 2003 Mailmerge to a mailing label template. I've set up the Mailing label mailmerge and my datasource is a stored query in an Access database. Everything works fine with the mailmerge from Word. Now I want to run that mailmerge from a Visual Basic 6 program, but change the datasource. When I change the datasource I don't get anything coming out on the labels. I'm trying to set the datasource to a query statement that I build in the program. For my initial tests the Access database is the same and the query statement I build is identical to the stored query statement in the database. The fields that are returned in the queries will be identical, ultimately the where clause will change so it will select different records. But first I have to get the labels populated. When I run the code in VB and the new Word doc is created with the labels the labels are blank. Here's the code and the query statement. I recorded a Word macro to get the proper syntax for the Opendatasource command. gsDBLocation is the fully qualified path to the database. The msSQL variable holds the query statement. After opening the datasource, firstrecord returns a 1 and lastrecord returns a -16 (there should be 3 records) so it appears that the query is failing, but it's the same as the stored query (which works) so I'm stumped. Any help would be appreciated. Thanks in advance. Query: SELECT distinct REG.*, REGADDR.*, Address.* FROM REG INNER JOIN (Address INNER JOIN REGADDR ON Address.A_ADDRCODE = REGADDR.RA_ADDRCODE) ON REG.CODE = REGADDR.RA_REGCODE WHERE (((REGADDR.WIRE_NOTICE)=True)) or (((REGADDR.WIRE_NOTICE_DUP)=True)); Code: Set objWordApp = CreateObject("Word.application") 'New word.Application If chkLabels.Value = vbChecked Then Set objWordDoc = objWordApp.Documents.Open(C:\Labels5663.doc") End If objWordApp.Visible = vbTrue With objWordDoc.MailMerge .MainDocumentType = wdMailingLabels .SuppressBlankLines = True .OpenDataSource Name:=gsDBLocation, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:= _ "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Data Source=gsDBLocation;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:" _ , SQLStatement:=msSQL, SQLStatement1:="", _ SubType:=wdMergeSubTypeAccess objWordDoc.MailMerge.ViewMailMergeFieldCodes = vbTrue With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False End With |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merge Fields won't populate after Datasource change
There are two different limits: the total you can fit into the combined
SQLStatement parameters, which is I think 510 chars, and the total that the provider/driver will actually accept, which depends partly on the version of Word and the type of driver/provider. Some don't allow more than about 255 altogether. I can't rember the details now. Happy new year! Peter Jamieson http://tips.pjmsn.me.uk Graywolf wrote: Thanks for the feedback Peter. I will try the registry change and let you know what happens. I didn't think I needed everything on the opendatasource line but like I said I just recorded a macro and cut a pasted the line into my VB code changing the arguments where needed. I know the SQL statement is restricted to about 250 chars. Is the second SQL argument restricted to the same length? That might give me problems if it is. Thanks again. Happy New Year!!! "Peter Jamieson" wrote: If you haven't made the registry change described in http://support.microsoft.com/kb/825765/en-us try that first. Everything else looks OK (I can't say I've checked in detail, but the SQL statement is only around 248 characters so it should fit into SQLStatement. However, Word does not always record the OpenDataSource statement correctly (e.g. it usually truncates the Connection string). You can also cut a lot of stuff out - I'd try: .OpenDataSource _ Name:=gsDBLocation, _ Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word="""";User ID=Admin;Data Source=gsDBLocation;Mode=Read;", _ SQLStatement:=msSQL, _ SubType:=wdMergeSubTypeAccess (you may be able to get away with .OpenDataSource _ Name:=gsDBLocation, _ SQLStatement:=msSQL ) -- Peter Jamieson http://tips.pjmsn.me.uk "Graywolf" wrote in message ... If this post should be in a different forum, please let me know. I'm trying to run a Word 2003 Mailmerge to a mailing label template. I've set up the Mailing label mailmerge and my datasource is a stored query in an Access database. Everything works fine with the mailmerge from Word. Now I want to run that mailmerge from a Visual Basic 6 program, but change the datasource. When I change the datasource I don't get anything coming out on the labels. I'm trying to set the datasource to a query statement that I build in the program. For my initial tests the Access database is the same and the query statement I build is identical to the stored query statement in the database. The fields that are returned in the queries will be identical, ultimately the where clause will change so it will select different records. But first I have to get the labels populated. When I run the code in VB and the new Word doc is created with the labels the labels are blank. Here's the code and the query statement. I recorded a Word macro to get the proper syntax for the Opendatasource command. gsDBLocation is the fully qualified path to the database. The msSQL variable holds the query statement. After opening the datasource, firstrecord returns a 1 and lastrecord returns a -16 (there should be 3 records) so it appears that the query is failing, but it's the same as the stored query (which works) so I'm stumped. Any help would be appreciated. Thanks in advance. Query: SELECT distinct REG.*, REGADDR.*, Address.* FROM REG INNER JOIN (Address INNER JOIN REGADDR ON Address.A_ADDRCODE = REGADDR.RA_ADDRCODE) ON REG.CODE = REGADDR.RA_REGCODE WHERE (((REGADDR.WIRE_NOTICE)=True)) or (((REGADDR.WIRE_NOTICE_DUP)=True)); Code: Set objWordApp = CreateObject("Word.application") 'New word.Application If chkLabels.Value = vbChecked Then Set objWordDoc = objWordApp.Documents.Open(C:\Labels5663.doc") End If objWordApp.Visible = vbTrue With objWordDoc.MailMerge .MainDocumentType = wdMailingLabels .SuppressBlankLines = True .OpenDataSource Name:=gsDBLocation, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:= _ "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Data Source=gsDBLocation;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:" _ , SQLStatement:=msSQL, SQLStatement1:="", _ SubType:=wdMergeSubTypeAccess objWordDoc.MailMerge.ViewMailMergeFieldCodes = vbTrue With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False End With |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merge Fields won't populate after Datasource change
Peter,
Sorry for the delay. Got pulled off this and put on another project for a while. Now I'm back to this. Tried making the registry change with no affect on my results; still getting blank labels. As I step through the code in the debugger when I open the Word label document with the following statement Set objWordDoc = objWordApp.Documents.Open("C:\Labels5663.doc") and then make it visible, I can see the 'labels' on screen in normal view because the document is set up to use the predefined query in the Access database as the datasource. When I go to change the datasource by setting the SQLStatement argument within the .OpenDataSource method I lose my labels. Do you have any other ideas? If I cannot find a resolution here, do you think it would be possible to alter the SQL statement in the predefined query in the Access table and then just open the mailmerge?? If so, any idea how I would go about changing the query in Access? or is that question for another forum? Thanks again for all your help. "Peter Jamieson" wrote: There are two different limits: the total you can fit into the combined SQLStatement parameters, which is I think 510 chars, and the total that the provider/driver will actually accept, which depends partly on the version of Word and the type of driver/provider. Some don't allow more than about 255 altogether. I can't rember the details now. Happy new year! Peter Jamieson http://tips.pjmsn.me.uk Graywolf wrote: Thanks for the feedback Peter. I will try the registry change and let you know what happens. I didn't think I needed everything on the opendatasource line but like I said I just recorded a macro and cut a pasted the line into my VB code changing the arguments where needed. I know the SQL statement is restricted to about 250 chars. Is the second SQL argument restricted to the same length? That might give me problems if it is. Thanks again. Happy New Year!!! "Peter Jamieson" wrote: If you haven't made the registry change described in http://support.microsoft.com/kb/825765/en-us try that first. Everything else looks OK (I can't say I've checked in detail, but the SQL statement is only around 248 characters so it should fit into SQLStatement. However, Word does not always record the OpenDataSource statement correctly (e.g. it usually truncates the Connection string). You can also cut a lot of stuff out - I'd try: .OpenDataSource _ Name:=gsDBLocation, _ Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word="""";User ID=Admin;Data Source=gsDBLocation;Mode=Read;", _ SQLStatement:=msSQL, _ SubType:=wdMergeSubTypeAccess (you may be able to get away with .OpenDataSource _ Name:=gsDBLocation, _ SQLStatement:=msSQL ) -- Peter Jamieson http://tips.pjmsn.me.uk "Graywolf" wrote in message ... If this post should be in a different forum, please let me know. I'm trying to run a Word 2003 Mailmerge to a mailing label template. I've set up the Mailing label mailmerge and my datasource is a stored query in an Access database. Everything works fine with the mailmerge from Word. Now I want to run that mailmerge from a Visual Basic 6 program, but change the datasource. When I change the datasource I don't get anything coming out on the labels. I'm trying to set the datasource to a query statement that I build in the program. For my initial tests the Access database is the same and the query statement I build is identical to the stored query statement in the database. The fields that are returned in the queries will be identical, ultimately the where clause will change so it will select different records. But first I have to get the labels populated. When I run the code in VB and the new Word doc is created with the labels the labels are blank. Here's the code and the query statement. I recorded a Word macro to get the proper syntax for the Opendatasource command. gsDBLocation is the fully qualified path to the database. The msSQL variable holds the query statement. After opening the datasource, firstrecord returns a 1 and lastrecord returns a -16 (there should be 3 records) so it appears that the query is failing, but it's the same as the stored query (which works) so I'm stumped. Any help would be appreciated. Thanks in advance. Query: SELECT distinct REG.*, REGADDR.*, Address.* FROM REG INNER JOIN (Address INNER JOIN REGADDR ON Address.A_ADDRCODE = REGADDR.RA_ADDRCODE) ON REG.CODE = REGADDR.RA_REGCODE WHERE (((REGADDR.WIRE_NOTICE)=True)) or (((REGADDR.WIRE_NOTICE_DUP)=True)); Code: Set objWordApp = CreateObject("Word.application") 'New word.Application If chkLabels.Value = vbChecked Then Set objWordDoc = objWordApp.Documents.Open(C:\Labels5663.doc") End If objWordApp.Visible = vbTrue With objWordDoc.MailMerge .MainDocumentType = wdMailingLabels .SuppressBlankLines = True .OpenDataSource Name:=gsDBLocation, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:= _ "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Data Source=gsDBLocation;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:" _ , SQLStatement:=msSQL, SQLStatement1:="", _ SubType:=wdMergeSubTypeAccess objWordDoc.MailMerge.ViewMailMergeFieldCodes = vbTrue With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False End With |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merge Fields won't populate after Datasource change
Peter,
Sorry for the delay. I got moved to another project for a while, now I'm back. Tried the registry change with no affect on my results; I'm still not getting label output. When I step through the code in the debugger I can see my labels when I open the Word document because the template points to the predefined query in Access as the original datasource that was used when creating the label mailmerge. When I go to change the datasource by altering the SQLStatement argument of the OpenDataSource method, I lose my labels. Any other ideas? If I cannot find a resolution here, do you think it would be possible to change the SQL in the predefined query in Access? If I can, then, in theory I would only have to open the labels document and the altered predefined query would take care of itself. If you think this is possible, do you have any idea of how to change the sql statement in a predefined Access query? or is that a question for a different forum? Thanks again for all your help. "Peter Jamieson" wrote: There are two different limits: the total you can fit into the combined SQLStatement parameters, which is I think 510 chars, and the total that the provider/driver will actually accept, which depends partly on the version of Word and the type of driver/provider. Some don't allow more than about 255 altogether. I can't rember the details now. Happy new year! Peter Jamieson http://tips.pjmsn.me.uk Graywolf wrote: Thanks for the feedback Peter. I will try the registry change and let you know what happens. I didn't think I needed everything on the opendatasource line but like I said I just recorded a macro and cut a pasted the line into my VB code changing the arguments where needed. I know the SQL statement is restricted to about 250 chars. Is the second SQL argument restricted to the same length? That might give me problems if it is. Thanks again. Happy New Year!!! "Peter Jamieson" wrote: If you haven't made the registry change described in http://support.microsoft.com/kb/825765/en-us try that first. Everything else looks OK (I can't say I've checked in detail, but the SQL statement is only around 248 characters so it should fit into SQLStatement. However, Word does not always record the OpenDataSource statement correctly (e.g. it usually truncates the Connection string). You can also cut a lot of stuff out - I'd try: .OpenDataSource _ Name:=gsDBLocation, _ Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word="""";User ID=Admin;Data Source=gsDBLocation;Mode=Read;", _ SQLStatement:=msSQL, _ SubType:=wdMergeSubTypeAccess (you may be able to get away with .OpenDataSource _ Name:=gsDBLocation, _ SQLStatement:=msSQL ) -- Peter Jamieson http://tips.pjmsn.me.uk "Graywolf" wrote in message ... If this post should be in a different forum, please let me know. I'm trying to run a Word 2003 Mailmerge to a mailing label template. I've set up the Mailing label mailmerge and my datasource is a stored query in an Access database. Everything works fine with the mailmerge from Word. Now I want to run that mailmerge from a Visual Basic 6 program, but change the datasource. When I change the datasource I don't get anything coming out on the labels. I'm trying to set the datasource to a query statement that I build in the program. For my initial tests the Access database is the same and the query statement I build is identical to the stored query statement in the database. The fields that are returned in the queries will be identical, ultimately the where clause will change so it will select different records. But first I have to get the labels populated. When I run the code in VB and the new Word doc is created with the labels the labels are blank. Here's the code and the query statement. I recorded a Word macro to get the proper syntax for the Opendatasource command. gsDBLocation is the fully qualified path to the database. The msSQL variable holds the query statement. After opening the datasource, firstrecord returns a 1 and lastrecord returns a -16 (there should be 3 records) so it appears that the query is failing, but it's the same as the stored query (which works) so I'm stumped. Any help would be appreciated. Thanks in advance. Query: SELECT distinct REG.*, REGADDR.*, Address.* FROM REG INNER JOIN (Address INNER JOIN REGADDR ON Address.A_ADDRCODE = REGADDR.RA_ADDRCODE) ON REG.CODE = REGADDR.RA_REGCODE WHERE (((REGADDR.WIRE_NOTICE)=True)) or (((REGADDR.WIRE_NOTICE_DUP)=True)); Code: Set objWordApp = CreateObject("Word.application") 'New word.Application If chkLabels.Value = vbChecked Then Set objWordDoc = objWordApp.Documents.Open(C:\Labels5663.doc") End If objWordApp.Visible = vbTrue With objWordDoc.MailMerge .MainDocumentType = wdMailingLabels .SuppressBlankLines = True .OpenDataSource Name:=gsDBLocation, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:= _ "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Data Source=gsDBLocation;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:" _ , SQLStatement:=msSQL, SQLStatement1:="", _ SubType:=wdMergeSubTypeAccess objWordDoc.MailMerge.ViewMailMergeFieldCodes = vbTrue With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False End With |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merge Fields won't populate after Datasource change
Peter,
Sorry for the delay. I got moved to another project for a while, now I'm back. Tried the registry change with no affect on my results; I'm still not getting label output. When I step through the code in the debugger I can see my labels when I open the Word document because the template points to the predefined query in Access as the original datasource that was used when creating the label mailmerge. When I go to change the datasource by altering the SQLStatement argument of the OpenDataSource method, I lose my labels. Any other ideas? If I cannot find a resolution here, do you think it would be possible to change the SQL in the predefined query in Access? If I can, then, in theory I would only have to open the labels document and the altered predefined query would take care of itself. If you think this is possible, do you have any idea of how to change the sql statement in a predefined Access query? or is that a question for a different forum? Thanks again for all your help. "Peter Jamieson" wrote: There are two different limits: the total you can fit into the combined SQLStatement parameters, which is I think 510 chars, and the total that the provider/driver will actually accept, which depends partly on the version of Word and the type of driver/provider. Some don't allow more than about 255 altogether. I can't rember the details now. Happy new year! Peter Jamieson http://tips.pjmsn.me.uk Graywolf wrote: Thanks for the feedback Peter. I will try the registry change and let you know what happens. I didn't think I needed everything on the opendatasource line but like I said I just recorded a macro and cut a pasted the line into my VB code changing the arguments where needed. I know the SQL statement is restricted to about 250 chars. Is the second SQL argument restricted to the same length? That might give me problems if it is. Thanks again. Happy New Year!!! "Peter Jamieson" wrote: If you haven't made the registry change described in http://support.microsoft.com/kb/825765/en-us try that first. Everything else looks OK (I can't say I've checked in detail, but the SQL statement is only around 248 characters so it should fit into SQLStatement. However, Word does not always record the OpenDataSource statement correctly (e.g. it usually truncates the Connection string). You can also cut a lot of stuff out - I'd try: .OpenDataSource _ Name:=gsDBLocation, _ Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word="""";User ID=Admin;Data Source=gsDBLocation;Mode=Read;", _ SQLStatement:=msSQL, _ SubType:=wdMergeSubTypeAccess (you may be able to get away with .OpenDataSource _ Name:=gsDBLocation, _ SQLStatement:=msSQL ) -- Peter Jamieson http://tips.pjmsn.me.uk "Graywolf" wrote in message ... If this post should be in a different forum, please let me know. I'm trying to run a Word 2003 Mailmerge to a mailing label template. I've set up the Mailing label mailmerge and my datasource is a stored query in an Access database. Everything works fine with the mailmerge from Word. Now I want to run that mailmerge from a Visual Basic 6 program, but change the datasource. When I change the datasource I don't get anything coming out on the labels. I'm trying to set the datasource to a query statement that I build in the program. For my initial tests the Access database is the same and the query statement I build is identical to the stored query statement in the database. The fields that are returned in the queries will be identical, ultimately the where clause will change so it will select different records. But first I have to get the labels populated. When I run the code in VB and the new Word doc is created with the labels the labels are blank. Here's the code and the query statement. I recorded a Word macro to get the proper syntax for the Opendatasource command. gsDBLocation is the fully qualified path to the database. The msSQL variable holds the query statement. After opening the datasource, firstrecord returns a 1 and lastrecord returns a -16 (there should be 3 records) so it appears that the query is failing, but it's the same as the stored query (which works) so I'm stumped. Any help would be appreciated. Thanks in advance. Query: SELECT distinct REG.*, REGADDR.*, Address.* FROM REG INNER JOIN (Address INNER JOIN REGADDR ON Address.A_ADDRCODE = REGADDR.RA_ADDRCODE) ON REG.CODE = REGADDR.RA_REGCODE WHERE (((REGADDR.WIRE_NOTICE)=True)) or (((REGADDR.WIRE_NOTICE_DUP)=True)); Code: Set objWordApp = CreateObject("Word.application") 'New word.Application If chkLabels.Value = vbChecked Then Set objWordDoc = objWordApp.Documents.Open(C:\Labels5663.doc") End If objWordApp.Visible = vbTrue With objWordDoc.MailMerge .MainDocumentType = wdMailingLabels .SuppressBlankLines = True .OpenDataSource Name:=gsDBLocation, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:= _ "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Data Source=gsDBLocation;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:" _ , SQLStatement:=msSQL, SQLStatement1:="", _ SubType:=wdMergeSubTypeAccess objWordDoc.MailMerge.ViewMailMergeFieldCodes = vbTrue With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False End With |
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merge Fields won't populate after Datasource change
Peter,
Sorry for the delay. I got moved to another project for a while, now I'm back. Tried the registry change with no affect on my results; I'm still not getting label output. When I step through the code in the debugger I can see my labels when I open the Word document because the template points to the predefined query in Access as the original datasource that was used when creating the label mailmerge. When I go to change the datasource by altering the SQLStatement argument of the OpenDataSource method, I lose my labels. Any other ideas? If I cannot find a resolution here, do you think it would be possible to change the SQL in the predefined query in Access? If I can, then, in theory I would only have to open the labels document and the altered predefined query would take care of itself. If you think this is possible, do you have any idea of how to change the sql statement in a predefined Access query? or is that a question for a different forum? Thanks again for all your help. "Graywolf" wrote in message ... If this post should be in a different forum, please let me know. I'm trying to run a Word 2003 Mailmerge to a mailing label template. I've set up the Mailing label mailmerge and my datasource is a stored query in an Access database. Everything works fine with the mailmerge from Word. Now I want to run that mailmerge from a Visual Basic 6 program, but change the datasource. When I change the datasource I don't get anything coming out on the labels. I'm trying to set the datasource to a query statement that I build in the program. For my initial tests the Access database is the same and the query statement I build is identical to the stored query statement in the database. The fields that are returned in the queries will be identical, ultimately the where clause will change so it will select different records. But first I have to get the labels populated. When I run the code in VB and the new Word doc is created with the labels the labels are blank. Here's the code and the query statement. I recorded a Word macro to get the proper syntax for the Opendatasource command. gsDBLocation is the fully qualified path to the database. The msSQL variable holds the query statement. After opening the datasource, firstrecord returns a 1 and lastrecord returns a -16 (there should be 3 records) so it appears that the query is failing, but it's the same as the stored query (which works) so I'm stumped. Any help would be appreciated. Thanks in advance. Query: SELECT distinct REG.*, REGADDR.*, Address.* FROM REG INNER JOIN (Address INNER JOIN REGADDR ON Address.A_ADDRCODE = REGADDR.RA_ADDRCODE) ON REG.CODE = REGADDR.RA_REGCODE WHERE (((REGADDR.WIRE_NOTICE)=True)) or (((REGADDR.WIRE_NOTICE_DUP)=True)); Code: Set objWordApp = CreateObject("Word.application") 'New word.Application If chkLabels.Value = vbChecked Then Set objWordDoc = objWordApp.Documents.Open(C:\Labels5663.doc") End If objWordApp.Visible = vbTrue With objWordDoc.MailMerge .MainDocumentType = wdMailingLabels .SuppressBlankLines = True .OpenDataSource Name:=gsDBLocation, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:= _ "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Data Source=gsDBLocation;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:" _ , SQLStatement:=msSQL, SQLStatement1:="", _ SubType:=wdMergeSubTypeAccess objWordDoc.MailMerge.ViewMailMergeFieldCodes = vbTrue With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False End With |
#9
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merge Fields won't populate after Datasource change
Peter,
I've found that when I change the datasource I lose my mapped fields. Could that be the problem? If so, can you give me a clue as to a quick way to remap the fields? Thanks again, sorry for the multiple posts, had a browser problem and I thought I wasn't posting when apparently I was. "Graywolf" wrote: Peter, Sorry for the delay. Got pulled off this and put on another project for a while. Now I'm back to this. Tried making the registry change with no affect on my results; still getting blank labels. As I step through the code in the debugger when I open the Word label document with the following statement Set objWordDoc = objWordApp.Documents.Open("C:\Labels5663.doc") and then make it visible, I can see the 'labels' on screen in normal view because the document is set up to use the predefined query in the Access database as the datasource. When I go to change the datasource by setting the SQLStatement argument within the .OpenDataSource method I lose my labels. Do you have any other ideas? If I cannot find a resolution here, do you think it would be possible to alter the SQL statement in the predefined query in the Access table and then just open the mailmerge?? If so, any idea how I would go about changing the query in Access? or is that question for another forum? Thanks again for all your help. "Peter Jamieson" wrote: There are two different limits: the total you can fit into the combined SQLStatement parameters, which is I think 510 chars, and the total that the provider/driver will actually accept, which depends partly on the version of Word and the type of driver/provider. Some don't allow more than about 255 altogether. I can't rember the details now. Happy new year! Peter Jamieson http://tips.pjmsn.me.uk Graywolf wrote: Thanks for the feedback Peter. I will try the registry change and let you know what happens. I didn't think I needed everything on the opendatasource line but like I said I just recorded a macro and cut a pasted the line into my VB code changing the arguments where needed. I know the SQL statement is restricted to about 250 chars. Is the second SQL argument restricted to the same length? That might give me problems if it is. Thanks again. Happy New Year!!! "Peter Jamieson" wrote: If you haven't made the registry change described in http://support.microsoft.com/kb/825765/en-us try that first. Everything else looks OK (I can't say I've checked in detail, but the SQL statement is only around 248 characters so it should fit into SQLStatement. However, Word does not always record the OpenDataSource statement correctly (e.g. it usually truncates the Connection string). You can also cut a lot of stuff out - I'd try: .OpenDataSource _ Name:=gsDBLocation, _ Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word="""";User ID=Admin;Data Source=gsDBLocation;Mode=Read;", _ SQLStatement:=msSQL, _ SubType:=wdMergeSubTypeAccess (you may be able to get away with .OpenDataSource _ Name:=gsDBLocation, _ SQLStatement:=msSQL ) -- Peter Jamieson http://tips.pjmsn.me.uk "Graywolf" wrote in message ... If this post should be in a different forum, please let me know. I'm trying to run a Word 2003 Mailmerge to a mailing label template. I've set up the Mailing label mailmerge and my datasource is a stored query in an Access database. Everything works fine with the mailmerge from Word. Now I want to run that mailmerge from a Visual Basic 6 program, but change the datasource. When I change the datasource I don't get anything coming out on the labels. I'm trying to set the datasource to a query statement that I build in the program. For my initial tests the Access database is the same and the query statement I build is identical to the stored query statement in the database. The fields that are returned in the queries will be identical, ultimately the where clause will change so it will select different records. But first I have to get the labels populated. When I run the code in VB and the new Word doc is created with the labels the labels are blank. Here's the code and the query statement. I recorded a Word macro to get the proper syntax for the Opendatasource command. gsDBLocation is the fully qualified path to the database. The msSQL variable holds the query statement. After opening the datasource, firstrecord returns a 1 and lastrecord returns a -16 (there should be 3 records) so it appears that the query is failing, but it's the same as the stored query (which works) so I'm stumped. Any help would be appreciated. Thanks in advance. Query: SELECT distinct REG.*, REGADDR.*, Address.* FROM REG INNER JOIN (Address INNER JOIN REGADDR ON Address.A_ADDRCODE = REGADDR.RA_ADDRCODE) ON REG.CODE = REGADDR.RA_REGCODE WHERE (((REGADDR.WIRE_NOTICE)=True)) or (((REGADDR.WIRE_NOTICE_DUP)=True)); Code: Set objWordApp = CreateObject("Word.application") 'New word.Application If chkLabels.Value = vbChecked Then Set objWordDoc = objWordApp.Documents.Open(C:\Labels5663.doc") End If objWordApp.Visible = vbTrue With objWordDoc.MailMerge .MainDocumentType = wdMailingLabels .SuppressBlankLines = True .OpenDataSource Name:=gsDBLocation, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:= _ "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Data Source=gsDBLocation;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:" _ , SQLStatement:=msSQL, SQLStatement1:="", _ SubType:=wdMergeSubTypeAccess objWordDoc.MailMerge.ViewMailMergeFieldCodes = vbTrue With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False End With |
#10
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merge Fields won't populate after Datasource change
Access has a perfectly good utility for the production of labels. Why not
just use that and automate Access from your VB6 code? -- 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 "Graywolf" wrote in message ... Peter, I've found that when I change the datasource I lose my mapped fields. Could that be the problem? If so, can you give me a clue as to a quick way to remap the fields? Thanks again, sorry for the multiple posts, had a browser problem and I thought I wasn't posting when apparently I was. "Graywolf" wrote: Peter, Sorry for the delay. Got pulled off this and put on another project for a while. Now I'm back to this. Tried making the registry change with no affect on my results; still getting blank labels. As I step through the code in the debugger when I open the Word label document with the following statement Set objWordDoc = objWordApp.Documents.Open("C:\Labels5663.doc") and then make it visible, I can see the 'labels' on screen in normal view because the document is set up to use the predefined query in the Access database as the datasource. When I go to change the datasource by setting the SQLStatement argument within the .OpenDataSource method I lose my labels. Do you have any other ideas? If I cannot find a resolution here, do you think it would be possible to alter the SQL statement in the predefined query in the Access table and then just open the mailmerge?? If so, any idea how I would go about changing the query in Access? or is that question for another forum? Thanks again for all your help. "Peter Jamieson" wrote: There are two different limits: the total you can fit into the combined SQLStatement parameters, which is I think 510 chars, and the total that the provider/driver will actually accept, which depends partly on the version of Word and the type of driver/provider. Some don't allow more than about 255 altogether. I can't rember the details now. Happy new year! Peter Jamieson http://tips.pjmsn.me.uk Graywolf wrote: Thanks for the feedback Peter. I will try the registry change and let you know what happens. I didn't think I needed everything on the opendatasource line but like I said I just recorded a macro and cut a pasted the line into my VB code changing the arguments where needed. I know the SQL statement is restricted to about 250 chars. Is the second SQL argument restricted to the same length? That might give me problems if it is. Thanks again. Happy New Year!!! "Peter Jamieson" wrote: If you haven't made the registry change described in http://support.microsoft.com/kb/825765/en-us try that first. Everything else looks OK (I can't say I've checked in detail, but the SQL statement is only around 248 characters so it should fit into SQLStatement. However, Word does not always record the OpenDataSource statement correctly (e.g. it usually truncates the Connection string). You can also cut a lot of stuff out - I'd try: .OpenDataSource _ Name:=gsDBLocation, _ Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word="""";User ID=Admin;Data Source=gsDBLocation;Mode=Read;", _ SQLStatement:=msSQL, _ SubType:=wdMergeSubTypeAccess (you may be able to get away with .OpenDataSource _ Name:=gsDBLocation, _ SQLStatement:=msSQL ) -- Peter Jamieson http://tips.pjmsn.me.uk "Graywolf" wrote in message ... If this post should be in a different forum, please let me know. I'm trying to run a Word 2003 Mailmerge to a mailing label template. I've set up the Mailing label mailmerge and my datasource is a stored query in an Access database. Everything works fine with the mailmerge from Word. Now I want to run that mailmerge from a Visual Basic 6 program, but change the datasource. When I change the datasource I don't get anything coming out on the labels. I'm trying to set the datasource to a query statement that I build in the program. For my initial tests the Access database is the same and the query statement I build is identical to the stored query statement in the database. The fields that are returned in the queries will be identical, ultimately the where clause will change so it will select different records. But first I have to get the labels populated. When I run the code in VB and the new Word doc is created with the labels the labels are blank. Here's the code and the query statement. I recorded a Word macro to get the proper syntax for the Opendatasource command. gsDBLocation is the fully qualified path to the database. The msSQL variable holds the query statement. After opening the datasource, firstrecord returns a 1 and lastrecord returns a -16 (there should be 3 records) so it appears that the query is failing, but it's the same as the stored query (which works) so I'm stumped. Any help would be appreciated. Thanks in advance. Query: SELECT distinct REG.*, REGADDR.*, Address.* FROM REG INNER JOIN (Address INNER JOIN REGADDR ON Address.A_ADDRCODE = REGADDR.RA_ADDRCODE) ON REG.CODE = REGADDR.RA_REGCODE WHERE (((REGADDR.WIRE_NOTICE)=True)) or (((REGADDR.WIRE_NOTICE_DUP)=True)); Code: Set objWordApp = CreateObject("Word.application") 'New word.Application If chkLabels.Value = vbChecked Then Set objWordDoc = objWordApp.Documents.Open(C:\Labels5663.doc") End If objWordApp.Visible = vbTrue With objWordDoc.MailMerge .MainDocumentType = wdMailingLabels .SuppressBlankLines = True .OpenDataSource Name:=gsDBLocation, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:= _ "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Data Source=gsDBLocation;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:" _ , SQLStatement:=msSQL, SQLStatement1:="", _ SubType:=wdMergeSubTypeAccess objWordDoc.MailMerge.ViewMailMergeFieldCodes = vbTrue With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False End With |
#11
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merge Fields won't populate after Datasource change
One other problem is that you have
..OpenDataSource _ Name:=gsDBLocation, _ Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word="""";User ID=Admin;Data Source=gsDBLocation;Mode=Read;", _ SQLStatement:=msSQL, _ SubType:=wdMergeSubTypeAccess when you need ..OpenDataSource _ Name:=gsDBLocation, _ Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word="""";User ID=Admin;Data Source=" & gsDBLocation & ";Mode=Read;", _ SQLStatement:=msSQL, _ SubType:=wdMergeSubTypeAccess Sorry, missed that before. As for the mapped fields, if you still need to do that (and you probably will), have a look at http://support.microsoft.com/kb/282567 for guidance on the necessary code - it's slightly more awkward than it really needs to be. Peter Jamieson http://tips.pjmsn.me.uk Graywolf wrote: Peter, Sorry for the delay. Got pulled off this and put on another project for a while. Now I'm back to this. Tried making the registry change with no affect on my results; still getting blank labels. As I step through the code in the debugger when I open the Word label document with the following statement Set objWordDoc = objWordApp.Documents.Open("C:\Labels5663.doc") and then make it visible, I can see the 'labels' on screen in normal view because the document is set up to use the predefined query in the Access database as the datasource. When I go to change the datasource by setting the SQLStatement argument within the .OpenDataSource method I lose my labels. Do you have any other ideas? If I cannot find a resolution here, do you think it would be possible to alter the SQL statement in the predefined query in the Access table and then just open the mailmerge?? If so, any idea how I would go about changing the query in Access? or is that question for another forum? Thanks again for all your help. "Peter Jamieson" wrote: There are two different limits: the total you can fit into the combined SQLStatement parameters, which is I think 510 chars, and the total that the provider/driver will actually accept, which depends partly on the version of Word and the type of driver/provider. Some don't allow more than about 255 altogether. I can't rember the details now. Happy new year! Peter Jamieson http://tips.pjmsn.me.uk Graywolf wrote: Thanks for the feedback Peter. I will try the registry change and let you know what happens. I didn't think I needed everything on the opendatasource line but like I said I just recorded a macro and cut a pasted the line into my VB code changing the arguments where needed. I know the SQL statement is restricted to about 250 chars. Is the second SQL argument restricted to the same length? That might give me problems if it is. Thanks again. Happy New Year!!! "Peter Jamieson" wrote: If you haven't made the registry change described in http://support.microsoft.com/kb/825765/en-us try that first. Everything else looks OK (I can't say I've checked in detail, but the SQL statement is only around 248 characters so it should fit into SQLStatement. However, Word does not always record the OpenDataSource statement correctly (e.g. it usually truncates the Connection string). You can also cut a lot of stuff out - I'd try: .OpenDataSource _ Name:=gsDBLocation, _ Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word="""";User ID=Admin;Data Source=gsDBLocation;Mode=Read;", _ SQLStatement:=msSQL, _ SubType:=wdMergeSubTypeAccess (you may be able to get away with .OpenDataSource _ Name:=gsDBLocation, _ SQLStatement:=msSQL ) -- Peter Jamieson http://tips.pjmsn.me.uk "Graywolf" wrote in message ... If this post should be in a different forum, please let me know. I'm trying to run a Word 2003 Mailmerge to a mailing label template. I've set up the Mailing label mailmerge and my datasource is a stored query in an Access database. Everything works fine with the mailmerge from Word. Now I want to run that mailmerge from a Visual Basic 6 program, but change the datasource. When I change the datasource I don't get anything coming out on the labels. I'm trying to set the datasource to a query statement that I build in the program. For my initial tests the Access database is the same and the query statement I build is identical to the stored query statement in the database. The fields that are returned in the queries will be identical, ultimately the where clause will change so it will select different records. But first I have to get the labels populated. When I run the code in VB and the new Word doc is created with the labels the labels are blank. Here's the code and the query statement. I recorded a Word macro to get the proper syntax for the Opendatasource command. gsDBLocation is the fully qualified path to the database. The msSQL variable holds the query statement. After opening the datasource, firstrecord returns a 1 and lastrecord returns a -16 (there should be 3 records) so it appears that the query is failing, but it's the same as the stored query (which works) so I'm stumped. Any help would be appreciated. Thanks in advance. Query: SELECT distinct REG.*, REGADDR.*, Address.* FROM REG INNER JOIN (Address INNER JOIN REGADDR ON Address.A_ADDRCODE = REGADDR.RA_ADDRCODE) ON REG.CODE = REGADDR.RA_REGCODE WHERE (((REGADDR.WIRE_NOTICE)=True)) or (((REGADDR.WIRE_NOTICE_DUP)=True)); Code: Set objWordApp = CreateObject("Word.application") 'New word.Application If chkLabels.Value = vbChecked Then Set objWordDoc = objWordApp.Documents.Open(C:\Labels5663.doc") End If objWordApp.Visible = vbTrue With objWordDoc.MailMerge .MainDocumentType = wdMailingLabels .SuppressBlankLines = True .OpenDataSource Name:=gsDBLocation, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:= _ "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Data Source=gsDBLocation;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:" _ , SQLStatement:=msSQL, SQLStatement1:="", _ SubType:=wdMergeSubTypeAccess objWordDoc.MailMerge.ViewMailMergeFieldCodes = vbTrue With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False End With |
#12
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merge Fields won't populate after Datasource change
Thanks for the suggestion Doug. My end users are more comfortable with MS
Word than Access in case anything needs to be changed, so I would prefer to stay in Word; however, I may investigate your suggestion further if i can't get Word working the way I need. "Doug Robbins - Word MVP" wrote: Access has a perfectly good utility for the production of labels. Why not just use that and automate Access from your VB6 code? -- 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 "Graywolf" wrote in message ... Peter, I've found that when I change the datasource I lose my mapped fields. Could that be the problem? If so, can you give me a clue as to a quick way to remap the fields? Thanks again, sorry for the multiple posts, had a browser problem and I thought I wasn't posting when apparently I was. "Graywolf" wrote: Peter, Sorry for the delay. Got pulled off this and put on another project for a while. Now I'm back to this. Tried making the registry change with no affect on my results; still getting blank labels. As I step through the code in the debugger when I open the Word label document with the following statement Set objWordDoc = objWordApp.Documents.Open("C:\Labels5663.doc") and then make it visible, I can see the 'labels' on screen in normal view because the document is set up to use the predefined query in the Access database as the datasource. When I go to change the datasource by setting the SQLStatement argument within the .OpenDataSource method I lose my labels. Do you have any other ideas? If I cannot find a resolution here, do you think it would be possible to alter the SQL statement in the predefined query in the Access table and then just open the mailmerge?? If so, any idea how I would go about changing the query in Access? or is that question for another forum? Thanks again for all your help. "Peter Jamieson" wrote: There are two different limits: the total you can fit into the combined SQLStatement parameters, which is I think 510 chars, and the total that the provider/driver will actually accept, which depends partly on the version of Word and the type of driver/provider. Some don't allow more than about 255 altogether. I can't rember the details now. Happy new year! Peter Jamieson http://tips.pjmsn.me.uk Graywolf wrote: Thanks for the feedback Peter. I will try the registry change and let you know what happens. I didn't think I needed everything on the opendatasource line but like I said I just recorded a macro and cut a pasted the line into my VB code changing the arguments where needed. I know the SQL statement is restricted to about 250 chars. Is the second SQL argument restricted to the same length? That might give me problems if it is. Thanks again. Happy New Year!!! "Peter Jamieson" wrote: If you haven't made the registry change described in http://support.microsoft.com/kb/825765/en-us try that first. Everything else looks OK (I can't say I've checked in detail, but the SQL statement is only around 248 characters so it should fit into SQLStatement. However, Word does not always record the OpenDataSource statement correctly (e.g. it usually truncates the Connection string). You can also cut a lot of stuff out - I'd try: .OpenDataSource _ Name:=gsDBLocation, _ Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word="""";User ID=Admin;Data Source=gsDBLocation;Mode=Read;", _ SQLStatement:=msSQL, _ SubType:=wdMergeSubTypeAccess (you may be able to get away with .OpenDataSource _ Name:=gsDBLocation, _ SQLStatement:=msSQL ) -- Peter Jamieson http://tips.pjmsn.me.uk "Graywolf" wrote in message ... If this post should be in a different forum, please let me know. I'm trying to run a Word 2003 Mailmerge to a mailing label template. I've set up the Mailing label mailmerge and my datasource is a stored query in an Access database. Everything works fine with the mailmerge from Word. Now I want to run that mailmerge from a Visual Basic 6 program, but change the datasource. When I change the datasource I don't get anything coming out on the labels. I'm trying to set the datasource to a query statement that I build in the program. For my initial tests the Access database is the same and the query statement I build is identical to the stored query statement in the database. The fields that are returned in the queries will be identical, ultimately the where clause will change so it will select different records. But first I have to get the labels populated. When I run the code in VB and the new Word doc is created with the labels the labels are blank. Here's the code and the query statement. I recorded a Word macro to get the proper syntax for the Opendatasource command. gsDBLocation is the fully qualified path to the database. The msSQL variable holds the query statement. After opening the datasource, firstrecord returns a 1 and lastrecord returns a -16 (there should be 3 records) so it appears that the query is failing, but it's the same as the stored query (which works) so I'm stumped. Any help would be appreciated. Thanks in advance. Query: SELECT distinct REG.*, REGADDR.*, Address.* FROM REG INNER JOIN (Address INNER JOIN REGADDR ON Address.A_ADDRCODE = REGADDR.RA_ADDRCODE) ON REG.CODE = REGADDR.RA_REGCODE WHERE (((REGADDR.WIRE_NOTICE)=True)) or (((REGADDR.WIRE_NOTICE_DUP)=True)); Code: Set objWordApp = CreateObject("Word.application") 'New word.Application If chkLabels.Value = vbChecked Then Set objWordDoc = objWordApp.Documents.Open(C:\Labels5663.doc") End If objWordApp.Visible = vbTrue With objWordDoc.MailMerge .MainDocumentType = wdMailingLabels .SuppressBlankLines = True .OpenDataSource Name:=gsDBLocation, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:= _ "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Data Source=gsDBLocation;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:" _ , SQLStatement:=msSQL, SQLStatement1:="", _ SubType:=wdMergeSubTypeAccess objWordDoc.MailMerge.ViewMailMergeFieldCodes = vbTrue With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False End With |
#13
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merge Fields won't populate after Datasource change
Peter,
Thanks for catching that punctuation/syntax error. I've made the correction to that and it looks like I am changing datasources correctly now. However, I still am not retaining my mapped fields. The link to the knowledgebase article shows how to return the name of a mapped field or how to return the data value held in the mapped field once the merge is complete. However, it does not tell you how to programmatically set the field mappings; and if I'm reading the object browser in VB correctly for the MailMerge class in the Word library for MappedDataFields, the properties are almost all 'readonly' which would mean I can't set a mapped data field. The DataFieldIndex property of MappedDataField and the Item Function of MappedDataFields collection are not marked as Readonly. Any idea on how to use them to set a data field mapping? "Peter Jamieson" wrote: One other problem is that you have ..OpenDataSource _ Name:=gsDBLocation, _ Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word="""";User ID=Admin;Data Source=gsDBLocation;Mode=Read;", _ SQLStatement:=msSQL, _ SubType:=wdMergeSubTypeAccess when you need ..OpenDataSource _ Name:=gsDBLocation, _ Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word="""";User ID=Admin;Data Source=" & gsDBLocation & ";Mode=Read;", _ SQLStatement:=msSQL, _ SubType:=wdMergeSubTypeAccess Sorry, missed that before. As for the mapped fields, if you still need to do that (and you probably will), have a look at http://support.microsoft.com/kb/282567 for guidance on the necessary code - it's slightly more awkward than it really needs to be. Peter Jamieson http://tips.pjmsn.me.uk Graywolf wrote: Peter, Sorry for the delay. Got pulled off this and put on another project for a while. Now I'm back to this. Tried making the registry change with no affect on my results; still getting blank labels. As I step through the code in the debugger when I open the Word label document with the following statement Set objWordDoc = objWordApp.Documents.Open("C:\Labels5663.doc") and then make it visible, I can see the 'labels' on screen in normal view because the document is set up to use the predefined query in the Access database as the datasource. When I go to change the datasource by setting the SQLStatement argument within the .OpenDataSource method I lose my labels. Do you have any other ideas? If I cannot find a resolution here, do you think it would be possible to alter the SQL statement in the predefined query in the Access table and then just open the mailmerge?? If so, any idea how I would go about changing the query in Access? or is that question for another forum? Thanks again for all your help. "Peter Jamieson" wrote: There are two different limits: the total you can fit into the combined SQLStatement parameters, which is I think 510 chars, and the total that the provider/driver will actually accept, which depends partly on the version of Word and the type of driver/provider. Some don't allow more than about 255 altogether. I can't rember the details now. Happy new year! Peter Jamieson http://tips.pjmsn.me.uk Graywolf wrote: Thanks for the feedback Peter. I will try the registry change and let you know what happens. I didn't think I needed everything on the opendatasource line but like I said I just recorded a macro and cut a pasted the line into my VB code changing the arguments where needed. I know the SQL statement is restricted to about 250 chars. Is the second SQL argument restricted to the same length? That might give me problems if it is. Thanks again. Happy New Year!!! "Peter Jamieson" wrote: If you haven't made the registry change described in http://support.microsoft.com/kb/825765/en-us try that first. Everything else looks OK (I can't say I've checked in detail, but the SQL statement is only around 248 characters so it should fit into SQLStatement. However, Word does not always record the OpenDataSource statement correctly (e.g. it usually truncates the Connection string). You can also cut a lot of stuff out - I'd try: .OpenDataSource _ Name:=gsDBLocation, _ Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word="""";User ID=Admin;Data Source=gsDBLocation;Mode=Read;", _ SQLStatement:=msSQL, _ SubType:=wdMergeSubTypeAccess (you may be able to get away with .OpenDataSource _ Name:=gsDBLocation, _ SQLStatement:=msSQL ) -- Peter Jamieson http://tips.pjmsn.me.uk "Graywolf" wrote in message ... If this post should be in a different forum, please let me know. I'm trying to run a Word 2003 Mailmerge to a mailing label template. I've set up the Mailing label mailmerge and my datasource is a stored query in an Access database. Everything works fine with the mailmerge from Word. Now I want to run that mailmerge from a Visual Basic 6 program, but change the datasource. When I change the datasource I don't get anything coming out on the labels. I'm trying to set the datasource to a query statement that I build in the program. For my initial tests the Access database is the same and the query statement I build is identical to the stored query statement in the database. The fields that are returned in the queries will be identical, ultimately the where clause will change so it will select different records. But first I have to get the labels populated. When I run the code in VB and the new Word doc is created with the labels the labels are blank. Here's the code and the query statement. I recorded a Word macro to get the proper syntax for the Opendatasource command. gsDBLocation is the fully qualified path to the database. The msSQL variable holds the query statement. After opening the datasource, firstrecord returns a 1 and lastrecord returns a -16 (there should be 3 records) so it appears that the query is failing, but it's the same as the stored query (which works) so I'm stumped. Any help would be appreciated. Thanks in advance. Query: SELECT distinct REG.*, REGADDR.*, Address.* FROM REG INNER JOIN (Address INNER JOIN REGADDR ON Address.A_ADDRCODE = REGADDR.RA_ADDRCODE) ON REG.CODE = REGADDR.RA_REGCODE WHERE (((REGADDR.WIRE_NOTICE)=True)) or (((REGADDR.WIRE_NOTICE_DUP)=True)); Code: Set objWordApp = CreateObject("Word.application") 'New word.Application If chkLabels.Value = vbChecked Then Set objWordDoc = objWordApp.Documents.Open(C:\Labels5663.doc") End If objWordApp.Visible = vbTrue With objWordDoc.MailMerge .MainDocumentType = wdMailingLabels .SuppressBlankLines = True .OpenDataSource Name:=gsDBLocation, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:= _ "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Data Source=gsDBLocation;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:" _ , SQLStatement:=msSQL, SQLStatement1:="", _ SubType:=wdMergeSubTypeAccess objWordDoc.MailMerge.ViewMailMergeFieldCodes = vbTrue With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False End With |
#14
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Merge Fields won't populate after Datasource change
Yes, you have to assign the values indirectly. I'm not quite sure
whether you would be able to get the existing mappings before opening the new data source, but if not you can probably adapt the following example... Sub Macro4() ' ' Try to remap field names after a data source change ' ' Dim i As Integer Dim intMappedFieldIndexes() As Integer Dim strDataFieldNames() As String Dim objMappedDataField As Word.MappedDataField ' Assume we already have a data source - save the mapped field names With ActiveDocument.MailMerge ReDim strDataFieldNames(.DataSource.MappedDataFields.Cou nt) As String For i = 1 To .DataSource.MappedDataFields.Count strDataFieldNames(i) = .DataSource.MappedDataFields(i).DataFieldName Next ' disconnect the existing data source .MainDocumentType = wdNotAMergeDocument ' you'll need an OpenDataSource that works here... .OpenDataSource whatever ' If the old field name is not in the new source, ignore the problem On Error Resume Next ' map the names by finding the /index/ of the name in Datafields() For i = 1 To .DataSource.MappedDataFields.Count .DataSource.MappedDataFields(i).DataFieldIndex _ = .DataSource.DataFields(strDataFieldNames(i)).Index Next Err.Clear End With End Sub Peter Jamieson http://tips.pjmsn.me.uk Graywolf wrote: Peter, Thanks for catching that punctuation/syntax error. I've made the correction to that and it looks like I am changing datasources correctly now. However, I still am not retaining my mapped fields. The link to the knowledgebase article shows how to return the name of a mapped field or how to return the data value held in the mapped field once the merge is complete. However, it does not tell you how to programmatically set the field mappings; and if I'm reading the object browser in VB correctly for the MailMerge class in the Word library for MappedDataFields, the properties are almost all 'readonly' which would mean I can't set a mapped data field. The DataFieldIndex property of MappedDataField and the Item Function of MappedDataFields collection are not marked as Readonly. Any idea on how to use them to set a data field mapping? "Peter Jamieson" wrote: One other problem is that you have ..OpenDataSource _ Name:=gsDBLocation, _ Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word="""";User ID=Admin;Data Source=gsDBLocation;Mode=Read;", _ SQLStatement:=msSQL, _ SubType:=wdMergeSubTypeAccess when you need ..OpenDataSource _ Name:=gsDBLocation, _ Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word="""";User ID=Admin;Data Source=" & gsDBLocation & ";Mode=Read;", _ SQLStatement:=msSQL, _ SubType:=wdMergeSubTypeAccess Sorry, missed that before. As for the mapped fields, if you still need to do that (and you probably will), have a look at http://support.microsoft.com/kb/282567 for guidance on the necessary code - it's slightly more awkward than it really needs to be. Peter Jamieson http://tips.pjmsn.me.uk Graywolf wrote: Peter, Sorry for the delay. Got pulled off this and put on another project for a while. Now I'm back to this. Tried making the registry change with no affect on my results; still getting blank labels. As I step through the code in the debugger when I open the Word label document with the following statement Set objWordDoc = objWordApp.Documents.Open("C:\Labels5663.doc") and then make it visible, I can see the 'labels' on screen in normal view because the document is set up to use the predefined query in the Access database as the datasource. When I go to change the datasource by setting the SQLStatement argument within the .OpenDataSource method I lose my labels. Do you have any other ideas? If I cannot find a resolution here, do you think it would be possible to alter the SQL statement in the predefined query in the Access table and then just open the mailmerge?? If so, any idea how I would go about changing the query in Access? or is that question for another forum? Thanks again for all your help. "Peter Jamieson" wrote: There are two different limits: the total you can fit into the combined SQLStatement parameters, which is I think 510 chars, and the total that the provider/driver will actually accept, which depends partly on the version of Word and the type of driver/provider. Some don't allow more than about 255 altogether. I can't rember the details now. Happy new year! Peter Jamieson http://tips.pjmsn.me.uk Graywolf wrote: Thanks for the feedback Peter. I will try the registry change and let you know what happens. I didn't think I needed everything on the opendatasource line but like I said I just recorded a macro and cut a pasted the line into my VB code changing the arguments where needed. I know the SQL statement is restricted to about 250 chars. Is the second SQL argument restricted to the same length? That might give me problems if it is. Thanks again. Happy New Year!!! "Peter Jamieson" wrote: If you haven't made the registry change described in http://support.microsoft.com/kb/825765/en-us try that first. Everything else looks OK (I can't say I've checked in detail, but the SQL statement is only around 248 characters so it should fit into SQLStatement. However, Word does not always record the OpenDataSource statement correctly (e.g. it usually truncates the Connection string). You can also cut a lot of stuff out - I'd try: .OpenDataSource _ Name:=gsDBLocation, _ Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word="""";User ID=Admin;Data Source=gsDBLocation;Mode=Read;", _ SQLStatement:=msSQL, _ SubType:=wdMergeSubTypeAccess (you may be able to get away with .OpenDataSource _ Name:=gsDBLocation, _ SQLStatement:=msSQL ) -- Peter Jamieson http://tips.pjmsn.me.uk "Graywolf" wrote in message ... If this post should be in a different forum, please let me know. I'm trying to run a Word 2003 Mailmerge to a mailing label template. I've set up the Mailing label mailmerge and my datasource is a stored query in an Access database. Everything works fine with the mailmerge from Word. Now I want to run that mailmerge from a Visual Basic 6 program, but change the datasource. When I change the datasource I don't get anything coming out on the labels. I'm trying to set the datasource to a query statement that I build in the program. For my initial tests the Access database is the same and the query statement I build is identical to the stored query statement in the database. The fields that are returned in the queries will be identical, ultimately the where clause will change so it will select different records. But first I have to get the labels populated. When I run the code in VB and the new Word doc is created with the labels the labels are blank. Here's the code and the query statement. I recorded a Word macro to get the proper syntax for the Opendatasource command. gsDBLocation is the fully qualified path to the database. The msSQL variable holds the query statement. After opening the datasource, firstrecord returns a 1 and lastrecord returns a -16 (there should be 3 records) so it appears that the query is failing, but it's the same as the stored query (which works) so I'm stumped. Any help would be appreciated. Thanks in advance. Query: SELECT distinct REG.*, REGADDR.*, Address.* FROM REG INNER JOIN (Address INNER JOIN REGADDR ON Address.A_ADDRCODE = REGADDR.RA_ADDRCODE) ON REG.CODE = REGADDR.RA_REGCODE WHERE (((REGADDR.WIRE_NOTICE)=True)) or (((REGADDR.WIRE_NOTICE_DUP)=True)); Code: Set objWordApp = CreateObject("Word.application") 'New word.Application If chkLabels.Value = vbChecked Then Set objWordDoc = objWordApp.Documents.Open(C:\Labels5663.doc") End If objWordApp.Visible = vbTrue With objWordDoc.MailMerge .MainDocumentType = wdMailingLabels .SuppressBlankLines = True .OpenDataSource Name:=gsDBLocation, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:= _ "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Data Source=gsDBLocation;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:" _ , SQLStatement:=msSQL, SQLStatement1:="", _ SubType:=wdMergeSubTypeAccess objWordDoc.MailMerge.ViewMailMergeFieldCodes = vbTrue With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False End With |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fields to populate a document | Microsoft Word Help | |||
CHange query/datasource mail merge is bound to | Mailmerge | |||
Populate Merge Fields via VB.NET | Mailmerge | |||
How do I use unique fields to populate mail merge data like Word 2 | Mailmerge | |||
Merge fields in word tables populate by a VBA query | Tables |