Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Simple Question
I am trying to do a mail merge with an excel sheet that has several sheets.
I am sure there is a way to specify which sheet to use when the merge helper asks if I want to use the entire sheet or not. I have tried plugging in the following which is the sheet name and cell range: 'Consolidated New.'!1:1475 This causes the action to time out and the DDE connection to break. If I just want to use a range of cells on one sheet in a workbook with multiple sheets, what do I need to do to process a mail merge. I know that you can pull the sheet out by itself, but it would be nice if that didn't have to happen. Thanks for your help. |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Simple Question
I have never discovered a way to get DDE to connect to anything except the
first sheet in a workbook. You can specify a sheet and range of cells if you connect using ODBC (most versions of Word or OLEDB (Word 2002/2003/2007), but as you probably know they tend to introduce other problems. To do it with ODBC you have to use VBA. With OLEDB you can do it using an ODC file or with VBA - which (if any) would you prefer to try? Peter Jamieson "Bstice" wrote in message ... I am trying to do a mail merge with an excel sheet that has several sheets. I am sure there is a way to specify which sheet to use when the merge helper asks if I want to use the entire sheet or not. I have tried plugging in the following which is the sheet name and cell range: 'Consolidated New.'!1:1475 This causes the action to time out and the DDE connection to break. If I just want to use a range of cells on one sheet in a workbook with multiple sheets, what do I need to do to process a mail merge. I know that you can pull the sheet out by itself, but it would be nice if that didn't have to happen. Thanks for your help. |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Simple Question
I am using Word 2000 so lets do VBA. Thanks again for your help. |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Simple Question
OK in fact you can use MS Query (if it has been installed) to do the ODBC
connection, but there can be problems and if you haven't used it before, there are a lot of dialog boxes. Let's just go the VBA route for now. First (whatever you do) you need an Excel ODBC DSN. There will probably be one on your system already called "Excel Files" (it depends on the version of Windows as well I think). You should be able to check in Control Panel|Administrative Tools|Data Sources (ODBC) or similar and create one if necessary. Let's assume the DS is "Excel Files", your workbook is c:\myworkbooks\mywb.xls, and the Sheet is called "Consolidated New" (no full stop in theend in my example) Then try Sub mysub() ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument ActiveDocument.MailMerge.MainDocumentType = wdFormLetters ActiveDocument.MailMerge.Destination = wdSendToNewDocument ActiveDocument.MailMerge.OpenDataSource _ Name:="", _ Connection:="DSN=Excel Files;DBQ=c:\myworkbooks\mywb.xls;", _ SQLStatement:="SELECT * FROM [Consolidated New$]" End Sub That will get the whole sheet. If you need to specify a particular range of cells, make sure that the first row in the rnge contains column headings, then add the range in A1:Xn notation, e.g. for the first 3 columns and 2 data rows in the spreadsheet, use SQLStatement:="SELECT * FROM [Consolidated New$A1:C3]" The SQL dialect is Jet (Access) SQL so you can select columns, do WHERE clauses, all the usual stuff. If you're not that familiar with VBA, see also ttp://word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm Peter Jamieson "Bstice" wrote in message ... I am using Word 2000 so lets do VBA. Thanks again for your help. |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Simple Question
BTW, there is a lengthy description of some of the problems getting data
from Excel workbooks at http://tips.pjmsn.me.uk/t0003.htm It doesn't cover ODBC - if you read the article, the following additional notes may help: a. The ODBC driver has most of the same problems as the OLE DB provider, but returns many of the data types rather differently. You can't use OLE DB in Word 2000. b. With ODBC, you get the (nastier) behaviour described for IMEX=0. As far as I can tell, the ODBC driver always checks the first 8 rows of the sheet. Documentation suggests it looks at the same TypeGuessRows registry setting but that didn't work for me. In essence, none of the workarounds suggested for OLE DB work with ODBC. Peter Jamieson "Peter Jamieson" wrote in message ... OK in fact you can use MS Query (if it has been installed) to do the ODBC connection, but there can be problems and if you haven't used it before, there are a lot of dialog boxes. Let's just go the VBA route for now. First (whatever you do) you need an Excel ODBC DSN. There will probably be one on your system already called "Excel Files" (it depends on the version of Windows as well I think). You should be able to check in Control Panel|Administrative Tools|Data Sources (ODBC) or similar and create one if necessary. Let's assume the DS is "Excel Files", your workbook is c:\myworkbooks\mywb.xls, and the Sheet is called "Consolidated New" (no full stop in theend in my example) Then try Sub mysub() ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument ActiveDocument.MailMerge.MainDocumentType = wdFormLetters ActiveDocument.MailMerge.Destination = wdSendToNewDocument ActiveDocument.MailMerge.OpenDataSource _ Name:="", _ Connection:="DSN=Excel Files;DBQ=c:\myworkbooks\mywb.xls;", _ SQLStatement:="SELECT * FROM [Consolidated New$]" End Sub That will get the whole sheet. If you need to specify a particular range of cells, make sure that the first row in the rnge contains column headings, then add the range in A1:Xn notation, e.g. for the first 3 columns and 2 data rows in the spreadsheet, use SQLStatement:="SELECT * FROM [Consolidated New$A1:C3]" The SQL dialect is Jet (Access) SQL so you can select columns, do WHERE clauses, all the usual stuff. If you're not that familiar with VBA, see also ttp://word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm Peter Jamieson "Bstice" wrote in message ... I am using Word 2000 so lets do VBA. Thanks again for your help. |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Simple Question
Peter - this is great help. Thank you!
Brennan "Peter Jamieson" wrote: BTW, there is a lengthy description of some of the problems getting data from Excel workbooks at http://tips.pjmsn.me.uk/t0003.htm It doesn't cover ODBC - if you read the article, the following additional notes may help: a. The ODBC driver has most of the same problems as the OLE DB provider, but returns many of the data types rather differently. You can't use OLE DB in Word 2000. b. With ODBC, you get the (nastier) behaviour described for IMEX=0. As far as I can tell, the ODBC driver always checks the first 8 rows of the sheet. Documentation suggests it looks at the same TypeGuessRows registry setting but that didn't work for me. In essence, none of the workarounds suggested for OLE DB work with ODBC. Peter Jamieson "Peter Jamieson" wrote in message ... OK in fact you can use MS Query (if it has been installed) to do the ODBC connection, but there can be problems and if you haven't used it before, there are a lot of dialog boxes. Let's just go the VBA route for now. First (whatever you do) you need an Excel ODBC DSN. There will probably be one on your system already called "Excel Files" (it depends on the version of Windows as well I think). You should be able to check in Control Panel|Administrative Tools|Data Sources (ODBC) or similar and create one if necessary. Let's assume the DS is "Excel Files", your workbook is c:\myworkbooks\mywb.xls, and the Sheet is called "Consolidated New" (no full stop in theend in my example) Then try Sub mysub() ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument ActiveDocument.MailMerge.MainDocumentType = wdFormLetters ActiveDocument.MailMerge.Destination = wdSendToNewDocument ActiveDocument.MailMerge.OpenDataSource _ Name:="", _ Connection:="DSN=Excel Files;DBQ=c:\myworkbooks\mywb.xls;", _ SQLStatement:="SELECT * FROM [Consolidated New$]" End Sub That will get the whole sheet. If you need to specify a particular range of cells, make sure that the first row in the rnge contains column headings, then add the range in A1:Xn notation, e.g. for the first 3 columns and 2 data rows in the spreadsheet, use SQLStatement:="SELECT * FROM [Consolidated New$A1:C3]" The SQL dialect is Jet (Access) SQL so you can select columns, do WHERE clauses, all the usual stuff. If you're not that familiar with VBA, see also ttp://word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm Peter Jamieson "Bstice" wrote in message ... I am using Word 2000 so lets do VBA. Thanks again for your help. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Word created document; but would like to add "excell" style simple | Microsoft Word Help | |||
Auto-numbering keeps goofing up | Formatting Long Documents | |||
Make Word simple to use | Microsoft Word Help | |||
Simple Tab Question | Page Layout | |||
Newbie document question please | New Users |