Re: Reading Excel file Fast
You code always try adodb
VB Code:
Set tmpConn = New Connection
tmpConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFileName & ";" & _
"Extended Properties=""Excel 8.0;"""
'The provider assumes that the table of data begins with the upper-most,
'left-most, non-blank cell on the specified worksheet.
'SELECT * FROM [SheetName$] WHERE [Field Name] LIKE 'A%' ORDER BY [Field Name]
strSQL = "SELECT * FROM [Sheet1$]"
Set oRs = New Recordset
Call oRs.Open(strSQL, tmpConn)
Re: Reading Excel file Fast
Thanks, can you explain in more detail?
Don't know much about adodb and I need to read values from excel sheet to show in my VB app.
Oh yeah, I need to display the data in VB controls (textboxes etc)
Re: Reading Excel file Fast
With this method your treating the excel spreadsheet like a table in a database.
You'll need to add a reference to ADO
VB Code:
Dim tmpConn as Connection
Dim oRs as RecordSet
Dim fld as Field
'strFileName is the path to the Excel file.
Set tmpConn = New Connection
tmpConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFileName & ";" & _
"Extended Properties=""Excel 8.0;"""
'The provider assumes that the table of data begins with the upper-most,
'left-most, non-blank cell on the specified worksheet.
'SELECT * FROM [SheetName$] WHERE [Field Name] LIKE 'A%' ORDER BY [Field Name]
'this is the query which is just say get everything in Sheet1$.
'sheet one is the default name, yours might be set to something different.
strSQL = "SELECT * FROM [Sheet1$]"
Set oRs = New Recordset
Call oRs.Open(strSQL, tmpConn)
'oRs is the RecordSet this contains all the information that was in the spreadsheet
'to view this information
While Not oRs.Eof
For Each fld in ors.Fields 'loop through each column
Text1.Text = Text1.Text & Field.Name & "=" & fld.Value & vbcrlf
Next
oRs.MoveNext
Wend 'loop through each row