Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Mailmerge by macro
I am using word and excel 2002. I filter and save data in an excel worksheet.
When I open it from the mailmerge toolbar and click on mailmerge recipients I need to filter again to loose the blank rows, I also sort 2 other columns. This gives me the labels required. But I want to run it on a macro. The macro wont filter or sort, it gives me my labels plus three hundred pages of empty labels. Looking at the macro to edit it, there is a gap where the filter and sort should be. I dont know how to write (fill in) the VBA to produce the required result. |
#2
|
|||
|
|||
Word does filtering in two ways:
a. when you pick individual records in the mail merge Recipients dialog box, Word just marks individual records as included or amitted in its internal list b. when you apply sorts and filters, Word generates a new SQL command and re-queries the data source. You almost certainly need to do (b), and what that means is that you need c. to work out what SQL you need d. to close and re-open the data source using OpenDataSource and providing that SQL or e. to set your document object .Mailmerge.DataSource.QueryString to that SQL (e) doesn't always seem to work so you may be forced to do (d). You may find it is difficult to discover the typical SQL for an Excel data source because there is an error in Word 2002 that (sometimes, if not always) crashes Word when you try to display the value of the ConnectString and Query string. If so, make the connection and the filter and sort manually, save the document as a Web Page, close it, then open the main .htm file in notepad. The merge settings should be visible near the top of the file. That said, the OpenDataSource for a typical Excel data source can leave a lot of the parameters blank or leave them out altogether, e.g. try ActiveDocument.MailMerge.OpenDataSource _ Name:="c:\myxls\wb.xls", _ Connection:="", _ SQLStatement:="SELECT * FROM `Sheet1$`", _ SubType:=wdMergeSubTypeAccess Actually, you can probably get away with ActiveDocument.MailMerge.OpenDataSource _ Name:="c:\myxls\wb.xls" but you will certainly need the SQLStatement parameter if you want to issue some SQL when you connect. An example of how to set an SQL command modified with a simple filter and sort is ActiveDocument.MailMerge.DataSource.QueryString = _ "SELECT * FROM `Sheet1$` WHERE `class` = 'b' ORDER BY `pupil` ASC " & "" Peter Jamieson "J.Reynolds" wrote in message ... I am using word and excel 2002. I filter and save data in an excel worksheet. When I open it from the mailmerge toolbar and click on mailmerge recipients I need to filter again to loose the blank rows, I also sort 2 other columns. This gives me the labels required. But I want to run it on a macro. The macro wont filter or sort, it gives me my labels plus three hundred pages of empty labels. Looking at the macro to edit it, there is a gap where the filter and sort should be. I dont know how to write (fill in) the VBA to produce the required result. |
#3
|
|||
|
|||
I copied and pasted your last suggestion into my macro changing the column
headings but it wont run. "Peter Jamieson" wrote: Word does filtering in two ways: a. when you pick individual records in the mail merge Recipients dialog box, Word just marks individual records as included or amitted in its internal list b. when you apply sorts and filters, Word generates a new SQL command and re-queries the data source. You almost certainly need to do (b), and what that means is that you need c. to work out what SQL you need d. to close and re-open the data source using OpenDataSource and providing that SQL or e. to set your document object .Mailmerge.DataSource.QueryString to that SQL (e) doesn't always seem to work so you may be forced to do (d). You may find it is difficult to discover the typical SQL for an Excel data source because there is an error in Word 2002 that (sometimes, if not always) crashes Word when you try to display the value of the ConnectString and Query string. If so, make the connection and the filter and sort manually, save the document as a Web Page, close it, then open the main .htm file in notepad. The merge settings should be visible near the top of the file. That said, the OpenDataSource for a typical Excel data source can leave a lot of the parameters blank or leave them out altogether, e.g. try ActiveDocument.MailMerge.OpenDataSource _ Name:="c:\myxls\wb.xls", _ Connection:="", _ SQLStatement:="SELECT * FROM `Sheet1$`", _ SubType:=wdMergeSubTypeAccess Actually, you can probably get away with ActiveDocument.MailMerge.OpenDataSource _ Name:="c:\myxls\wb.xls" but you will certainly need the SQLStatement parameter if you want to issue some SQL when you connect. An example of how to set an SQL command modified with a simple filter and sort is ActiveDocument.MailMerge.DataSource.QueryString = _ "SELECT * FROM `Sheet1$` WHERE `class` = 'b' ORDER BY `pupil` ASC " & "" Peter Jamieson "J.Reynolds" wrote in message ... I am using word and excel 2002. I filter and save data in an excel worksheet. When I open it from the mailmerge toolbar and click on mailmerge recipients I need to filter again to loose the blank rows, I also sort 2 other columns. This gives me the labels required. But I want to run it on a macro. The macro wont filter or sort, it gives me my labels plus three hundred pages of empty labels. Looking at the macro to edit it, there is a gap where the filter and sort should be. I dont know how to write (fill in) the VBA to produce the required result. |
#4
|
|||
|
|||
"J.Reynolds" wrote: I copied and pasted your last suggestion into my macro changing the column headings but it wont run. "Peter Jamieson" wrote: Word does filtering in two ways: a. when you pick individual records in the mail merge Recipients dialog box, Word just marks individual records as included or amitted in its internal list b. when you apply sorts and filters, Word generates a new SQL command and re-queries the data source. You almost certainly need to do (b), and what that means is that you need c. to work out what SQL you need d. to close and re-open the data source using OpenDataSource and providing that SQL or e. to set your document object .Mailmerge.DataSource.QueryString to that SQL (e) doesn't always seem to work so you may be forced to do (d). You may find it is difficult to discover the typical SQL for an Excel data source because there is an error in Word 2002 that (sometimes, if not always) crashes Word when you try to display the value of the ConnectString and Query string. If so, make the connection and the filter and sort manually, save the document as a Web Page, close it, then open the main .htm file in notepad. The merge settings should be visible near the top of the file. That said, the OpenDataSource for a typical Excel data source can leave a lot of the parameters blank or leave them out altogether, e.g. try ActiveDocument.MailMerge.OpenDataSource _ Name:="c:\myxls\wb.xls", _ Connection:="", _ SQLStatement:="SELECT * FROM `Sheet1$`", _ SubType:=wdMergeSubTypeAccess Actually, you can probably get away with ActiveDocument.MailMerge.OpenDataSource _ Name:="c:\myxls\wb.xls" but you will certainly need the SQLStatement parameter if you want to issue some SQL when you connect. An example of how to set an SQL command modified with a simple filter and sort is ActiveDocument.MailMerge.DataSource.QueryString = _ "SELECT * FROM `Sheet1$` WHERE `class` = 'b' ORDER BY `pupil` ASC " & "" Peter Jamieson "J.Reynolds" wrote in message ... I am using word and excel 2002. I filter and save data in an excel worksheet. When I open it from the mailmerge toolbar and click on mailmerge recipients I need to filter again to loose the blank rows, I also sort 2 other columns. This gives me the labels required. But I want to run it on a macro. The macro wont filter or sort, it gives me my labels plus three hundred pages of empty labels. Looking at the macro to edit it, there is a gap where the filter and sort should be. I dont know how to write (fill in) the VBA to produce the required result. |
#5
|
|||
|
|||
What exactly did you paste in? this was just a piece of sample code and the
SQL uses field names that I happen to have in a test Spreadsheet here. You need at least to adapt the field names to your situation. Peter Jamieson "J.Reynolds" wrote in message ... "J.Reynolds" wrote: I copied and pasted your last suggestion into my macro changing the column headings but it wont run. "Peter Jamieson" wrote: Word does filtering in two ways: a. when you pick individual records in the mail merge Recipients dialog box, Word just marks individual records as included or amitted in its internal list b. when you apply sorts and filters, Word generates a new SQL command and re-queries the data source. You almost certainly need to do (b), and what that means is that you need c. to work out what SQL you need d. to close and re-open the data source using OpenDataSource and providing that SQL or e. to set your document object .Mailmerge.DataSource.QueryString to that SQL (e) doesn't always seem to work so you may be forced to do (d). You may find it is difficult to discover the typical SQL for an Excel data source because there is an error in Word 2002 that (sometimes, if not always) crashes Word when you try to display the value of the ConnectString and Query string. If so, make the connection and the filter and sort manually, save the document as a Web Page, close it, then open the main .htm file in notepad. The merge settings should be visible near the top of the file. That said, the OpenDataSource for a typical Excel data source can leave a lot of the parameters blank or leave them out altogether, e.g. try ActiveDocument.MailMerge.OpenDataSource _ Name:="c:\myxls\wb.xls", _ Connection:="", _ SQLStatement:="SELECT * FROM `Sheet1$`", _ SubType:=wdMergeSubTypeAccess Actually, you can probably get away with ActiveDocument.MailMerge.OpenDataSource _ Name:="c:\myxls\wb.xls" but you will certainly need the SQLStatement parameter if you want to issue some SQL when you connect. An example of how to set an SQL command modified with a simple filter and sort is ActiveDocument.MailMerge.DataSource.QueryString = _ "SELECT * FROM `Sheet1$` WHERE `class` = 'b' ORDER BY `pupil` ASC " & "" Peter Jamieson "J.Reynolds" wrote in message ... I am using word and excel 2002. I filter and save data in an excel worksheet. When I open it from the mailmerge toolbar and click on mailmerge recipients I need to filter again to loose the blank rows, I also sort 2 other columns. This gives me the labels required. But I want to run it on a macro. The macro wont filter or sort, it gives me my labels plus three hundred pages of empty labels. Looking at the macro to edit it, there is a gap where the filter and sort should be. I dont know how to write (fill in) the VBA to produce the required result. |
#6
|
|||
|
|||
After reading through other peoples merge difficulties I found someone who
wanted to revert to the 2000 mailmerge system. This gives a query box which when used running the macro actually records the query. The result was ActiveDocument.Mailmerge.DataSource.QueryString = "SELECT * FROM 'Sheet 1$' WHERE ('Name' IS NOT NULL And 'Name' ") ORDER BY 'Product' ASC, 'Size' ASC" &"" . I may have missed or included an extra space, but it works. Thanks for your help. It is great to know there is someone out there who knows what they are doing. "Peter Jamieson" wrote: What exactly did you paste in? this was just a piece of sample code and the SQL uses field names that I happen to have in a test Spreadsheet here. You need at least to adapt the field names to your situation. Peter Jamieson "J.Reynolds" wrote in message ... "J.Reynolds" wrote: I copied and pasted your last suggestion into my macro changing the column headings but it wont run. "Peter Jamieson" wrote: Word does filtering in two ways: a. when you pick individual records in the mail merge Recipients dialog box, Word just marks individual records as included or amitted in its internal list b. when you apply sorts and filters, Word generates a new SQL command and re-queries the data source. You almost certainly need to do (b), and what that means is that you need c. to work out what SQL you need d. to close and re-open the data source using OpenDataSource and providing that SQL or e. to set your document object .Mailmerge.DataSource.QueryString to that SQL (e) doesn't always seem to work so you may be forced to do (d). You may find it is difficult to discover the typical SQL for an Excel data source because there is an error in Word 2002 that (sometimes, if not always) crashes Word when you try to display the value of the ConnectString and Query string. If so, make the connection and the filter and sort manually, save the document as a Web Page, close it, then open the main .htm file in notepad. The merge settings should be visible near the top of the file. That said, the OpenDataSource for a typical Excel data source can leave a lot of the parameters blank or leave them out altogether, e.g. try ActiveDocument.MailMerge.OpenDataSource _ Name:="c:\myxls\wb.xls", _ Connection:="", _ SQLStatement:="SELECT * FROM `Sheet1$`", _ SubType:=wdMergeSubTypeAccess Actually, you can probably get away with ActiveDocument.MailMerge.OpenDataSource _ Name:="c:\myxls\wb.xls" but you will certainly need the SQLStatement parameter if you want to issue some SQL when you connect. An example of how to set an SQL command modified with a simple filter and sort is ActiveDocument.MailMerge.DataSource.QueryString = _ "SELECT * FROM `Sheet1$` WHERE `class` = 'b' ORDER BY `pupil` ASC " & "" Peter Jamieson "J.Reynolds" wrote in message ... I am using word and excel 2002. I filter and save data in an excel worksheet. When I open it from the mailmerge toolbar and click on mailmerge recipients I need to filter again to loose the blank rows, I also sort 2 other columns. This gives me the labels required. But I want to run it on a macro. The macro wont filter or sort, it gives me my labels plus three hundred pages of empty labels. Looking at the macro to edit it, there is a gap where the filter and sort should be. I dont know how to write (fill in) the VBA to produce the required result. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Button Won't Stay on Toolbar | Microsoft Word Help | |||
Possible bug when recording a Word Macro | Microsoft Word Help | |||
Macro for a Table in a MailMerge | Mailmerge | |||
Macro to Find/Replace (with MailMerge Field)? | Mailmerge | |||
2000 to 2002 macro and "Could not open macro storage" | Mailmerge |