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

Применяется к: Excel for Microsoft 365, Microsoft Excel 2024, Microsoft Excel 2021, Microsoft Excel 2016

Сводка

В этой статье рассматриваются многочисленные методы передачи данных в 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 имеют ограничение 64K для запросов интерфейса. Если вы достигнете или превышаете это ограничение в 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

Заметка Если вы используете версию Базы данных 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 & ";"

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

Дополнительные сведения об использовании ADO и методе CopyFromRecordset см. в статье "Передача данных из набора записей ADO в Excel с помощью автоматизации".

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

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

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

В следующем коде показано, как автоматизировать Excel 2000, Excel 2002 или Office Excel 2003 для создания таблицы QueryTable на листе Excel с помощью данных из базы данных Northwind Sample Database:

'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

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

Excel 97, Excel 2000 и Excel 2003

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

A1: FirstName B1: LastName
Отформатируйте ячейку B1 по правому краю.
Выберите A1:B1.
В меню "Вставка" выберите "Имена" и нажмите кнопку "Определить". Введите имя MyTable и нажмите кнопку "ОК".
Сохраните новую книгу как 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

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

A1: Имя B1: Фамилия
Отформатируйте ячейку B1 по правому краю.
Выберите A1:B1.
На ленте щелкните вкладку "Формулы" и нажмите кнопку "Определить имя". Введите имя MyTable и нажмите кнопку "ОК".
Сохраните новую книгу как 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 вы указываете диапазон в нотации row-column (R1C1) для LinkItem. Если вы вставляете данные в несколько ячеек, можно использовать строку, в которой столбцы разделены табуляциями, а строки — возвратами каретки.

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

Обратная связь

Были ли сведения на этой странице полезными?

Last updated on 2026-04-01