Read excel file into MS Access DB

Hi,
How to store the values in excel files into MS Access Database? Thanks for replying.

Try opening an ADO connnection to an Excel spreadsheet like this

Quote:

---

strDBPath = "C:\Documents\spreadsheet.xls"
With cnnDB
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties") = "Excel 8.0"
.Open strDBPath
End With

---

I believe you can then access the data in SQL. The table name you must be in the form sheetname$range for example 1996 Sales$A1: D12 for a sheet called "1996 Sales" and the range A1: D12 in that sheet. If the whole sheet is required use sheetname$

hope this helps.

Here is a way to do it:
Sub GetAccess()
Dim m_cnExcel As ADODB.Connection

Dim strAccessPath As String
strAccessPath = "C:\windows\desktop\db1.MDB"
Dim strAccessTable As String
strAccessTable = "RWR"

Dim strExcelPath As String
strExcelPath = "C:\windows\desktop\ycds\ycd.XLS"

Dim strExcelSheet As String
strExcelSheet = "readtxt$"

' Existence Tests
If Dir(strAccessPath) = "" Then
MsgBox "FILE " & LCase(strAccessPath) & " NOT FOUND!!!"
Exit Sub
End If
If Dir(strExcelPath) = "" Then
MsgBox "FILE " & LCase(strExcelPath) & " NOT FOUND!!!"
Exit Sub
End If

' Establish Connection
Set m_cnExcel = New ADODB.Connection
With m_cnExcel
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = _
"Data Source=" & "'" & Trim(strExcelPath) & "'" & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES;"";"
.Open
End With

' Transfer Excel Spreadsheet Into An Access Table
Dim strSQL As String
strSQL _
= "SELECT * " _
& " INTO " & strAccessTable & " " _
& " IN " & """" & strAccessPath & """ " _
& " FROM [" & strExcelSheet & "] "
m_cnExcel.Execute strSQL

' Shutdown
m_cnExcel.Close
Set m_cnExcel = Nothing

MsgBox "Transfer Complete"
' Unload Me
Exit Sub

End Sub
Hope it helps..:D