Как экспортировать данные из Excel в Access через Excel Macro, когда открыто несколько экземпляров Excel

Question

Я использую макрос Excel для определения диапазона данных в Excel, а затем вызываю "objAccess.DoCmd.TransferSpreadsheet" для импорта данных из этого диапазона в таблицу Access. Этот импорт не работает все время.

Я пришел к выводу, что макрос работает нормально, когда открыт только один экземпляр Excel. Тем не менее, импорт данных не удается, когда другой экземпляр уже открыт. В последнем случае открывается база данных Access, и файл Excel, из которого я запускаю макрос, открывается заново (в режиме только для чтения) в другом экземпляре Excel. Фактической ошибки нет, но требуемый импорт не выполняется. Почему это происходит?

Sub Excel_2_Access()

    Dim strPath As String
    Dim strwbPath As String
    Dim strRange As String
    Dim objAccess As Access.Application
    Dim wbActive As Workbook

'get database path
    strPath = Worksheets("error").Range("Access_DB_Path").Value & "\" & Worksheets("error").Range("Access_DB").Value

'open database
    Set objAccess = New Access.Application
    Call objAccess.OpenCurrentDatabase(strPath)
    objAccess.Visible = True

'access import
    Worksheets("error").Columns("P:P").Calculate
    Set wbActive = ActiveWorkbook
    strwbPath = Application.ActiveWorkbook.FullName
    strRange = "error!M2:M" & (Worksheets("error").Range("WKN_count").Value + 2)
    Call objAccess.DoCmd.TransferSpreadsheet(acImport, 8, "WKN_Mapping", strwbPath, True, strRange)
    objAccess.Forms("MX_Import").Refresh

End Sub

Поскольку макрос довольно короткий, я включил весь код для вашей справки. Тем не менее, я не думаю, что способ, которым указан диапазон или указаны имена, действительно имеет отношение к вопросу.

Желательным результатом будет наличие макроса Excel, который выполняет перенос из Excel в Access, независимо от того, открыты ли другие экземпляры Excel или нет.

Существует ли такая вещь, как основной экземпляр Excel (первый, который был открыт), который имеет особый статус? Есть ли способ предоставить конкретный экземпляр Excel, в котором находится книга, при вызове функции Access из Excel? Или есть более надежный способ передачи данных, который обычно позволяет избежать этой проблемы в нескольких случаях?

Answer 1

Я протестировал подход, который открывает Access db и запускает TransferSreadsheet. Не нужно устанавливать объект рабочей книги (ваш набор кодов, но даже не использует). Он работал без ошибок каждый раз. Я попытался установить объект Access Visible, но база данных появляется и сразу же закрывается, хотя импорт данных происходит. Установить справочную библиотеку: Microsoft Access xx Object Library.

Sub test()
Dim ac As Access.Application, strRange As String
Set ac = New Access.Application
strRange = "Sheet1!A1:E3"
ac.OpenCurrentDatabase "C:\Users\June\LL\Umpires.accdb"
ac.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Rates", ThisWorkbook.FullName, True, strRange
End Sub

Пример кода Excel VBA, который экспортирует все строки листа в существующую таблицу в Access без открытия файла Access. Установка соединения ADODB делает доступным метод Execute. Этот подход работает быстрее. Установить справочную библиотеку: Microsoft ActiveX Data Objects xx Library.

Sub test()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=Excel 8.0"
cn.Execute "INSERT INTO Rates(RateLevel, Rate, Pos, EffDate) IN 'C:\Users\June\LL\Umpires.accdb' " & _
           "SELECT RateLevel,Rate,Pos,EffDate FROM [Sheet1$];"
cn.Close
Set cn = Nothing
End Sub