Методы передачи данных в Excel из Visual Basic

  • Статья
  • Применяется к:
    Excel 2010, Office Excel 2007, Office Excel 2003

Сводка

В этой статье рассматриваются многочисленные методы передачи данных в Microsoft Excel из приложения Microsoft Visual Basic. В этой статье также представлены преимущества и недостатки для каждого метода, чтобы вы могли выбрать наиболее подходящее решение.

Дополнительные сведения

Наиболее часто используется для передачи данных в книгу Excel— автоматизация. Автоматизация обеспечивает максимальную гибкость для указания расположения данных в книге, а также возможность форматирования книги и внесения различных параметров во время выполнения. С помощью службы автоматизации можно использовать несколько подходов для передачи данных:

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

Существуют также методы, которые можно использовать для передачи данных в Excel, для которых не обязательно требуется автоматизация. Если вы используете сервер приложения, это может быть хорошим подходом для переноса основной части обработки данных от клиентов. Для передачи данных без автоматизации можно использовать следующие методы:

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

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

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

oBook.SaveAs "C:\Book1.xls"

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

oBook.SaveAs "C:\Book1.xlsx"

Кроме того, база данных Northwind не включена в Office 2007 по умолчанию. Однако базу данных Northwind можно скачать из Microsoft Office Online.

Использование службы автоматизации для передачи данных по ячейке

С помощью службы автоматизации можно передавать данные на лист по одной ячейке за раз:

Dim oExcel As Object    
Dim oBook As Object    
Dim oSheet As Object 
    
'Start a new workbook in Excel    
Set oExcel = CreateObject("Excel.Application")    
Set oBook = oExcel.Workbooks.Add
      
'Add data to cells of the first worksheet in the new workbook    
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"     

'Save the Workbook and Quit Excel    
oBook.SaveAs "C:\Book1.xls"    
oExcel.Quit

Передача данных по ячейкам может быть вполне приемлемым подходом, если объем данных невелик. Вы можете размещать данные в любом месте книги и условно форматировать ячейки во время выполнения. Однако этот подход не рекомендуется, если у вас есть большой объем данных для передачи в книгу Excel. Каждый объект Range, который вы получаете во время выполнения, приводит к запросу интерфейса, поэтому передача данных таким образом может выполняться медленно. Кроме того, Microsoft Windows 95 и Windows 98 имеют ограничение в 64 КБ на запросы интерфейса. Если вы достигли или превысили это ограничение в 64 КБ для запросов интерфейса, сервер автоматизации (Excel) может перестать отвечать на запросы или вы можете получить ошибки, указывающие на нехватку памяти.

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

Дополнительные примеры кода для автоматизации Excel см. в статье Автоматизация Microsoft Excel из Visual Basic.

Использование автоматизации для передачи массива данных в диапазон на листе

Массив данных можно передать в диапазон из нескольких ячеек одновременно:

Dim oExcel As Object    
Dim oBook As Object    
Dim oSheet As Object     

'Start a new workbook in Excel    
Set oExcel = CreateObject("Excel.Application")    
Set oBook = oExcel.Workbooks.Add     

'Create an array with 3 columns and 100 rows    
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     

'Add headers to the worksheet on row 1    
Set oSheet = oBook.Worksheets(1)    
oSheet.Range("A1:C1").Value = Array("Order ID", "Amount", "Tax")     

'Transfer the array to the worksheet starting at cell A2    
oSheet.Range("A2").Resize(100, 3).Value = DataArray        

'Save the Workbook and Quit 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 в образце базы данных Northwind с помощью метода CopyFromRecordset.

'Create a Recordset from all the records in the Orders table    
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)        

'Create a new workbook in 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)        

'Transfer the data to Excel    
oSheet.Range("A1").CopyFromRecordset rs        

'Save the Workbook and Quit Excel    
oBook.SaveAs "C:\Book1.xls"    
oExcel.Quit        
'Close the connection    
rs.Close    
conn.Close

Примечание Если вы используете версию Office 2007 базы данных Northwind, необходимо заменить следующую строку кода в примере кода:

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"

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

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ sNWind & ";"

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

Дополнительные сведения об использовании ADO и метода CopyFromRecordset см. в статье Перенос данных из набора записей ADO в 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:

'Create a new workbook in 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)        

'Create the 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        

'Save the Workbook and Quit Excel    
oBook.SaveAs "C:\Book1.xls"    
oExcel.Quit

Использование буфера обмена

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

'Copy a string to the clipboard    
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        

'Create a new workbook in Excel    
Dim oExcel As Object    
Dim oBook As Object    
Set oExcel = CreateObject("Excel.Application")    
Set oBook = oExcel.Workbooks.Add         

'Paste the data    
oBook.Worksheets(1).Range("A1").Select    
oBook.Worksheets(1).Paste        

'Save the Workbook and Quit Excel    
oBook.SaveAs "C:\Book1.xls"    
oExcel.Quit

Создание текстового файла с разделителями, который Excel может анализировать по строкам и столбцам

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

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

'Create a Recordset from all the records in the Orders table    
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)        

'Save the recordset as a tab-delimited file    
sData = rs.GetString(adClipString, , vbTab, vbCr, vbNullString)    
Open "C:\Test.txt" For Output As #1    
Print #1, sData    
Close #1         

'Close the connection    
rs.Close    
conn.Close        

'Open the new text file in Excel    
Shell "C:\Program Files\Microsoft Office\Office\Excel.exe " & _       Chr(34) & "C:\Test.txt" & Chr(34), vbMaximizedFocus

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

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _       
   sNWind & ";"

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

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _       
   sNWind & ";"

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

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

'Create a new instance of Excel    
Dim oExcel As Object    
Dim oBook As Object    
Dim oSheet As Object    
Set oExcel = CreateObject("Excel.Application")            

'Open the text file   
 Set oBook = oExcel.Workbooks.Open("C:\Test.txt")        

'Save as Excel workbook and Quit Excel    
oBook.SaveAs "C:\Book1.xls", xlWorkbookNormal    
oExcel.Quit

Передача данных на лист с помощью ADO

С помощью поставщика Microsoft Jet OLE DB можно добавлять записи в таблицу в существующей книге Excel. Таблица в Excel — это просто диапазон с определенным именем. Первая строка диапазона должна содержать заголовки (или имена полей), а все последующие строки содержат записи. Ниже показано, как создать книгу с пустой таблицей MyTable.

Excel 97, Excel 2000 и Excel 2003

  1. Запустите новую книгу в Excel.

  2. Добавьте следующие заголовки в ячейки A1:B1 листа 1:

    A1: FirstName B1: LastName

  3. Отформатируйте ячейку B1 по правому краю.

  4. Выберите A1:B1.

  5. В меню Вставка выберите Имена, а затем — Определить. Введите имя MyTable и нажмите кнопку ОК.

  6. Сохраните новую книгу как C:\Book1.xls и закройте Excel.

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

'Create a new connection object for 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

  1. В Excel 2007 запустите новую книгу.

  2. Добавьте следующие заголовки в ячейки A1:B1 листа 1:

    A1: FirstName B1: LastName

  3. Отформатируйте ячейку B1 по правому краю.

  4. Выберите A1:B1.

  5. На ленте откройте вкладку Формулы и нажмите кнопку Определить имя. Введите имя MyTable и нажмите кнопку ОК.

  6. Сохраните новую книгу как C:\Book1.xlsx, а затем закройте Excel.

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

'Create a new connection object for 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 после установки Office 2003 с пакетом обновления 2 (SP2) или после установки обновления для Access 2002, включенного в статью базы знаний Майкрософт 904018. Метод хорошо работает в среде Visual Basic для приложений из других приложений Office, таких как Word, Excel и Outlook.

Дополнительные сведения см. в следующей статье:

Нельзя изменять, добавлять или удалять данные в таблицах, связанных с книгой Excel в Office Access 2003 или Access 2002

Дополнительные сведения об использовании ADO для доступа к книге Excel см. в статье Как запрашивать и обновлять данные Excel с помощью ADO из ASP.

Использование DDE для передачи данных в Excel

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:

'Initiate a DDE communication with Excel    
Text1.LinkMode = 0    
Text1.LinkTopic = "Excel|MyBook.xls"    
Text1.LinkItem = "R1C1:R2C3"    
Text1.LinkMode = 1        

'Poke the text in Text1 to the R1C1:R2C3 in MyBook.xls    
Text1.Text = "one" & vbTab & "two" & vbTab & "three" & vbCr & _                             
             "four" & vbTab & "five" & vbTab & "six"    
Text1.LinkPoke        

'Execute commands to select cell A1 (same as R1C1) and change the font  format 
Text1.LinkExecute "[SELECT(""R1C1"")]"    
Text1.LinkExecute "[FONT.PROPERTIES(""Times New Roman"",""Bold"",10)]"        

'Terminate the DDE communication    
Text1.LinkMode = 0

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

При использовании LinkExecute для запроса Excel на выполнение команды необходимо предоставить команду Excel в синтаксисе языка макросов Excel (XLM). Документация по XLM не включена в Excel версии 97 и более поздних.
DDE не рекомендуется использовать для взаимодействия с Excel. Автоматизация обеспечивает максимальную гибкость и предоставляет более широкий доступ к новым функциям Excel.