Импорт объекта Excel Listobject в таблицу Access

Question

У меня есть база данных Access с таблицей с именем InventoryAvail. Я хотел бы нажать кнопку в Access и импортировать конкретный объект Listobject из определенного файла Excel, чтобы заполнить таблицу InventoryAvail.

Какой код VBA мне нужно поставить на событие кнопки?

Docmd.Transferspreadsheet не будет работать, потому что я не хочу, чтобы все данные из электронной таблицы, я хочу только то, что находится в конкретном объекте Listobject.

Файл Excel называется Inventory и находится на моем рабочем столе. Рабочий лист называется Inventory. Листобъект называется Доступным и имеет два столбца: Part, Qty,

Таблица InventoryAvail имеет два столбца: PartNumber а также Quantity,

Мне нужны данные из Excel Listobject Available для импорта в таблицу Access InventoryAvail

Answer 1

Вы правы, что по какой-то причине DoCmd.TransferSpreadsheet не работает для именованных диапазонов. Я смог взломать его один раз, вытащив из него адрес, но мне пришлось открыть книгу.

В любом случае я не вижу возможности открыть книгу

Это должно работать для вас.

шаги:

• открывает электронную таблицу
• считывает данные списка в массив
• открывает набор записей, используя вашу таблицу
• передает каждую строку данных в таблицу, используя AddNew метод
• очищает

Вот код

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

Если вам известно только имя ListObject, нет другого способа, кроме как открыть файл Excel, как описано в ответе @dbmitch, но если вы знаете диапазон ListObject, вы можете перенести данные из этого диапазона только в свою таблицу, не используя Excel.Application объект, просто используйте SELECT следующим образом:

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

Это будет работать намного быстрее, чем через объект

ОБНОВИТЬ

Просто выяснил, что можно запрашивать именованные диапазоны по имени. Синтаксис такой:

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

Обратите внимание, что этот синтаксис работает с именованными диапазонами, для таблиц такой выбор данных не работает. Также не будет работать именованный диапазон, если он был назначен тому же диапазону, что и таблица. Но можно определить именованный диапазон, который включает в себя диапазон таблицы, но не точно такой же