Read excel file into MS Access DB

[choco_fudge]

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

[goatsucker]

Try opening an ADO connnection to an Excel spreadsheet like this

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.

[ITboy]

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..