Экспорт таблиц из access в excel и наоборот

@kiryl · Регистрация: 08.08.2013

Author24 — интернет-сервис помощи студентам

Как импортировать таблицу excel в access и наоборот в vba?

Аксима · 16.08.2013, 13:29

Здравствуйте, kiryl,

Задача в общем виде нетривиальная, но после нескольких часов мучений мне удалось написать универсальную процедуру, которая осуществляет импорт и экспорт в обеих направлениях.

Надеюсь, это именно то, что вам нужно.

Универсальный экспортер - импортер Access<->Excel

Visual Basic

Const ARG_PRV = "Provider="
Const ARG_SRC = "Data Source="
Const ARG_SCR = "Persist Security Info="
Const ARG_EPS = "Extended Properties="
 
Enum ExportDirections
    EXPORT_ACC_TO_EXC 'Экспорт осуществляется из Access в Excel.
    EXPORT_EXC_TO_ACC 'Экспорт осуществляется из Excel в Access.
End Enum
 
Sub AccessExcel_2007_2010_Transfer(ByVal direction As ExportDirections)
 
    Dim i As Long, n As Long, s As String, flds() As String, fldData() As String
    Dim p As String, prov As String, cnnStrAcc As String, cnnStrExc As String
    Dim dBAcc As String, dBExc As String, srcAcc As String, srcExc As String
    Dim rst As Object, cmd As Object, cat As Object, tbl As Object
    Dim cnnAcc As Object, cnnExc As Object, oAcc As Object, f As Boolean
    
    p = "C:\Temp\" 'Папка, в которой хранится исходный файл Access или файл Excel.
    Select Case direction
        Case EXPORT_ACC_TO_EXC
            With Application.FileDialog(msoFileDialogFilePicker)
                .AllowMultiSelect = False
                .InitialFileName = p
                .Title = "Выберите файл Access для экспорта данных в Excel."
                .Filters.Clear
                .Filters.Add Description:="Файлы Access 2007-2010", Extensions:="*.accdb"
                If .Show = False Then Exit Sub
                s = .SelectedItems(1)
                dBAcc = Mid(s, InStrRev(s, "\") + 1)
            End With
            dBExc = "Export.xlsx"
        Case EXPORT_EXC_TO_ACC
            With Application.FileDialog(msoFileDialogFilePicker)
                .AllowMultiSelect = False
                .InitialFileName = p
                .Title = "Выберите файл Excel для экспорта данных в Excel."
                .Filters.Clear
                .Filters.Add Description:="Файлы Excel 2007-2010", Extensions:="*.xlsx"
                If .Show = False Then Exit Sub
                s = .SelectedItems(1)
                dBExc = Mid(s, InStrRev(s, "\") + 1)
            End With
            If Dir(p & "Export.accdb") = "" Then
                On Error GoTo OpenAccess
                Set oAcc = GetObject(Class:="Access.Application")
                On Error GoTo 0
                oAcc.DBEngine.CreateDatabase p & "Export.accdb", ";LANGID=0x0419;CP=1251;COUNTRY=0"
                If f Then oAcc.Quit
                Set oAcc = Nothing
            End If
            dBAcc = "Export.accdb"
        Case Else
            Debug.Print "Неверно указано направление экспорта."
            Exit Sub
    End Select
    prov = ARG_PRV & "Microsoft.ACE.OLEDB.12.0"
    srcAcc = ARG_SRC & p & dBAcc
    srcExc = ARG_SRC & p & dBExc
    cnnStrAcc = Join(Array(prov, srcAcc, ARG_SCR & "True"), "; ")
    cnnStrExc = Join(Array(prov, srcExc, ARG_EPS & "Excel 12.0 Xml"), "; ")
    Set cnnAcc = CreateObject("ADODB.Connection")
    Set cnnExc = CreateObject("ADODB.Connection")
    cnnAcc.Open cnnStrAcc
    cnnExc.Open cnnStrExc
    Set cat = CreateObject("ADOX.Catalog")
    Select Case direction
        Case EXPORT_ACC_TO_EXC: cat.ActiveConnection = cnnAcc
        Case EXPORT_EXC_TO_ACC: cat.ActiveConnection = cnnExc
    End Select
    With cat.Tables
        For i = 0 To .Count - 1
            If Left(.Item(i).Name, 4) <> "MSys" Then
                s = .Item(i).Name
                Exit For
            End If
        Next i
    End With
    If s <> "" Then
        With cat.Tables(s).Columns
            n = .Count - 1
            ReDim flds(n) As String
            For i = 0 To n
                flds(i) = .Item(i)
            Next i
        End With
    End If
    Select Case direction
        Case EXPORT_ACC_TO_EXC: cat.ActiveConnection = cnnExc
        Case EXPORT_EXC_TO_ACC: cat.ActiveConnection = cnnAcc
    End Select
    Set tbl = CreateObject("ADOX.Table")
    Select Case direction
        Case EXPORT_ACC_TO_EXC: tbl.Name = s
        Case EXPORT_EXC_TO_ACC: tbl.Name = Replace(s, "$", "")
    End Select
    For i = 0 To n
        tbl.Columns.Append flds(i)
    Next i
    cat.Tables.Append tbl
    Set tbl = Nothing
    Set cat = Nothing
    Set rst = CreateObject("ADODB.Recordset")
    Set cmd = CreateObject("ADODB.Command")
    Select Case direction
        Case EXPORT_ACC_TO_EXC
            rst.ActiveConnection = cnnAcc
            cmd.ActiveConnection = cnnExc
        Case EXPORT_EXC_TO_ACC
            rst.ActiveConnection = cnnExc
            cmd.ActiveConnection = cnnAcc
    End Select
    Select Case direction
        Case EXPORT_ACC_TO_EXC
            rst.Open "SELECT " & Join(flds, ", ") & " FROM " & s
        Case EXPORT_EXC_TO_ACC
            rst.Open "SELECT " & Join(flds, ", ") & " FROM [" & s & "]"
    End Select
    ReDim fldData(n) As String
    If direction = EXPORT_EXC_TO_ACC Then s = Replace(s, "$", "")
    While Not rst.EOF
        For i = 0 To n
            fldData(i) = rst.Fields(i)
        Next i
        cmd.CommandText = "INSERT INTO " & s & " VALUES ('" & Join(fldData, "', '") & "')"
        cmd.Execute
        rst.MoveNext
    Wend
    Set cmd = Nothing
    rst.Close
    Set rst = Nothing
    Set cnnExc = Nothing
    Set cnnAcc = Nothing
    Exit Sub
 
OpenAccess:
    Set oAcc = CreateObject(Class:="Access.Application")
    f = True
    Resume Next
 
End Sub
 
Sub MakeExport()
    AccessExcel_2007_2010_Transfer EXPORT_ACC_TO_EXC
End Sub

С уважением,

Aksima

mobile · 16.08.2013, 15:01

Aksima, гораздо быстрее при импорте из Excel в Access пользоваться одним запросом Insert Into Таблица Select, нежели проходом по рекордсету и последующей записью через Insert Into Таблица Values

В противоположном направлении также намного быстрее определить рекордсет и воспользоваться замечательным методом Excel: CopyFromRecordset

Аксима · 16.08.2013, 17:12

mobile, ставка была на универсальность кода, чтобы потенциально его можно было приспособить на обмен данными между любыми двумя OLE DB. С этой точки зрения (совместимости) способ переноса данных по одной строчке вне конкуренции.

Но все равно замечание важное. Спасибо.

С уважением,

Aksima

~~dev.Free~~ · 16.08.2013, 20:39

Способы передачи данных из Visual Basic в Excel
Код статьи: 247412 - Список продуктов, к которым относится данная статья.
Развернуть все | Свернуть все
Аннотация
В данной статье рассматриваются способы передачи данных в Microsoft Excel из приложения Microsoft Visual Basic. В статье также представлены преимущества и недостатки каждого из способов, что позволяет пользователю выбрать наиболее подходящий способ для конкретной ситуации.
Перейти к началу страницы | Отправить отзыв
Дополнительная информация
Чаще всего для передачи данных в книгу Excel используется программирование объектов (автоматизация). Этот способ обладает наибольшим спектром возможностей для указания местоположения данных в книге Excel, а также обеспечивает возможность форматирования книги и настройки различных параметров во время выполнения. Программирование объектов позволяет использовать для передачи данных несколько подходов:

Передача данных по одной ячейке
Передача массива данных в диапазон ячеек
Передача набора записей ADO в диапазон ячеек с помощью способа CopyFromRecordset
Создание в листе Excel объекта QueryTable, содержащего результаты запроса по источнику данных ODBC или OLEDB
Передача данных в буфер обмена с последующей вставкой содержимого буфера обмена в лист Excel

Также существуют способы передачи данных в Excel, не требующие программирования объектов. При работе с серверным приложением рекомендуется освободить клиентов от большого объема обрабатываемых данных. Ниже приведены способы передачи данных, не использующие программирование объектов.

Передача данных в текстовый файл, использующий запятые или знаки табуляции в качестве разделителей, который Excel впоследствии может разобрать на ячейки листа
Передача данных на лист Excel с помощью ADO
Передача данных в Excel с помощью динамического обмена данными (DDE)

В следующих разделах приведены дополнительные сведения о каждом решении.

Примечание. При использовании Microsoft Office Excel 2007 для сохранения книги Excel 2007 можно использовать новый формат файла (XSLX). Для этого найдите следующую строку кода в приведенных ниже примерах:

oBook.SaveAs "C:\Book1.xls"

Замените этот код следующей строкой кода:

oBook.SaveAs "C:\Book1.xlsx"

Кроме того, база данных "Борей" не входит в состав Office 2007 по умолчанию. Тем не менее базу данных "Борей" можно загрузить с веб-узла русской версии Microsoft Office Online.
Перенос данных по одной ячейке с помощью автоматизации
Автоматизация позволяет передавать данные на лист Excel по одной ячейке:

Visual Basic

   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
 
   'Открыть новую книгу Excel
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
 
 
   'Добавить данные в ячейки первого листа новой книги
   Set oSheet = oBook.Worksheets(1)
   oSheet.Range("A1").Value = "Last Name"
   oSheet.Range("B1").Value = "First Name"
   oSheet.Range("A1:B1").Font.Bold = True
   oSheet.Range("A2").Value = "Doe"
   oSheet.Range("B2").Value = "John"
 
   'Сохранить книгу и закрыть Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit

Передача данных по одной ячейке является оптимальным способом передачи небольших объемов данных. Этот способ позволяет помещать данные в любом месте рабочей книги и форматировать ячейки во время выполнения. Однако этот способ не рекомендуется применять при передаче больших объемов данных в книгу Excel. Каждый объект Range, получаемый во время выполнения, вызывает запрос к интерфейсу, поэтому такой способ передачи данных может оказаться очень медленным. Кроме того, в Microsoft Windows 95 и Windows 98 существует ограничение на запросы к интерфейсу, составляющее 64 КБ. При превышении лимита в 64 КБ сервер автоматизации (Excel) может перестать отвечать на запросы или может отображаться сообщение о нехватке памяти. Это ограничение для Windows 95 и Windows 98 рассматривается в следующей статье базы знаний Майкрософт:
216400 Автоматизация COM может привести к зависанию клиентского приложения в Win 95/98 (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
Таким образом, передача данных по одной ячейке допустима только для небольших объемов данных. Для передачи больших объемов данных в Excel следует использовать один из способов, описанных ниже.

Примеры сценариев для автоматизации Excel см. в следующей статье базы знаний Майкрософт:
219151 Использование Visual Basic для автоматизации Microsoft Excel
Передача массива данных в диапазон ячеек листа с помощью программирования объектов
Массив данных можно одновременно передать в диапазон ячеек листа:

Visual Basic

   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
 
   'Открыть новую книгу Excel
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
 
   'Создать массив с 3 столбцами и 100 строками
   Dim DataArray(1 To 100, 1 To 3) As Variant
   Dim r As Integer
   For r = 1 To 100
      DataArray(r, 1) = "ORD" & Format(r, "0000")
      DataArray(r, 2) = Rnd() * 1000
      DataArray(r, 3) = DataArray(r, 2) * 0.7
   Next
 
   'Добавить заголовки в строку 1
   Set oSheet = oBook.Worksheets(1)
   oSheet.Range("A1:C1").Value = Array("Order ID", "Amount", "Tax")
 
   'Передать массив на лист, начиная с ячейки A2
   oSheet.Range("A2").Resize(100, 3).Value = DataArray
   
   'Сохранить книгу и закрыть Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit

Передача большого объема данных с помощью массива происходит значительно быстрее, чем передача данных по одной ячейке. Обратите внимание на строку из приведенного выше сценария, которая одновременно передает данные в 300 ячеек листа:

oSheet.Range("A2").Resize(100, 3).Value = DataArray

Эта строка представляет всего два запроса к интерфейсу (один для объекта Range, возвращаемого методом Range, и один для объекта Range, возвращаемого методом Resize). При этом при передаче данных по одной ячейке потребовалось бы 300 запросов к интерфейсу для объектов Range. Поэтому по возможности рекомендуется выполнять массовый перенос данных, чтобы сократить число запросов к интерфейсу.
Перенос набора записей ADO в диапазон листа с помощью автоматизации
В Excel 2000 появился метод CopyFromRecordset, позволяющий переносить наборы данных ADO (или DAO) в диапазон ячеек листа. Приведенный ниже сценарий является примером автоматизации Excel 2000, Excel 2002 или Office Excel 2003 для переноса содержимого таблицы Orders образца базы данных "Борей" с помощью метода CopyFromRecordset.

Visual Basic

   'Создать набор записей из всех записей таблицы Orders
   Dim sNWind As String
   Dim conn As New ADODB.Connection
   Dim rs As ADODB.Recordset
   sNWind = _
      "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
      sNWind & ";"
   conn.CursorLocation = adUseClient
   Set rs = conn.Execute("Orders", , adCmdTable)
   
   'Создать новую книгу Excel
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
   Set oSheet = oBook.Worksheets(1)
   
   'Передать данные в Excel
   oSheet.Range("A1").CopyFromRecordset rs
   
   'Сохранить книгу и закрыть Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit
   
   'Разорвать соединение
   rs.Close
   conn.Close

Примечание. При использовании версии базы данных "Борей" для Office 2007 необходимо заменить в примере следующую строку кода:

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0

ata Source=" & _ sNWind & ";"

Замените эту строку кода следующей строкой:

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0

ata Source=" & _ sNWind & ";"

В Excel 97 также имеется метод CopyFromRecordset, однако его можно использовать только для набора записей DAO. CopyFromRecordset в Excel 97 не поддерживает ADO.

Дополнительные сведения об использовании ADO и метода CopyFromRecordset см. в следующей статье базы знаний Майкрософт:
246335 Использование программирования объектов для передачи данных из набора записей в Excel (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
Создание объекта QueryTable с помощью программирования объектов
Объект QueryTable представляет собой таблицу, содержащую данные, возвращенные из внешнего источника. При автоматизации Microsoft Excel для создания объекта QueryTable следует просто указать строку подключения к источнику данных OLEDB или ODBC в строке SQL. Далее Excel генерирует набор записей и вставляет его в указанное местоположение на листе. Использование объекта QueryTables обладает несколькими преимуществами по сравнению с использованием метода CopyFromRecordset:

Созданием набора записей и его размещением на листе управляет Excel.
Запрос можно сохранить в объекте QueryTable таким образом, чтобы в дальнейшем его можно было обновить и получить обновленный набор записей.
При добавлении нового объекта QueryTable к листу можно переместить данные, уже находящиеся в ячейках листа, чтобы свободно разместить новые данные (см. свойство RefreshStyle).

Ниже приводится пример сценария, позволяющего автоматизировать Excel 2000, Excel 2002 или Office Excel 2003 для создания нового объекта QueryTable на листе Excel с данными из базы Northwind:

Visual Basic

   'Создать новую книгу Excel
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
   Set oSheet = oBook.Worksheets(1)
   
   'Создать объект QueryTable
   Dim sNWind As String
   sNWind = _
      "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
   Dim oQryTable As Object
   Set oQryTable = oSheet.QueryTables.Add( _
   "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
      sNWind & ";", oSheet.Range("A1"), "Select * from Orders")
   oQryTable.RefreshStyle = xlInsertEntireRows
   oQryTable.Refresh False
   
   'Сохранить книгу и закрыть Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit

Использование буфера обмена
Буфер обмена Windows также может использоваться как механизм передачи данных на лист Excel. Чтобы вставить данные в несколько ячеек листа, можно скопировать строку, в которой столбцы разделены знаками табуляции, а строки – символами возврата каретки. В приведенном ниже сценарии показано, как Visual Basic может использовать буфер обмена для передачи данных в Excel:

Visual Basic

   'Скопировать строку в буфер обмена
   Dim sData As String
   sData = "FirstName" & vbTab & "LastName" & vbTab & "Birthdate" & vbCr _
           & "Bill" & vbTab & "Brown" & vbTab & "2/5/85" & vbCr _
           & "Joe" & vbTab & "Thomas" & vbTab & "1/1/91"
   Clipboard.Clear
 
   Clipboard.SetText sData
   
   'Создать новую книгу Excel
   Dim oExcel As Object
   Dim oBook As Object
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
   
 
   'Вставить данные
   oBook.Worksheets(1).Range("A1").Select
   oBook.Worksheets(1).Paste
   
   'Сохранить книгу и закрыть Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit

Создание текстового файла с разделителями, который Excel может разобрать на строки и столбцы
Excel может открывать файлы с разделителями-запятыми и знаками табуляции и разбирать данные по ячейкам. Этим можно воспользоваться при необходимости передачи большого объема данных в лист Excel с минимальным использованием автоматизации. Этот подход рекомендуется для приложений типа клиент-сервер, поскольку текстовый файл может генерироваться серверным приложением. Затем текстовый файл можно открыть с помощью клиентского приложения, при необходимости используя автоматизацию.

Ниже приведен сценарий, иллюстрирующий создание текстового файла с разделителями-запятыми из набора записей ADO:

Visual Basic

   'Создать набор записей из всех записей таблицы Orders
   Dim sNWind As String
   Dim conn As New ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim sData As String
   sNWind = _
      "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
      sNWind & ";"
   conn.CursorLocation = adUseClient
   Set rs = conn.Execute("Orders", , adCmdTable)
   
   'Сохранить набор записей как файл с символами табуляции в качестве разделителей
   sData = rs.GetString(adClipString, , vbTab, vbCr, vbNullString)
   Open "C:\Test.txt" For Output As #1
   Print #1, sData
   Close #1
    
   'Разорвать соединение
   rs.Close
   conn.Close
   
   'Открыть новый текстовый файл в Excel
   Shell "C:\Program Files\Microsoft Office\Office\Excel.exe " & _
      Chr(34) & "C:\Test.txt" & Chr(34), vbMaximizedFocus

Примечание. При использовании версии базы данных "Борей" для Office 2007 необходимо заменить в примере следующую строку кода:

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0

ata Source=" & _
sNWind & ";"

Замените эту строку кода следующей строкой:

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0

ata Source=" & _
sNWind & ";"

Если файл имеет расширение CSV, Excel открывает его без отображения мастера импорта текста и по умолчанию принимает, что в файле используются разделители-запятые. Если же файл имеет расширение TXT, Excel автоматически разбирает его, используя в качестве разделителей знаки табуляции.

В приведенном выше примере запуск Excel осуществлялся с помощью оператора Shell, а имя файла использовалось как аргумент командной строки. А в этом примере автоматизация не использовалась. Однако при желании можно применить минимум автоматизации, чтобы открыть текстовый файл и сохранить его в формате книги Excel:

Visual Basic

   'Создать новый экземпляр Excel
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   Set oExcel = CreateObject("Excel.Application")
       
   'Открыть текстовый файл
   Set oBook = oExcel.Workbooks.Open("C:\Test.txt")
   
   'Сохранить книгу Excel и закрыть Excel
   oBook.SaveAs "C:\Book1.xls", xlWorkbookNormal
   oExcel.Quit

Дополнительные сведения об операциях ввода-вывода файлов из приложения Visual Basic см. в следующей статье базы знаний Майкрософт:
172267 RECEDIT.VBP демонстрирует ввод-вывод файлов в Visual Basic (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
Передача данных на лист Excel с помощью ADO
С помощью Microsoft Jet OLE DB Provider можно добавлять записи в таблицу существующей книги Excel. «Таблицей» в Excel считается диапазон с заданным именем. Первая строка диапазона содержит заголовки (или имена полей), а все последующие строки – записи. Ниже приведен пример создания книги с пустой таблицей MyTable.
Excel 97, Excel 2000 и Excel 2003

Откройте новую книгу Excel.
Добавьте следующие заголовки в ячейки A1:B1 листа Sheet1:

A1: FirstName B1: LastName
Выровняйте ячейку B1 по правому краю.
Выделите A1:B1.
В меню Вставка выберите Имя, а затем Присвоить. Введите имя MyTable и нажмите кнопку OK.
Сохраните новую книгу как C:\Book1.xls и закройте Excel.

Чтобы добавить записи в таблицу MyTable с помощью ADO, понадобится сценарий приблизительно следующего вида:

'Создать новый объект подключения для Book1.xls
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Bill', 'Brown')"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Joe', 'Thomas')"
conn.Close

Excel 2007

В Excel 2007 создайте книгу.
Добавьте следующие заголовки в ячейки A1:B1 листа "Лист1":

A1: FirstName B1: LastName
Выровняйте ячейку B1 по правому краю.
Выделите диапазон A1:B1.
На ленте откройте вкладку Формулы и выберите пункт Определить имя. Введите имя MyTable и нажмите кнопку ОК.
Сохраните новую книгу как C:\Book1.xlsx и закройте Excel.

Чтобы добавить записи в таблицу MyTable с помощью ADO, используйте код, подобный приведенному ниже.

'Создание объекта соединения для Book1.xls
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Book1.xlsx;Extended Properties=Excel 12.0;"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Scott', 'Brown')"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
" values ('Jane', 'Dow')"
conn.Close

При подобном добавлении записей в таблицу производится форматирование книги. В приведенном выше примере новые поля, добавляемые в столбец B, выравниваются по правому краю. Каждая запись, добавляемая в строку, форматируется так же, как предыдущая.

Обратите внимание на то, что при добавлении в ячейку или ячейки листа запись заменяет любые данные, находившиеся в этих ячейках ранее; другими словами, строки листа не сдвигаются вниз при добавлении новых записей. Это следует иметь в виду при планировании размещения данных на листе.

Примечание. Обновление данных на листе Excel с помощью ADO или DAO невозможно в среде Visual Basic для приложений в Access после установки пакета обновления 2 (SP2) для Office 2003 или обновления для Access 2002, описанного в статье 904018 базы знаний Майкрософт. Однако этот способ можно использовать в среде Visual Basic для приложений в других приложениях Office, например в Word, Excel и Outlook. Дополнительные сведения см. в следующих статьях базы знаний Майкрософт:
904953 Запрещается вносить изменения, добавлять или удалять данные, источником которых являются книги Excel в Office Access 2003 или в Access 2002
904018 Описание обновления для Access 2002: от 18 октября 2005 г.

Дополнительные сведения об использовании ADO для доступа к книгам Excel см. в следующих статьях базы знаний Майкрософт:
195951 Создание запросов и обновление данных Excel с помощью ADO со страниц ASP (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
Передача данных в Excel с помощью DDE
Наряду с программированием объектов DDE является способом связи с Excel и передачи данных; однако, в противоположность автоматизации и COM, DDE больше не является часто используемым способом связи с другими приложениями и должен использоваться только при отсутствии других решений.

Для передачи данных в Excel с помощью DDE можно воспользоваться одним из следующих способов:

LinkPoke для вставки данных в указанный диапазон ячеек

-или-
LinkExecute для отправки команд, которые будет выполнять Excel.

В приведенном ниже примере показано, как установить связь DDE с Excel таким образом, чтобы модно было поместить данные в ячейки листа и выполнить команды. В этом примере для успешного установления связи DDE с файлом LinkTopic Excel|MyBook.xls книга с именем MyBook.xls уже должна быть открыта в запущенном экземпляре Excel.

Примечание. При использовании Excel 2007 для сохранения книг можно использовать новый формат файла (XLSX). Обязательно обновите имя файла в приведенном ниже примере кода.

Примечание. В данном примере Text1 представляет элемент управления Text Box формы Visual Basic:

'Установить связь DDE с Excel
Text1.LinkMode = 0
Text1.LinkTopic = "Excel|MyBook.xls"
Text1.LinkItem = "R1C1:R2C3"
Text1.LinkMode = 1

'Вставить текст из Text1 в ячейки R1C1:R2C3 файла MyBook.xls
Text1.Text = "one" & vbTab & "two" & vbTab & "three" & vbCr & _
"four" & vbTab & "five" & vbTab & "six"
Text1.LinkPoke

'Выполнить следующие команды – выбрать ячейку A1 (R1C1) и изменить шрифт
'format
Text1.LinkExecute "[SELECT(""R1C1"")]"
Text1.LinkExecute "[FONT.PROPERTIES(""Times New Roman"",""Bold"",10)]"

'Разорвать связь DDE
Text1.LinkMode = 0

При использовании метода LinkPoke с Excel необходимо указать диапазон в формате строка-столбец (R1C1) для LinkItem. Если данные вставляются в несколько ячеек, можно использовать строку, в которой столбцы разделены символами табуляции, а строки – символами возврата каретки.

Если метод LinkExecute используется для выполнения команды в Excel, синтаксис команды должен соответствовать языку Excel Macro Language (XLM). Документация по XLM не входит в состав Excel 97 и более поздних версий. Дополнительные сведения о получении документации по XLM см. в следующей статье базы знаний Майкрософт:
143466 Файл Macro97.exe доступен для загрузки (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
DDE не является рекомендуемым способом связи с Excel. Программирование объектов предоставляет больше возможностей и обеспечивает лучший доступ к новым функциям Excel.
Перейти к началу страницы | Отправить отзыв
Ссылки
Дополнительные сведения см. в следующей статье базы знаний Майкрософт:
306022 Передача данных в книгу Excel с помощью Visual Basic .NET (Эта ссылка может указывать на содержимое полностью или частично на английском языке)
Перейти к началу страницы | Отправить отзыв
Свойства
Код статьи: 247412 - Последнее изменение :: 20 марта 2007 г. - Редакция: 8.0
Информация в данной статье относится к следующим продуктам.

Microsoft Office Excel 2007
Microsoft Office Excel 2003
Microsoft Excel 2002 Standard Edition
Microsoft Excel 2000 Standard Edition
Microsoft Excel 97 Standard Edition
Microsoft Visual Basic for Applications 5.0
Microsoft Visual Basic for Applications 6.0
Microsoft Visual Basic 6.0 Enterprise Edition
Microsoft Visual Basic 6.0 Professional Edition

Ключевые слова:
kbautomation kbdde kbinfo KB247412
Перейти к началу страницы | Отправить отзыв

@tems-ya · 08.07.2016, 16:32

Не смотря на обилие текста, считаю, что тема импорта данных из Excel в Acсess методами Excel так и не раскрыта (впрочем как и на просторах Интернета). Понятна только построчная передача данных в access. При больших таблицах это тупик.
Остается непонятным возможно ли как-то передать целиком весь recordset в Acсess ? И если возможно то как...?
Если передавать через SQL весь лист Excel в Acсess, то в Acсess вставляются пустые строки...

Помогайте, коллеги! У кого рабочий пример есть?

Добавлено через 18 часов 35 минут
Сам спросил, сам ответил...

Visual Basic

Private Sub Add_Data_From_Excel_to_DB(ByVal xlFile As String, ByVal xlSheetName As String, ByVal dbFile As String, ByVal dbTableName As String)
 
    Dim cn, dbcn As ADODB.Connection
    Dim strSQL As String
    
   '------------------------------------------------------------------------------------------------------------
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & xlFile & "; Extended Properties=Excel 8.0"
   '------------------------------------------------------------------------------------------------------------
    Set dbcn = New ADODB.Connection
    dbcn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & dbFile
   '------------------------------------------------------------------------------------------------------------
 
    strSQL = "INSERT INTO [" & dbTableName & "] SELECT * FROM [" & xlSheetName & "$] IN '" & xlFile & "' [Excel 12.0; hdr=yes;] [B]WHERE [" & xlSheetName & "$].WEEK IS NOT NULL[/B]"
 
    dbcn.Execute strSQL  ', adExecuteNoRecords
        
    cn.Close
    dbcn.Close
    
    Set cn = Nothing
    Set dbcn = Nothing
 
End Sub

@час · 10.08.2016, 13:58

Спасибо.

@dan24 · 11.10.2017, 13:22

Как пользоваться универсальной функцией?

@dan24 · 23.10.2017, 13:02

Сообщение от Аксима

With Application.FileDialog(msoFileDialogFilePicker) .AllowMultiSelect = False .InitialFileName = p .Title = "Выберите файл Access для экспорта данных в Excel." .Filters.Clear .Filters.Add Description:="Файлы Access 2007-2010", Extensions:="*.accdb"

Ругается на данную строчку кода.
Пишет фаил диалог из фэилд.
Папку я указываю верную, только на английском.
ЧТо не так то?

Visual Basic

'Создать новую книгу Excel
 Dim oExcel As Object
 Dim oBook As Object
 Dim oSheet As Object
 Set oExcel = CreateObject("Excel.Application")
 Set oBook = oExcel.Workbooks.Add
 Set oSheet = oBook.Worksheets(1)
'Создать объект QueryTable
 Dim sNWind As String
 sNWind = _
 "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
 Dim oQryTable As Object
 Set oQryTable = oSheet.QueryTables.Add( _
 "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
 sNWind & ";", oSheet.Range("A1"), "Select * from Orders")
 oQryTable.RefreshStyle = xlInsertEntireRows
 oQryTable.Refresh False
'Сохранить книгу и закрыть Excel
 oBook.SaveAs "C:\Book1.xls"
 oExcel.Quit[/quote]

Не работает. Пишет: Сбой инициализацииисточника данных.
И это даже при указании копии базы данных, которая звкрыта.

@kiryl 0 / 0 / 0 Регистрация: 08.08.2013 Сообщений: 5
		1
	Экспорт таблиц из access в excel и наоборот 15.08.2013, 19:23. Показов 74562. Ответов 8 Метки нет (Все метки) Как импортировать таблицу excel в access и наоборот в vba? 0

mobile 26815 / 14494 / 3192 Регистрация: 28.04.2012 Сообщений: 15,782
	16.08.2013, 15:01	3
	Aksima, гораздо быстрее при импорте из Excel в Access пользоваться одним запросом Insert Into Таблица Select, нежели проходом по рекордсету и последующей записью через Insert Into Таблица Values В противоположном направлении также намного быстрее определить рекордсет и воспользоваться замечательным методом Excel: CopyFromRecordset 3

Аксима 6082 / 1326 / 195 Регистрация: 12.12.2012 Сообщений: 1,023
	16.08.2013, 17:12	4
	mobile, ставка была на универсальность кода, чтобы потенциально его можно было приспособить на обмен данными между любыми двумя OLE DB. С этой точки зрения (совместимости) способ переноса данных по одной строчке вне конкуренции. Но все равно замечание важное. Спасибо. С уважением, Aksima 0

@час 48 / 48 / 2 Регистрация: 07.03.2011 Сообщений: 203
	10.08.2016, 13:58	7
	Спасибо. 0

@dan24 167 / 106 / 30 Регистрация: 19.01.2013 Сообщений: 847
	11.10.2017, 13:22	8
	Как пользоваться универсальной функцией? 0