DOCUMENT:Q190195  13-AUG-1998  [vbwin]
TITLE   :HOWTO: ExtractInformation From Excel Sheet with DAO
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:
OPER/SYS:WINDOWS winnt
KEYWORDS:

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual Basic for Windows Learning, Professional, and
   Enterprise Editions, version 6.0
 - Microsoft Visual Basic Professional and Enterprise Editions for
   Windows, version 5.0
 - The DAO SDK
---------------------------------------------------------------------

SUMMARY
=======

The Excel ISAM driver is limited in the sense that it does not dynamically
convert datatypes.

MORE INFORMATION
================

If there is a column in your Excel spreadsheet that contains both text and
numbers, the ISAM will not be able to correctly interpret which datatype it
should be. Please make sure that all the cells in a column are formatted to
be the same datatype. For example, you might have following data in four
columns in an Excel sheet:

   male   female children teens
   11     cc     78       ee
   22     xx     33       ff
   45     uu     56       oo

If you try to read the data through ISAM driver against the whole sheet,
you will get the null values for first row. If you want to avoid this,
create named ranges; one containing only the header information and another
one containing the data information, such as:

   named range 'myRange1' :
   male     female    children     teens

   named range 'myRange2' :
   11   cc   78   ee
   22   xx   33   ff
   45   uu   56   oo

Now you can connect to Excel and request information only from the
particular named range. However, in one range, one particular column can
contain only one type of data.

Creating a Range
----------------

Highlight the data. From the menu, select Insert->Name->Define->rangename.
Note that the "refers to" box below will refer to your highlighted range;
this should grow and shrink as data is inserted and deleted. To retrieve
your data, use the range name you just created for the table name in your
select statement.

Excel Steps
-----------

1. Create the Excel file, test.XLS, with following data in sheet1:

   excel File : test.xls with the following entries:

        male  female    children   teens
         11    cc       78           ee
         22    xx       33           ff
         45    uu       56           oo

2. Create the named range, myRange1 and myRange2, in the sheet containing
   the appropriate data.

      named range : myRange1

      male female children teens

      named range  : myRange2

      11     cc   78   ee
      22     xx   33   ff
      45     uu   56   oo

Visual Basic Steps
------------------

1. Create a new standard EXE project called "DAO_EXCEL."

2. Select References from the project menu and check Microsoft
   DAO 3.5 Library.

3. Place a CommandButton on the form.

4. Paste the following code in the form code window:

      private Sub Command_click1

      im dbtmp As DAO.Database
      im tblObj As DAO.TableDef
      im rs As DAO.Recordset

      et dbtmp = OpenDatabase_
           ("<complete path>\test.xls", False, True, "Excel 8.0;")

      DoEvents

      Set rs = dbtmp.OpenRecordset("select * from `myRange2`")
      While Not rs.EOF
          For x = 0 To rs.Fields.Count - 1
              Debug.Print rs.Fields(x).Value
          Next
          rs.MoveNext
      Wend
      End Sub

      Note the reverse apostrophe "`" while specifying the range
      name(myrange2).

The following results are as expected:

  11
  cc
  78
  ee
  22
  xx
  33
  ff
  45
  uu
  56
  oo

REFERENCES
==========

Please refer to the documentation for how to connect to Excel through DAO.
Another good reference will be the upcoming ADO->Excel reference.

Additional query words: kbDAO350 kbIISAM kbDatabase kbDSupport kbdse
kbVBp500 kbVBp600
======================================================================
Platform          : WINDOWS winnt
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1998.