Tip of the moment is: ADO - Reading Excel worksheet by VBA
Following is the basic code to loop throug
Excel code by ADO
Dim cnnExcel As New ADODB.Connection
With cnnExcel
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties").Value = "Excel 8.0"
.Open "C:\Test.xls"
End With
Dim rsExcel As New ADODB.Recordset
rsExcel.Open "Select * from [Sheet1$]", cnnExcel, adOpenStatic
Do Until rsExcel.EOF
Debug.Print rsExcel.Fields(0).Value
Debug.Print rsExcel.Fields(1).Value
Debug.Print rsExcel.Fields(2).Value
Debug.Print rsExcel.Fields(3).Value
rsExcel.MoveNext
Loop
NOTE: If the data type for a column of values is not the same for all data you
must work around.
ADO begin to estimate the data type for each column in your Excel worksheet or
range. A problem can arise if you have numeric values mixed with text values in
the same column. Both the Jet and the ODBC Provider return the data of the
majority type, but return NULL values for the minority data type. If the two
types are equally mixed in the column, the provider chooses numeric over text.
For mixed data types in columns use following to solve the problem:
"IMEX=1" Extended Property Enable Import Mode by using the setting "IMEX=1" in the Extended Properties
section of the connection string. This enforces the ImportMixedTypes=Text
registry setting and imports all fields as text. However, note that updates may
give unexpected results in this mode. For additional information about this
setting see the Microsoft Knowledge Base Article - Q194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset http://support.microsoft.com/default.aspx?scid=kb;en-us;194124
Dim cnnExcel As New ADODB.Connection
With cnnExcel
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties").Value = _
"Excel 8.0; HDR=yes; MAXSCANROWS=0 ;"
.Open "C:\Test.xls"
End With
"MAXSCANROWS=0" Extended Property By default ADO scans first 8 rows to guess the data type for each column in
your Excel. Specifying MAXSCANROWS=0 in the Extended Properties of the
connection string should force ADO to scan all rows before choosing a data type.
MAXSCANROWS can also be set to fixed value (between 0-16) in the registry by
changing the TypeGuessRows value:
Dim cnnExcel As New ADODB.Connection
With cnnExcel
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties").Value = _
"Excel 8.0; HDR=yes; IMEX=1;"
.Open "C:\Test.xls"
End With
Sometime is this not enough and then must one sett
following key in the registy to 0: Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows