Re: 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" <pjj@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:OYdW9xqRHHA.4076@xxxxxxxxxxxxxxxxxxxxxxx
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" <Bstice@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:21C4155E-3752-4666-A19A-805724475522@xxxxxxxxxxxxxxxx

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




.

Relevant Pages

  • Re: C0000005 errors: ODBC driver problem?
    ... Your suggestion to use APPEND FROM solved the problem. ... There must be a bug in the Excel ODBC driver I was using, because when I entirely stopped using SQLConnectand SQLexecfor Excel and instead used ...
    (microsoft.public.fox.programmer.exchange)
  • Re: ODBC with Excel in 2003
    ... The orgchart is essentially an addon to visio and as ... As the org chart wizard isn't very tidy and doesn't allow pre-formatting ... of data in my excel sheets.I've also been able to update it but I had to ... odbc are programming interfaces to allow ...
    (microsoft.public.visio.general)
  • Re: ODBC with Excel in 2003
    ... At home I've just managed to import from excel via odbc and create a visio ... drawing (not an org chart) by following the MS knowledge base article. ...
    (microsoft.public.visio.general)
  • Re: ODBC with Excel in 2003
    ... can be linked to excel for refreshing then I'm sadly going to have to give ... you can't use this function for refreshing an org chart. ... I guess the ODBC function is avaiable on the ... The orgchart is essentially an addon to visio ...
    (microsoft.public.visio.general)
  • Re: ODBC with Excel in 2003
    ... At home I've just managed to import from excel via odbc and create a visio ... Now I need to achieve the same with an org chart, however, the way the data ...
    (microsoft.public.visio.general)