Microsoft KB Archive/190195

= How To Extract Information from Excel Sheet with DAO =

Article ID: 190195

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft Visual Basic 6.0 Learning Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * The DAO SDK

-



This article was previously published under Q190195



SUMMARY
The Excel ISAM driver 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. 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 containing the data information. For example:

  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, a particular column can contain only one type of data.

How to Create a Range
Follow these steps in order to create a range:
 * 1) Highlight the data.
 * 2) From the menu bar, click Insert, and then click Name.
 * 3) Click Define, and then click Range name.

Note: The Refers to box 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
  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                      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
 Create a new standard EXE project called "DAO_EXCEL." From the Project menu, click References, and then select the Microsoft DAO 3.5 Library check box.</li> Place a CommandButton on the form.</li>  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_ (" \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).

</li></ol>

The following results are as expected: <pre class="fixed_text"> 11 cc 78 ee 22 xx 33 ff 45 uu 56 oo

<div class="references_section">