Способи передачі даних з 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 (*.xlsx). Для цього в наведених нижче прикладах знайдіть такий рядок коду:
oBook.SaveAs "C:\Book1.xls"
Замініть цей код таким рядком:
oBook.SaveAs "C:\Book1.xlsx"
Крім того, за промовчанням база даних "Борей" не входить до складу Office 2007. Проте базу даних "Борей" можна завантажити з веб-сайту Microsoft Office Online.

Перенесення даних по одній клітинці за допомогою автоматизації

Автоматизація дозволяє передавати дані до аркуша Excel по одній клітинці:
   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-автоматизація може призвести до зависання клієнтського застосунку в Windows 95/98 (Це посилання може вказувати на матеріали, повністю або частково викладені англійською мовою.)
Таким чином, передача даних по одній клітинці прийнятна тільки для невеликих обсягів даних. Для передачі до Excel великих обсягів даних слід використовувати один зі способів, описаних далі.

Додаткові приклади коду для автоматизації Excel див. у наступній статті бази знань Майкрософт:
219151 Автоматизація 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 для переносу вмісту таблиці "Замовлення" демонстраційної бази даних "Борей" за допомогою методу CopyFromRecordset.
   'Створити набір записів з усіх записів таблиці "Замовлення"
   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;Data Source=" & _ sNWind & ";"
Замініть цей рядок коду таким рядком:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ sNWind & ";"
В Excel 97 також є метод CopyFromRecordset, проте його можна використовувати тільки з набором записів DAO. Метод CopyFromRecordset в Excel 97 не підтримує ADO.

Додаткові відомості про використання ADO та методу CopyFromRecordset див. у наступній статті бази знань Майкрософт:
246335 Перенесення даних з набору записів 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 з даними з бази даних "Борей":
   'Створити нову книгу 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:
   'Скопіювати рядок до буфера обміну
   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:
   'Створити набір записів з усіх записів таблиці "Замовлення"
   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;Data Source=" & _
      sNWind & ";"
Замініть цей рядок коду таким рядком:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
      sNWind & ";"
Якщо файл має розширення CSV, Excel відкриває його без запуску майстра імпорту тексту та за промовчанням приймає, що у файлі використовуються роздільники-коми. Якщо ж файл має розширення TXT, Excel автоматично обробляє його, використовуючи як роздільники знаки табуляції.

У наведеному вище прикладі запуск Excel здійснюється за допомогою оператора Shell, а ім'я файлу використовується як аргумент командного рядка. Автоматизація в цьому прикладі не використовується. Проте для відкриття текстового файлу та збереження його у форматі книги Excel можна застосувати й деякі засоби автоматизації:
   'Створити новий екземпляр 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 можна додавати записи до таблиці існуючої книги Excel. "Таблицею" в Excel вважається діапазон з визначеним ім'ям. Перший рядок діапазону містить заголовки (або імена полів), а всі наступні рядки — це записи. Нижче надано приклад створення книги з порожньою таблицею MyTable.
Excel 97, Excel 2000 та Excel 2003
  1. Створіть нову книгу Excel.
  2. Додайте такі заголовки в клітинки A1:B1 аркуша Sheet1:

    A1: FirstName B1: LastName
  3. Вирівняйте клітинку B1 за правим краєм.
  4. Виділіть діапазон A1:B1.
  5. У меню Вставка виберіть Імена і потім Визначити. Уведіть ім'я MyTable і натисніть кнопку ОК.
  6. Збережіть нову книгу як 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
  1. Створіть нову книгу в Excel 2007.
  2. Додайте такі заголовки в клітинки A1:B1 аркуша Sheet1:

    A1: FirstName B1: LastName
  3. Вирівняйте клітинку B1 за правим краєм.
  4. Виділіть діапазон A1:B1.
  5. На стрічці відкрийте вкладку Формули та виберіть Визначити ім'я. Уведіть ім'я MyTable і натисніть кнопку ОК.
  6. Збережіть нову книгу як 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. Щоб отримати додаткові відомості, клацніть відповідні номери статей бази знань Microsoft Knowledge Base:
904953 В Office Access 2003 або в Access 2002 неможливо вносити зміни, додавати або видаляти дані, джерелом яких є книги Excel. (Це посилання може вказувати на матеріали, повністю або частково викладені англійською мовою.)
904018 Опис оновлення для Access 2002: 18 жовтня 2005 р. (Це посилання може вказувати на матеріали, повністю або частково викладені англійською мовою.)

Докладніше про використання ADO для доступу до книг Excel див. у наступних статтях бази знань Майкрософт:
195951 Створення запитів та оновлення даних Excel за допомогою ADO зі сторінок ASP. (Це посилання може вказувати на матеріали, повністю або частково викладені англійською мовою.)

Передача даних до Excel за допомогою DDE

Як і автоматизація, DDE є способом зв'язку з Excel та передачі даних. Проте, на відміну від автоматизації та COM, механізм DDE більше не є пріорітетним способом зв'язку з іншими застосунками та має використовуватися тільки за відсутності інших можливостей.

Для передачі даних до Excel за допомогою DDE можна скористатися одним з таких способів:
  • вставка даних у зазначений діапазон клітинок за допомогою методу LinkPoke

    - або -
  • надсилання команд, які буде виконувати Excel, за допомогою методу LinkExecute.
У наступному прикладі показано, як налаштувати 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) та зміни
   'формату шрифта
   Text1.LinkExecute "[SELECT(""R1C1"")]"
   Text1.LinkExecute "[FONT.PROPERTIES(""Times New Roman"",""Bold"",10)]"
   
   'Закрити DDE-зв'язок
   Text1.LinkMode = 0
Якщо з Excel використовується метод LinkPoke, необхідно вказати діапазон у форматі "рядок-стовпець" (R1C1) для методу LinkItem. Якщо дані вставляються в кілька клітинок, можна використовувати рядок, в якому стовпці розділені символами табуляції, а рядки — символами повернення каретки.

Якщо для виконання команди в Excel використовується метод LinkExecute, синтаксис команди має відповідати мові макросів Excel (XLM). Документація з XLM не входить до складу Excel 97 та пізніших версій. Докладніше про одержання документації з XLM див. у наступній статті бази знань Майкрософт:
143466 Файл Macro97.exe доступний для завантаження. (Це посилання може вказувати на матеріали, повністю або частково викладені англійською мовою.)
DDE не рекомендовано як спосіб зв'язку з Excel. Автоматизація надає більше можливостей та забезпечує кращий доступ до нових функцій Excel.

ПОСИЛАННЯ

Щоб отримати додаткові відомості, клацніть номер статті бази знань Microsoft Knowledge Base:
306022 Передача даних до книги Excel за допомогою Visual Basic .NET. (Це посилання може вказувати на матеріали, повністю або частково викладені англійською мовою.)

Властивості

Номер статті: 247412 - Востаннє переглянуто: 28 листопада 2007 р. - Редакція: 8.0
ЗАСТОСОВУЄТЬСЯ ДО:
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 - стандартний випуск
  • Microsoft Excel 2000 - стандартний випуск
  • Microsoft Excel 97 - стандартний випуск
  • Microsoft Visual Basic for Applications 5.0
  • Microsoft Visual Basic для додатків 6.0
  • Microsoft Visual Basic 6.0 Enterprise Edition
  • Microsoft Visual Basic 6.0 Professional Edition
Ключові слова: 
kbinfo kbautomation kbdde KB247412

Надіслати відгук

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com