Best way to read an Excel file into an Access database

Question

What's the "best" way to read (just read) an Excel file from within an Access 2007 application. I only want to loop trough the rows and put the data into an Access table.

I don't want a manually import (Get External Data dialog) but by VBA. The user gets a Form with a Browse button and then points to a Excel file with a defined content/format. After that the VBA code reads the data and puts it into the Access database.

Ague

Answer 1

You could try the DoCmd.TransferSpreadsheet method.

DoCmd.TransferSpreadsheet acImport, , "from_excel","C:\Access\demo.xls", True

That imports spreadsheet data into a table named from_excel, and assumes the first row of the spreadsheet contains field names. See Access help for TransferSpreadsheet or online here, for more details.

Spectacle

Answer 2

If you want to read the entire spreadsheet in, you can import an Excel spreadsheet directly into Access. See here or here.

You can also choose to link to the Excel spreadsheet instead of importing it. That way any changes to the Excel spreadsheet will be reflected in the linked table. However, you won't be able to make changes from within Access.

A third option is to write some VBA code within Access to open a recordset and read the spreadsheet in. See the answers from KeithG in this thread. You can do something like this to open the spreadsheet in VBA:

Dim xl As Excel.Application
Dim xlsht As Excel.Worksheet
Dim xlWrkBk As Excel.Workbook

Set xl = CreateObject("Excel.Application")
Set xlWrkBk = GetObject("H:/ggg.xls")
Set xlsht = xlWrkBk.Worksheets(1)

Abeyta

Thanks, excellent tuts, but I meant a option by VBA. I've extended the question, see above – Ague

See my edited post for some info on how to access the spreadsheet through VBA. – Abeyta

You can run the import in VBA with DoCmd.TransferSpreadsheet. This answer seems very incomplete without a mention of that, seems to me. – Quianaquibble

TLiebe > Thanks for the answer, but I a prefer the TransferSpreadsheet answer. Unfortunately I can just mark 1 answer as the accepted answer – Ague

Answer 3

Try something like this:

Dim excelApp As Excel.Application
Dim workbook As Excel.Workbook
Dim worksheet As Excel.Worksheet

Set excelApp = CreateObject("Excel.application")
Set workbook = excelApp.Open("C:\someFileName.xls")
Set worksheet = workbook.Worksheets(1)

And then loop through the rows and columns, pull the data from the cells, and insert it into the database. (You can use the worksheet.cells method.) Try searching on google for code samples.

Weep

So, how do you loop through the rows and columns? I want to import an xlsx into a table, but since the columns don't necessarily match up to those in the table, I need to manually do some redirection. – Ogive

Answer 4

Hereafter my method to read an excel file and all the worksheet names:

Function listOfWorksheet(filename As String) As Collection

Set dbExcel = OpenDatabase(filename, False, True, "excel 8.0")
For Each TableDef In dbExcel.TableDefs
    Debug.Print TableDef.Name
Next

End Function

Now, you can use the name of the worksheet to read the whole content:

Function ReadMyObjects(filename as String, wsName as String) As Collection
On Error GoTo label_error
Set results = New Collection
Dim countRows As Integer
Set dbExcel = OpenDatabase(filename, False, True, "excel 8.0")
Set excelRs = dbExcel.OpenRecordset(wsName, dbOpenSnapshot)

Do While Not excelRs.EOF
    'Data Rows
     Dim item As MyObject 'a custom object defined by you.
     Set item = New MyObject
     item.ABC = Nz(excelRs.Fields("COLUMN_ABC").Value, "")
     item.DEF = Nz(excelRs.Fields("COLUMN_DEF").Value, "")
     results.Add item
     excelRs.MoveNext
Loop
excelRs.Close
Set ReadMyObjects= results
GoTo label_exit
label_error:
    MsgBox "ReadMyObjects" & Err.Number & " " & Err.Description
label_exit:
End Function

Sap