Simple Question

[Bstice]

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.

 

[Peter Jamieson]

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]

I am using Word 2000 so lets do VBA. Thanks again for your help.

 

[Peter Jamieson]

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

 

[Peter Jamieson]

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

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

I am using Word 2000 so lets do VBA. Thanks again for your help.

 

[Bstice]

Peter - this is great help. Thank you!

Brennan

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

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

I am using Word 2000 so lets do VBA. Thanks again for your help.