Reading from Excel using

[ss4at]

Hi,

I have one excel sheet with 6 columns and then data as rows. I want to open that excel sheet and then read data from rows. I want to save that data to access database.

I am new to vb so i dont know how to read excel sheets using vb.

Any help will highly appreciated.

thanks.

[vincentg]

Hi there.

Option 1: You can use ADO to read the excel file and convert it into a recordset so you can read it again to save it into a database.

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
 
conn = New ADODB.Connection
conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" _
  & "Data Source=C:\YOUREXCELFILEHERE.XLS;" _
  & "Extended Properties=Excel 8.0;"
 
 
rs.Open("Select * from [YOUREXCELSHEETNAMEHERE]", conn,ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly)

Option 2: You can use a Excel Office Automation to read each cell in the Excel Object then later on save it into a database..Remember to add in your project a reference to Microsoft Excel Object...

Dim objExcelApp As Excel.Application
Dim objExcelWkb As Excel.Workbook
Dim objExcelWks As Excel.Worksheet
 
Set objExcelApp = New Excel.Application
Set objExcelWkb = objExcelApp.Workbooks.Open(FileName:='YOUREXCELNAME.XLS')
Set objExcelWks = objExcelWkb.Worksheets(1)
objExcelWks.Cells(10,10) '<-- you can use this to access each cell...

option 3.. option 4 and so on are yours to find out... there are a lot of ways to read it......


-vince

[RobDog888]

You can do it with one line of code, but from Access instead. Allot easier.

Application.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Table_XL", "D:\Book1.xls", False, "Sheet2$"

Moved from Classic VB forum.