Importing an Excel Listobject into an Access table

Question

I have an Access database with a table named InventoryAvail. I'd like to push a button in Access and import a specific Listobject from a specific Excel file to fill the InventoryAvail table.

What vba code do I need to put on the button's event?

Docmd.Transferspreadsheet will not work because I do not want all of the data from the spreadsheet, I only want what is in the specific Listobject.

The Excel file is called Inventory and resides on my desktop. The worksheet is named Inventory. The Listobject is named Available and has two columns: Part, Qty.

The InventoryAvail table has two columns: PartNumber and Quantity.

I need the data from the Excel Listobject Available to be imported into the Access table InventoryAvail

Answer 1

You're right that for some reason DoCmd.TransferSpreadsheet does not work for named ranges. I was able to hack it once by pulling the address from it, but I had to open the workbook.

In any case I can't see a way around opening the workbook

This should work for you.

Steps:

• opens the spreadsheet
• reads the listbox data into an array
• opens recordset using your table
• passes each row of data into the table using AddNew method
• cleans up

Here's the code

Public Sub AddInventory()

On Error GoTo Err_Handler

    Const IMPORT_TABLE  As String = "InventoryAvail"

    Const IMPORT_FILE   As String = "yourspreadsheetfullpathname"
    Const SHEET_NAME    As String = "Sheet1"
    Const LISTBOX_NAME  As String = "List1"

    Dim xlApp       As Object
    Dim xlBook      As Object
    Dim rs          As DAO.Recordset

    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.WorkBooks.Open(IMPORT_FILE, , True) ' open as readonly

    Dim xlList      As Variant
    Dim intRow      As Integer

    ' creates an array from ListObject values
    xlList = xlBook.Worksheets(SHEET_NAME).ListObjects(LISTBOX_NAME).DataBodyRange.Value

    xlBook.Close
    xlApp.Quit
    Set xlApp = Nothing

    Set rs = CurrentDb.OpenRecordset(IMPORT_TABLE)
    With rs
        For intRow = LBound(xlList) To UBound(xlList)
            Debug.Print xlList(intRow, 1) & ": " & xlList(intRow, 2)
            .AddNew
            .Fields(0).Value = xlList(intRow, 1)
            .Fields(1).Value = xlList(intRow, 2)
            .Update
        Next intRow
        .Close
    End With

    Set rs = Nothing
    Exit Sub

Err_Handler:
    MsgBox Err.Description

End Sub

Answer 2

If you know only ListObject name, there is no other way except opening Excel file like described in @dbmitch answer, but if you know ListObject range, you can transfer the data from this range only to your table without using Excel.Application object, just use SELECT like this:

INSERT INTO InventoryAvail (PartNumber, Quantity) 
SELECT *
  FROM [Inventory$D3:E24] 
    IN "C:\Users\YourName\Desktop\Inventory.xlsx" [Excel 12.0;HDR=YES;IMEX=0];

This will work much faster, than thru the object

UPDATE

Just figured out, that it's possible to query named ranges by name. Syntax like this:

INSERT INTO InventoryAvail (PartNumber, Quantity) 
SELECT *
  FROM [Available] 
    IN "C:\Users\YourName\Desktop\Inventory.xlsx" [Excel 12.0;HDR=YES;IMEX=0];

Please note, that this syntax works with named ranges, for tables such kind data selection doesn't work. Also won't work a named range if it was assigned to the same range as table. But it's possible to define named range, which includes table range, but not exactly the same