Exporting from Excel to an Access table

[mdmackillop]

Hi all,
It's still to early after the New Year to strain my brain muscles, so if someone can assist here I'd be much obliged. I just need to export some data from a spreadsheet to an Access table. I've done the donkey work in the attached file, so it's the clever bit that is required.:think:
Regards
Malcolm

[austenr]

Hi Malcomb,

Give this a try:

[mdmackillop]

Hi Austen,
I couldn't get the ADODB connection to work, but managed with a DAO link. I got the rest from your article.
Thanks
Malcolm


Sub Exports()
'Requires reference to Microsoft DAO 3.6 Object Library
Dim Cel As Range
Dim MyDate As Date
Dim Employee As Long
Dim JobNo As Long
Dim Category As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

'Establish connection
Set dbs = OpenDatabase("S:\Database\Time2005.mdb")
'Fill recordset
Set rst = dbs.OpenRecordset("Hours")
MyDate = Range("B1")
Employee = Range("F1")

With rst
' add values to each field in the record
For Each Cel In Range("MyData")
If Cel > 0 Then
.AddNew ' create a new record
JobNo = Cells(Cel.Row, 1)
Category = Cells(4, Cel.Column)
Hours = Round(Cel, 2)
.Fields("Date") = MyDate
.Fields("EmployeeNo") = Employee
.Fields("JobNo") = JobNo
.Fields("Category") = Category
.Fields("Hours") = Hours
.Update ' stores the new record
End If
Next
End With
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Sub

[austenr]

Glad I could help. http://vbaexpress.com/forum/images/smilies/beerchug.gif