Re: Simple Question
- From: "Peter Jamieson" <pjj@xxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 2 Feb 2007 09:47:13 -0000
BTW, there is a lengthy description of some of the problems getting data
from Excel workbooks at
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" <pjj@xxxxxxxxxxxxxxxxxxxxxxxxxx> 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
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)
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.Destination = wdSendToNewDocument
Connection:="DSN=Excel Files;DBQ=c:\myworkbooks\mywb.xls;", _
SQLStatement:="SELECT * FROM [Consolidated New$]"
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
"Bstice" <Bstice@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
I am using Word 2000 so lets do VBA. Thanks again for your help.
- Re: Simple Question
- From: Bstice
- Re: Simple Question
- Prev by Date: Re: Simple Question
- Next by Date: Re: More stupid INCLUDEPICTURE questions
- Previous by thread: Re: Simple Question
- Next by thread: Re: Simple Question