Visual Basic 6.0 and Excel 2000 2

[Guru2B]

Hi all,

Can anyone tell me if there is a way (there must be) to read an excel spreadsheet?

I have to grab all the rows out of a sheet and export the data to a database.

Thanks in advance,
Guru2B

 

[somsally]

Hi Guru
Using Excel 2000 files in VB is very easy.
i assume that you know how to use ADO Objects.

To open a excel file use a ADO Connection object and set its Provider as "Microsoft.Jet.OLEDB.4.0" and set Data Source property to the excel file path.
since Jet Providers can open only Access databases set another property "Extended Properties" to "Excel 8.0"

To open the worksheet, use recordset's Open method and specify worksheet name say Sheet1 as [Sheet1$]. This is required because ADO cannot understand $ sign which will be returned along with sheet names

Dim cnExcelSource As New Connection
Dim rsDataSheet As New Recordset

Private Sub Form_Load()
cnExcelSource.Provider = "Microsoft.Jet.OLEDB.4.0"
cnExcelSource.ConnectionString = "Data Source = d:\Customer.xls; Extended Properties=Excel 8.0"
cnExcelSource.Open
rsDataSheet.Open "select * from [Sheet1$]", cnExcelSource, adOpenStatic, adLockOptimistic
End Sub

good luck