Transfer spread sheet to Access

[numberman]

I have a worksheet with data that I would like to transfer to a certain MS Access table via VBA, the equivalent method in Access VBA is : TransferSpreadsheet, but as far as I know, it is not available in Excel.

so my question is , does anyone know how to transfer a range of data from Excel to Access via VBA? it needs to be done with macro, because I have a large number of files.

I know how to loop through the folder, I just need to know what is the best way or the proper way to transfer the data of each workbook to Access. The data range is about 300 rows

Thanks!


JH

 

[Fazza]

hi. below is some code I use. late bound, btw. you might add some error handling. hth. regards

Sub ADO_ExcelToAccess(ByVal DB_Path As String, ByVal DB_TableName As String, ByVal SourceWorksheetName As String)
  'based on Erlandsen Data Consulting
  Dim r As Long, i As Long
  Dim cn As Object
  Dim rs As Object
  Dim ar As Variant

  With Worksheets(SourceWorksheetName).Range("A1").CurrentRegion
    ReDim ar(1 To .Rows.Count, 1 To .Columns.Count)
    ar = .Value
  End With

  Set cn = CreateObject("ADODB.Connection")
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DB_Path & ";"
  Set rs = CreateObject("ADODB.Recordset")
  rs.Open DB_TableName, cn, 1, 3, 2
  For r = 2 To UBound(ar, 1)
    With rs
      .AddNew
      For i = LBound(ar, 2) To UBound(ar, 2)
        .fields(ar(1, i)) = ar(r, i)
      Next i
      .Update
    End With
  Next r
  rs.Close: Set rs = Nothing
  cn.Close: Set cn = Nothing
  Erase ar
End Sub