Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Macro For Merge and Filter
I recently acquired Office 2003 Professional and have been trying to transfer
VB code from an older version of Office. A thread in this discussion group helped me to get it running manually (many thanks) but the code still doesn't work. I suspect it's the DDE conversion for the Excel worksheet because when I record the steps in a macro and play it back I get a runtime error when the program hits the filtering command. How do I get the conversion to take place in Visual Basic code? -- Bob Collin |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Macro For Merge and Filter
1. Which version of Word/Office were you running before?
2. Do you mean VB or VBA? 3. Does your VB app. set up the mail merge data source (i.e. using OpenDataSource) or is it already set up in the document, and are you just setting MailMerge.DataSource.QueryString to apply the filtering? 4. Do you /want/ your connection to be via DDE or would you prefer it to be via OLEDB (say) if that is a feasible option? Apart from everything else, you will almost certainly need to apply the following KB article unless you have had to do it befo http://support.microsoft.com/?kbid=825765 Can you please post your OpenDataSource code (if any)? Peter Jamieson "Bob Collin" wrote in message ... I recently acquired Office 2003 Professional and have been trying to transfer VB code from an older version of Office. A thread in this discussion group helped me to get it running manually (many thanks) but the code still doesn't work. I suspect it's the DDE conversion for the Excel worksheet because when I record the steps in a macro and play it back I get a runtime error when the program hits the filtering command. How do I get the conversion to take place in Visual Basic code? -- Bob Collin |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Macro For Merge and Filter
Here are the answers to your questions:
1. The old versions are Word 2000 and Excel 2000. The new versions are in Office 2003. 2. VBA 3. 'OpenDataSource' is used followed by 'Query'. 4. I would prefer to use the DDE conversion to keep the Office 2003 consistent with the the 2000 version. Baically I am trying to set up the merge system at home(on Office 2003) so that I can make modifications in the code that I can then bring into the version that is in use in the office (Office 2000). 5. The code arouond the merge and filter operations is as follows: ActiveDocument.MailMerge.OpenDataSource Name:= _ "C:\Documents and Settings\Owner\My Documents\My Documents\Red Cross\Shelters\Work Area\MasterForMerge.xls" _ , ConfirmConversions:=True, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:="Entire Spreadsheet", SQLStatement _ :="", SQLStatement1:="", SubType:=wdMergeSubTypeOther ActiveDocument.MailMerge.DataSource.QueryString = _ "SELECT * FROM C:\Documents and Settings\Owner\My Documents\My Documents\Red Cross\Shelters\Work Area\MasterForMerge.xls WHERE ((ACTIVE__INACTIVE = 'ACTIVE') AND (TYPE_________CTcountyCOCooperativeFO = 'SD') AND (RENEWAL_MAILED IS NULL ) AND (UPDATE_STATUS" _ & " = 'UPDATE NOW'))" When I try to run this I get a runtime error 4198. Bob Collin "Peter Jamieson" wrote: 1. Which version of Word/Office were you running before? 2. Do you mean VB or VBA? 3. Does your VB app. set up the mail merge data source (i.e. using OpenDataSource) or is it already set up in the document, and are you just setting MailMerge.DataSource.QueryString to apply the filtering? 4. Do you /want/ your connection to be via DDE or would you prefer it to be via OLEDB (say) if that is a feasible option? Apart from everything else, you will almost certainly need to apply the following KB article unless you have had to do it befo http://support.microsoft.com/?kbid=825765 Can you please post your OpenDataSource code (if any)? Peter Jamieson "Bob Collin" wrote in message ... I recently acquired Office 2003 Professional and have been trying to transfer VB code from an older version of Office. A thread in this discussion group helped me to get it running manually (many thanks) but the code still doesn't work. I suspect it's the DDE conversion for the Excel worksheet because when I record the steps in a macro and play it back I get a runtime error when the program hits the filtering command. How do I get the conversion to take place in Visual Basic code? -- Bob Collin |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Macro For Merge and Filter
Hi Bob,
you will need to do/check this, as mentioned befo Apart from everything else, you will almost certainly need to apply the following KB article unless you have had to do it befo http://support.microsoft.com/?kbid=825765 I think you will need to change your OpenDataSource - off the top of my head, you will probably need: ActiveDocument.MailMerge.OpenDataSource _ Name:="C:\Documents and Settings\Owner\My Documents\My Documents\Red Cross\Shelters\Work Area\MasterForMerge.xls", _ Connection:="Entire Spreadsheet", _ SQLStatement:="", _ SubType:=wdMergeSubTypeWord2000 to open using DDE. Word should use the SQL statement SELECT * FROM C:\Documents and Settings\Owner\My Documents\My Documents\Red Cross\Shelters\Work Area\MasterForMerge.xls at that point. You should either be able to use exactly the same QueryString assignment as you had before, or put the same query in the SQLStatement parameter of the OpenDataSource if you prefer (if the SQL is longer than 255 characters, split it in two and put the first part in SQLStatement and the second in SQLStatment1 so that the two strngs concatenated are exactly the SQL string you want (i.e. be careful not to add/delete significant spaces). Peter Jamieson "Bob Collin" wrote in message ... Here are the answers to your questions: 1. The old versions are Word 2000 and Excel 2000. The new versions are in Office 2003. 2. VBA 3. 'OpenDataSource' is used followed by 'Query'. 4. I would prefer to use the DDE conversion to keep the Office 2003 consistent with the the 2000 version. Baically I am trying to set up the merge system at home(on Office 2003) so that I can make modifications in the code that I can then bring into the version that is in use in the office (Office 2000). 5. The code arouond the merge and filter operations is as follows: ActiveDocument.MailMerge.OpenDataSource Name:= _ "C:\Documents and Settings\Owner\My Documents\My Documents\Red Cross\Shelters\Work Area\MasterForMerge.xls" _ , ConfirmConversions:=True, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:="Entire Spreadsheet", SQLStatement _ :="", SQLStatement1:="", SubType:=wdMergeSubTypeOther ActiveDocument.MailMerge.DataSource.QueryString = _ "SELECT * FROM C:\Documents and Settings\Owner\My Documents\My Documents\Red Cross\Shelters\Work Area\MasterForMerge.xls WHERE ((ACTIVE__INACTIVE = 'ACTIVE') AND (TYPE_________CTcountyCOCooperativeFO = 'SD') AND (RENEWAL_MAILED IS NULL ) AND (UPDATE_STATUS" _ & " = 'UPDATE NOW'))" When I try to run this I get a runtime error 4198. Bob Collin "Peter Jamieson" wrote: 1. Which version of Word/Office were you running before? 2. Do you mean VB or VBA? 3. Does your VB app. set up the mail merge data source (i.e. using OpenDataSource) or is it already set up in the document, and are you just setting MailMerge.DataSource.QueryString to apply the filtering? 4. Do you /want/ your connection to be via DDE or would you prefer it to be via OLEDB (say) if that is a feasible option? Apart from everything else, you will almost certainly need to apply the following KB article unless you have had to do it befo http://support.microsoft.com/?kbid=825765 Can you please post your OpenDataSource code (if any)? Peter Jamieson "Bob Collin" wrote in message ... I recently acquired Office 2003 Professional and have been trying to transfer VB code from an older version of Office. A thread in this discussion group helped me to get it running manually (many thanks) but the code still doesn't work. I suspect it's the DDE conversion for the Excel worksheet because when I record the steps in a macro and play it back I get a runtime error when the program hits the filtering command. How do I get the conversion to take place in Visual Basic code? -- Bob Collin |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Macro For Merge and Filter
I read the article you recommended. It scared me off from the DDE approach so
now I'll look into the other way you mentioned (OLEDB) and probably get back after I've studied up a bit. Thanks for your help -- Bob Collin "Peter Jamieson" wrote: Hi Bob, you will need to do/check this, as mentioned befo Apart from everything else, you will almost certainly need to apply the following KB article unless you have had to do it befo http://support.microsoft.com/?kbid=825765 I think you will need to change your OpenDataSource - off the top of my head, you will probably need: ActiveDocument.MailMerge.OpenDataSource _ Name:="C:\Documents and Settings\Owner\My Documents\My Documents\Red Cross\Shelters\Work Area\MasterForMerge.xls", _ Connection:="Entire Spreadsheet", _ SQLStatement:="", _ SubType:=wdMergeSubTypeWord2000 to open using DDE. Word should use the SQL statement SELECT * FROM C:\Documents and Settings\Owner\My Documents\My Documents\Red Cross\Shelters\Work Area\MasterForMerge.xls at that point. You should either be able to use exactly the same QueryString assignment as you had before, or put the same query in the SQLStatement parameter of the OpenDataSource if you prefer (if the SQL is longer than 255 characters, split it in two and put the first part in SQLStatement and the second in SQLStatment1 so that the two strngs concatenated are exactly the SQL string you want (i.e. be careful not to add/delete significant spaces). Peter Jamieson "Bob Collin" wrote in message ... Here are the answers to your questions: 1. The old versions are Word 2000 and Excel 2000. The new versions are in Office 2003. 2. VBA 3. 'OpenDataSource' is used followed by 'Query'. 4. I would prefer to use the DDE conversion to keep the Office 2003 consistent with the the 2000 version. Baically I am trying to set up the merge system at home(on Office 2003) so that I can make modifications in the code that I can then bring into the version that is in use in the office (Office 2000). 5. The code arouond the merge and filter operations is as follows: ActiveDocument.MailMerge.OpenDataSource Name:= _ "C:\Documents and Settings\Owner\My Documents\My Documents\Red Cross\Shelters\Work Area\MasterForMerge.xls" _ , ConfirmConversions:=True, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:="Entire Spreadsheet", SQLStatement _ :="", SQLStatement1:="", SubType:=wdMergeSubTypeOther ActiveDocument.MailMerge.DataSource.QueryString = _ "SELECT * FROM C:\Documents and Settings\Owner\My Documents\My Documents\Red Cross\Shelters\Work Area\MasterForMerge.xls WHERE ((ACTIVE__INACTIVE = 'ACTIVE') AND (TYPE_________CTcountyCOCooperativeFO = 'SD') AND (RENEWAL_MAILED IS NULL ) AND (UPDATE_STATUS" _ & " = 'UPDATE NOW'))" When I try to run this I get a runtime error 4198. Bob Collin "Peter Jamieson" wrote: 1. Which version of Word/Office were you running before? 2. Do you mean VB or VBA? 3. Does your VB app. set up the mail merge data source (i.e. using OpenDataSource) or is it already set up in the document, and are you just setting MailMerge.DataSource.QueryString to apply the filtering? 4. Do you /want/ your connection to be via DDE or would you prefer it to be via OLEDB (say) if that is a feasible option? Apart from everything else, you will almost certainly need to apply the following KB article unless you have had to do it befo http://support.microsoft.com/?kbid=825765 Can you please post your OpenDataSource code (if any)? Peter Jamieson "Bob Collin" wrote in message ... I recently acquired Office 2003 Professional and have been trying to transfer VB code from an older version of Office. A thread in this discussion group helped me to get it running manually (many thanks) but the code still doesn't work. I suspect it's the DDE conversion for the Excel worksheet because when I record the steps in a macro and play it back I get a runtime error when the program hits the filtering command. How do I get the conversion to take place in Visual Basic code? -- Bob Collin |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Macro For Merge and Filter
Hi Bob, unfortunately the article is relevant whichever connection method
you use - the trouble is that any VBA OpenDataSource will fail if you don't apply it. Peter Jamieson "Bob Collin" wrote in message ... I read the article you recommended. It scared me off from the DDE approach so now I'll look into the other way you mentioned (OLEDB) and probably get back after I've studied up a bit. Thanks for your help -- Bob Collin "Peter Jamieson" wrote: Hi Bob, you will need to do/check this, as mentioned befo Apart from everything else, you will almost certainly need to apply the following KB article unless you have had to do it befo http://support.microsoft.com/?kbid=825765 I think you will need to change your OpenDataSource - off the top of my head, you will probably need: ActiveDocument.MailMerge.OpenDataSource _ Name:="C:\Documents and Settings\Owner\My Documents\My Documents\Red Cross\Shelters\Work Area\MasterForMerge.xls", _ Connection:="Entire Spreadsheet", _ SQLStatement:="", _ SubType:=wdMergeSubTypeWord2000 to open using DDE. Word should use the SQL statement SELECT * FROM C:\Documents and Settings\Owner\My Documents\My Documents\Red Cross\Shelters\Work Area\MasterForMerge.xls at that point. You should either be able to use exactly the same QueryString assignment as you had before, or put the same query in the SQLStatement parameter of the OpenDataSource if you prefer (if the SQL is longer than 255 characters, split it in two and put the first part in SQLStatement and the second in SQLStatment1 so that the two strngs concatenated are exactly the SQL string you want (i.e. be careful not to add/delete significant spaces). Peter Jamieson "Bob Collin" wrote in message ... Here are the answers to your questions: 1. The old versions are Word 2000 and Excel 2000. The new versions are in Office 2003. 2. VBA 3. 'OpenDataSource' is used followed by 'Query'. 4. I would prefer to use the DDE conversion to keep the Office 2003 consistent with the the 2000 version. Baically I am trying to set up the merge system at home(on Office 2003) so that I can make modifications in the code that I can then bring into the version that is in use in the office (Office 2000). 5. The code arouond the merge and filter operations is as follows: ActiveDocument.MailMerge.OpenDataSource Name:= _ "C:\Documents and Settings\Owner\My Documents\My Documents\Red Cross\Shelters\Work Area\MasterForMerge.xls" _ , ConfirmConversions:=True, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:="Entire Spreadsheet", SQLStatement _ :="", SQLStatement1:="", SubType:=wdMergeSubTypeOther ActiveDocument.MailMerge.DataSource.QueryString = _ "SELECT * FROM C:\Documents and Settings\Owner\My Documents\My Documents\Red Cross\Shelters\Work Area\MasterForMerge.xls WHERE ((ACTIVE__INACTIVE = 'ACTIVE') AND (TYPE_________CTcountyCOCooperativeFO = 'SD') AND (RENEWAL_MAILED IS NULL ) AND (UPDATE_STATUS" _ & " = 'UPDATE NOW'))" When I try to run this I get a runtime error 4198. Bob Collin "Peter Jamieson" wrote: 1. Which version of Word/Office were you running before? 2. Do you mean VB or VBA? 3. Does your VB app. set up the mail merge data source (i.e. using OpenDataSource) or is it already set up in the document, and are you just setting MailMerge.DataSource.QueryString to apply the filtering? 4. Do you /want/ your connection to be via DDE or would you prefer it to be via OLEDB (say) if that is a feasible option? Apart from everything else, you will almost certainly need to apply the following KB article unless you have had to do it befo http://support.microsoft.com/?kbid=825765 Can you please post your OpenDataSource code (if any)? Peter Jamieson "Bob Collin" wrote in message ... I recently acquired Office 2003 Professional and have been trying to transfer VB code from an older version of Office. A thread in this discussion group helped me to get it running manually (many thanks) but the code still doesn't work. I suspect it's the DDE conversion for the Excel worksheet because when I record the steps in a macro and play it back I get a runtime error when the program hits the filtering command. How do I get the conversion to take place in Visual Basic code? -- Bob Collin |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
One of my MAIN menu's is missing an item -- namely "FILE" | Microsoft Word Help | |||
Word should filter revision marks: show marks after given date. | Microsoft Word Help | |||
Advanced Filter Won't Unfilter | Mailmerge | |||
Mailmerge by macro | Mailmerge | |||
Need DXF filter for Office 2000, Word | Microsoft Word Help |