This article has been archived. It is offered "as is" and will no longer be updated.
The Excel ISAM driver does not dynamically convert datatypes.
If there is a column in your Excel spreadsheet that contains both text andnumbers, the ISAM will not be able to correctly interpret which datatype itshould be. Make sure that all the cells in a column are formatted tobe the same datatype. For example, you might have following data in fourcolumns 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 anothercontaining 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 theparticular named range. However, in one range, a particular column cancontain only one type of data.
How to Create a Range
Follow these steps in order to create a range:
Highlight the data.
From the menu bar, click Insert, and then click Name.
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.
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.
Place a CommandButton on the form.
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
Please refer to the documentation for how to connect to Excel through DAO.Another good reference will be the upcoming ADO->Excel reference.