Importing to Access from multiple Excel files

[Gareth Jones]

I am in the process of updating a system at work which
requires the transformation of data from Excel
spreadsheets in to Access database.

Does any one know a method of importing data quickly into
a from specific cells in multiple spreadsheets. This would
save considerable time in data input. (Ideally the Excel
files would not need to be individually specified, i.e.
just specify a starting directory)

Thanking you in advance

Gareth Jones

 

[Ken Snell [MVP]]

Some sample code that may get you started (you can add loops to this code to
loop through each worksheet in a book if you'd like).



Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Set xlw = xlx.Workbooks.Open("C:\Filename.xls"), , True
Set xls = xlw.Worksheets("WorksheetName")
Set xlc = xls.Range("A1")
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("TableName", dbOpenDynaset, dbAppendOnly)
Do While xlc.Value <> ""
rst.AddNew
For lngColumn = 0 To rst.Fields.Count - 1
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
Next lngColumn
rst.Update
Set xlc = xlc.Offset(1,0)
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Set xlc = Nothing
Set xls = Nothing
xlw.Close False
Set xlw = Nothing
xlx.Quit
Set xlx = Nothing