Передача данных в excel из visual basic
С пособы переноса данных в Excel из Visual Basic
Excel 2010, Office Excel 2007, Office Excel 2003
В этой статье рассказывается о многочисленных способах переноса данных в Microsoft Excel из приложения Microsoft Visual Basic. В этой статье также представлены преимущества и недостатки каждого метода, чтобы вы могли выбрать решение, которое лучше всего подходит для вас.
Д ополнительные сведения
Наиболее распространенный подход, используемый для передачи данных в книгу Excel, — Автоматизация. Автоматизация предоставляет максимальную гибкость для указания расположения данных в книге, а также возможность форматирования книги и создания различных параметров во время выполнения. С помощью автоматизации вы можете использовать несколько подходов для переноса данных:
- Перенос ячейки данных по ячейке
- Передача данных в массиве в диапазон ячеек
- Передача данных из набора записей ADO в диапазон ячеек с помощью метода Копифромрекордсет
- Создание QueryTable на листе Excel, который содержит результат запроса в источнике данных ODBC или OLEDB
- Перенесите данные в буфер обмена, а затем вставьте содержимое буфера обмена в лист Excel.
Кроме того, существуют методы, которые можно использовать для передачи данных в Excel, которые не требуют автоматизации. Если вы используете приложение на стороне сервера, это может быть хорошим подходом для массового обработки данных от клиентов. Для переноса данных без автоматизации можно использовать следующие методы:
- Перенос данных в текстовый файл с разделителями — табуляцией или запятыми, который Excel может выполнить в дальнейшем, разбить на ячейки листа
- Передача данных на лист с помощью ADO
- Передача данных в Excel с помощью динамического обмена данными (DDE)
В следующих разделах приводятся более подробные сведения о каждом из этих решений.
Note (Примечание ) При использовании Microsoft Office Excel 2007 вы можете использовать новый формат файла книги Excel 2007 (XLSX) при сохранении книг. Для этого откройте следующую строку кода в следующих примерах кода:
Замените этот код следующей строкой кода:
Кроме того, база данных "Борей" по умолчанию не включена в 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"
'Save the Workbook and Quit Excel
Передача данных по ячейкам может быть приемлемым способом, если объем данных мал. У вас есть гибкие возможности для размещения данных в любом месте книги и для условного форматирования ячеек во время выполнения. Однако этот подход не рекомендуется, если для передачи в книгу Excel требуется большой объем данных. Каждый объект диапазона, полученный во время выполнения, вызывает запрос интерфейса таким образом, что передача данных таким способом может замедлиться. Кроме того, 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
'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
Если вы переносите данные с помощью массива, а не ячейки по ячейке, вы можете реализовать огромную производительность с большим количеством данных. Рассмотрим эту строку из приведенного выше кода, который передает данные в 300 ячеек листа:
oSheet.Range("A2").Resize(100, 3).Value = DataArray
Эта строка представляет два запроса интерфейса (один для объекта Range, возвращаемого методом Range, а другой для объекта Range, возвращаемого методом resize). С другой стороны, при передаче ячейки данных по ячейке потребуются запросы 300 интерфейсов к объектам Range. Если это возможно, вы можете воспользоваться преимуществами для массового переноса данных и уменьшения количества запросов к интерфейсу.
И спользование автоматизации для переноса набора записей ADO в диапазон листа
В Excel 2000 появился метод Копифромрекордсет, позволяющий перенести набор записей ADO (или DAO) в диапазон на листе. В приведенном ниже коде показано, как можно автоматизировать Excel 2000, Excel 2002 или Office Excel 2003 и перенести содержимое таблицы Orders в образце базы данных Northwind с помощью метода Копифромрекордсет.
'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
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb" conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"
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
'Save the Workbook and Quit Excel
'Close the connection
Note (Примечание ) Если вы используете версию 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 также предоставляет метод Копифромрекордсет, но его можно использовать только с набором записей DAO. Копифромрекордсет с Excel 97 не поддерживает ADO.
Для получения дополнительных сведений об использовании ADO и методе Копифромрекордсет обратитесь к следующей статье базы знаний Майкрософт:
246335 как перенести данные из набора записей ADO в Excel с помощью автоматизации
И спользование автоматизации для создания QueryTable на листе
Объект QueryTable представляет таблицу, созданную на основе данных, возвращенных из внешнего источника данных. При автоматизации Microsoft Excel можно создать QueryTable, просто предоставив строку подключения к OLEDB или источнику данных ODBC, а также строку SQL. Excel предполагает ответственность за создание набора записей и вставку его на лист в указанном расположении. Использование QueryTables предоставляет ряд преимуществ по сравнению с методом Копифромрекордсет:
- Excel обрабатывает создание набора записей и его расположение на листе.
- Запрос можно сохранить вместе с QueryTable, чтобы его можно было обновить позже, чтобы получить обновленный набор записей.
- Когда на лист добавляется новый QueryTable, вы можете указать, что данные, уже существующие в ячейках листа, будут смещены в соответствии с новыми данными (Дополнительные сведения см. в свойстве Рефрешстиле).
В приведенном ниже коде показано, как можно автоматизировать 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
"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
'Save the Workbook and Quit Excel
И спользование буфера обмена
Буфер обмена Windows также можно использовать в качестве механизма передачи данных на лист. Чтобы вставить данные в несколько ячеек листа, можно скопировать строку, в которой столбцы разделяются символами табуляции, а строки — символами возврата каретки. В приведенном ниже коде показано, как Visual Basic может использовать объект Clipboard для передачи данных в 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"
'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'Save the Workbook and Quit Excel
С оздание текстового файла с разделителями, который 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
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb" conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"
Set rs = conn.Execute("Orders", , adCmdTable)
'Save the recordset as a tab-delimited file
sData = rs.GetString(adClipString, , vbTab, vbCr, vbNullString)
'Close the connection
'Open the new text file in Excel
Shell "C:\Program Files\Microsoft Office\Office\Excel.exe " & _ Chr(34) & "C:\Test.txt" & Chr(34), vbMaximizedFocus
Примечание Если вы используете версию Office 2007 для базы данных Northwind, необходимо заменить следующую строку кода в примере кода:
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Замените эту строку кода следующей строкой кода:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
Если текстовый файл имеет расширение. Расширение 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
П ередача данных на лист с помощью ADO
С помощью поставщика OLE DB для Microsoft Jet можно добавлять записи в таблицу в существующей книге Excel. "Таблица" в Excel — это просто диапазон с определенным именем. Первая строка диапазона должна содержать заголовки (или имена полей), а все последующие строки содержат записи. В следующей процедуре показано, как создать книгу с пустой таблицей с именем MyTable.
E xcel 97, Excel 2000 и Excel 2003
- Создайте новую книгу в Excel.
- Добавьте следующие заголовки в ячейки a1: B1 из Лист1:
A1: имя B1: LastName
Чтобы добавить записи в 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')"
- В Excel 2007 создайте новую книгу.
- Добавьте следующие заголовки в ячейки a1: B1 из Лист1:
A1: имя B1: LastName
Чтобы добавить записи в таблицу 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')"
При добавлении записей в таблицу таким образом форматирование в книге сохраняется. В предыдущем примере новые поля, добавленные в столбец 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
Дополнительную информацию об использовании ADO для доступа к книге Excel можно узнать, как запрашивать и обновлять данные Excel с помощью ADO из ASP .
И спользование DDE для передачи данных в Excel
DDE является альтернативой автоматизации для связи с Excel и передачи данных; Однако с появлением автоматизации и COM-интерфейс DDE больше не является предпочтительным методом связи с другими приложениями и его следует использовать только в том случае, если вы не можете использовать другие решения.
Для передачи данных в Excel с помощью DDE можно использовать метод Линкпоке для обмена данными с определенным диапазоном ячеек или с помощью метода Линкексекуте для отправки команд, которые будут выполняться приложением Excel.
В приведенном ниже примере кода показано, как установить сеанс DDE с Excel, чтобы можно было выполнять ввод данных в ячейки листа и выполнять команды. С помощью этого примера можно успешно установить сеанс DDE в Линктопик Excel | Мибук. xls книга с именем Мибук. xls должна быть уже открыта в запущенном экземпляре Excel.
При использовании Excel 2007 можно сохранить книги, используя новый формат файла XLSX. Убедитесь, что вы обновляете имя файла в приведенном ниже примере кода.
Note (Примечание ) В этом примере Текст1 представляет элемент управления "текстовое поле" в форме Visual Basic:
'Initiate a DDE communication with Excel
'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"
'Execute commands to select cell A1 (same as R1C1) and change the font format
Text1.LinkExecute "[FONT.PROPERTIES(""Times New Roman"",""Bold"",10)]"
'Terminate the DDE communication
При использовании Линкпоке с Excel необходимо указать диапазон в нотации Row-Column (R1C1) для Линкитем. При Покинг данных к нескольким ячейкам можно использовать строку, в которой столбцы разделяются символами табуляции, а строки — символами возврата каретки.
При использовании Линкексекуте для выполнения команды в Excel необходимо дать команду в виде синтаксиса в формате макросов Excel (XLM). Документация по XLM не входит в состав Excel версий 97 и более поздних версий. DDE не является рекомендуемым решением для связи с Excel. Автоматизация обеспечивает максимальную гибкость и предоставляет вам дополнительный доступ к новым функциям, которые предлагает Excel.
Вы можете перенести содержимое набора записей ADO на лист Microsoft Excel, автоматизируя Excel. Способ, который можно использовать, зависит от версии Excel, для которой выполняется Автоматизация. Excel 97, Excel 2000 и Excel 2002 имеют метод Копифромрекордсет, который можно использовать для переноса объекта Recordset в диапазон. Копифромрекордсет в Excel 2000 и 2002 можно использовать для копирования либо объекта DAO, либо набора записей ADO. Однако Копифромрекордсет в Excel 97 поддерживает только наборы записей DAO. Чтобы перенести набор записей ADO в Excel 97, можно создать массив из набора записей, а затем заполнить диапазон содержимым этого массива.
В этой статье обсуждаются оба подхода. В приведенном примере кода показано, как можно перенести набор записей ADO в Excel 97, Excel 2000, Excel 2002, Excel 2003 или Excel 2007.
Дополнительные сведения
В приведенном ниже примере кода показано, как скопировать набор записей ADO на лист Microsoft Excel с помощью автоматизации из Microsoft Visual Basic. Код сначала проверяет версию Excel. Если обнаруживается Excel 2000 или 2002, используется метод Копифромрекордсет, так как он эффективен и требует меньше кода. Однако если обнаруживается Excel 97 или более ранняя версия, набор записей сначала копируется в массив с помощью метода GetRows объекта Recordset объекта ADO. Затем массив передается таким образом, чтобы записи направляются в первое измерение (в строках), а поля — во второе измерение (в столбцах). Затем массив копируется на лист Excel путем присвоения массива диапазону ячеек. (Массив копируется за один шаг, а не циклически по каждой ячейке на листе.)
В примере кода используется пример базы данных Northwind, которая входит в состав Microsoft Office. Если вы выбрали папку по умолчанию при установке Microsoft Office, база данных находится в:
\Program Files\Microsoft Оффице\оффице\самплес\норсвинд.МДБ
Если база данных "Борей" расположена в другой папке на компьютере, необходимо изменить путь к базе данных в приведенном ниже коде.
Если в системе не установлена база данных "Борей", для установки образцов баз данных можно использовать параметр "Добавить/удалить" для установки Microsoft Office.
Note (Примечание ) База данных "Борей" не устанавливается при установке 2007 Microsoft Office. Чтобы получить базу данных Northwind 2007, посетите следующий веб-сайт корпорации Майкрософт:
Действия по созданию примера
Запустите Visual Basic и создайте новый стандартный проект EXE. По умолчанию создается форма Form1.
Добавьте CommandButton в Form1.
Выберите Референцесфром меню проект. Добавьте ссылку на библиотеку Microsoft ActiveX Data Objects 2,1.
Вставьте приведенный ниже код в раздел Code формы Form1:
Нажмите клавишу F5, чтобы запустить проект. Откроется форма Form1.
Нажмите CommandButton на Form1 и обратите внимание, что содержимое таблицы Заказы отображается в новой книге Excel.
Использование Копифромрекордсет
Ошибка во время выполнения 430: класс не поддерживает автоматизацию или не поддерживает ожидаемый интерфейс. В этом примере кода можно избежать этой ошибки путем проверки версии Excel, чтобы не использовать Копифромрекордсет для версии 97.
Note (Примечание ) При использовании Копифромрекордсет следует иметь в виду, что используемый набор записей ADO или DAO не может содержать поля объекта OLE или массивы данных, такие как иерархические наборы записей. Если вы включаете поля любого типа в набор записей, метод Копифромрекордсет завершается со следующей ошибкой:
Ошибка во время выполнения — 2147467259: сбой метода Копифромрекордсет в диапазоне объектов.
Использование GetRows
Если обнаруживается Excel 97, используйте метод GetRows объекта Recordset ADO, чтобы скопировать набор записей в массив. Если вы назначаете массиву, возвращаемому методом GetRows, диапазону ячеек на листе, данные передаются по столбцам, а не по строкам. Например, если в наборе записей есть два поля и 10 строк, массив будет отображаться в виде двух строк и 10 столбцов. Поэтому необходимо переставить массив с помощью функции Транспоседим (), прежде чем присваивать массив диапазону ячеек. При назначении массива диапазону ячеек существуют некоторые ограничения, которые следует учитывать.
При назначении массива объекту диапазона Excel применяются следующие ограничения:
Массив не может содержать поля объекта OLE или массив данных, например, иерархические наборы записей. Обратите внимание, что пример кода проверяет это условие и отображает "поле массива", чтобы пользователь знал, что это поле невозможно отобразить в Excel.
Массив не может содержать поля даты, которые имеют дату, предшествующую 1900 году. (Обратитесь к разделу "ссылки" для ссылки на статью базы знаний Майкрософт). Обратите внимание на то, что пример кода форматирует поля даты как строки Variant, чтобы избежать возникновения этой проблемы.
Обратите внимание на использование функции Транспоседим () для переставит массив перед копированием массива на лист Excel. Вместо того чтобы создавать собственную функцию для пересчета массива, можно использовать функцию транспонировать Excel, изменив пример кода, чтобы назначить массив ячейкам, как показано ниже:
Если вы решили использовать метод транспонировать Excel вместо функции Транспоседим () для перебытия массива, следует помнить о следующих ограничениях с помощью метода транспонировать:
- Массив не может содержать элемент, длина которого превышает 255 символов.
- Массив не может содержать значения NULL.
- Число элементов в массиве не может превышать 5461.
Если приведенные выше ограничения не учитываются при копировании массива на лист Excel, может возникнуть одна из следующих ошибок во время выполнения:
Ошибка во время выполнения 13: несоответствие типов
Ошибка во время выполнения 5: недопустимая процедура
Ошибка во время выполнения вызова или аргумента 1004: ошибка, определенная приложением или объектом
Ссылки
Чтобы получить дополнительные сведения об ограничениях на передачу массивов в различные версии Excel, щелкните следующий номер статьи базы знаний Майкрософт:
177991 XL: ограничения передаваемых массивов в Excel с помощью автоматизации
This article discusses numerous methods for transferring data to Microsoft Excel from your Microsoft Visual Basic application. This article also presents the advantages and the disadvantages for each method so that you can choose the solution that works best for you.
More Information
The approach most commonly used to transfer data to an Excel workbook is Automation. Automation gives you the greatest flexibility for specifying the location of your data in the workbook as well as the ability to format the workbook and make various settings at run time. With Automation, you can use several approaches for transferring your data:
- Transfer data cell by cell
- Transfer data in an array to a range of cells
- Transfer data in an ADO recordset to a range of cells using the CopyFromRecordset method
- Create a QueryTable on an Excel worksheet that contains the result of a query on an ODBC or OLEDB data source
- Transfer data to the clipboard and then paste the clipboard contents into an Excel worksheet
There are also methods that you can use to transfer data to Excel that do not necessarily require Automation. If you are running an application server-side, this can be a good approach for taking the bulk of processing the data away from your clients. The following methods can be used to transfer your data without Automation:
- Transfer your data to a tab- or comma-delimited text file that Excel can later parse into cells on a worksheet
- Transfer your data to a worksheet using ADO
- Transfer data to Excel using Dynamic Data Exchange (DDE)
The following sections provide more detail on each of these solutions.
Note When you use Microsoft Office Excel 2007, you can use the new Excel 2007 Workbook (*.xlsx) file format when you save the workbooks. To do this, locate the following line of code in the following code examples:
Replace this code with with the following line of code:
Additionally, the Northwind database is not included in Office 2007 by default. However, you can download the Northwind database from Microsoft Office Online.
Use Automation to transfer data cell by cell
With Automation, you can transfer data to a worksheet one cell at a time:
Transferring data cell by cell can be a perfectly acceptable approach if the amount of data is small. You have the flexibility to place data anywhere in the workbook and can format the cells conditionally at run time. However, this approach is not recommended if you have a large amount of data to transfer to an Excel workbook. Each Range object that you acquire at run time results in an interface request so that transferring data in this manner can be slow. Additionally, Microsoft Windows 95 and Windows 98 have a 64K limitation on interface requests. If you reach or exceed this 64k limit on interface requests, the Automation server (Excel) might stop responding or you might receive errors indicating low memory.
Once more, transferring data cell by cell is acceptable only for small amounts of data. If you need to transfer large data sets to Excel, you should consider one of the solutions presented later.
For more sample code for Automating Excel, see How to automate Microsoft Excel from Visual Basic.
Use automation to transfer an array of data to a range on a worksheet
An array of data can be transferred to a range of multiple cells at once:
If you transfer your data using an array rather than cell by cell, you can realize an enormous performance gain with a large amount of data. Consider this line from the code above that transfers data to 300 cells in the worksheet:
This line represents two interface requests (one for the Range object that the Range method returns and another for the Range object that the Resize method returns). On the other hand, transferring the data cell by cell would require requests for 300 interfaces to Range objects. Whenever possible, you can benefit from transferring your data in bulk and reducing the number of interface requests you make.
Use automation to transfer an ADO recordset to a worksheet range
Excel 2000 introduced the CopyFromRecordset method that allows you to transfer an ADO (or DAO) recordset to a range on a worksheet. The following code illustrates how you could automate Excel 2000, Excel 2002, or Office Excel 2003 and transfer the contents of the Orders table in the Northwind Sample Database using the CopyFromRecordset method.
Note If you use the Office 2007 version of the Northwind database, you must replace the following line of code in the code example:
Replace this line of code with the following line of code:
Excel 97 also provides a CopyFromRecordset method but you can use it only with a DAO recordset. CopyFromRecordset with Excel 97 does not support ADO.
For more information about using ADO and the CopyFromRecordset method, see How to transfer data from an ADO recordset to Excel with automation.
Use automation to create a QueryTable on a worksheet
A QueryTable object represents a table built from data returned from an external data source. While automating Microsoft Excel, you can create a QueryTable by simply providing a connection string to an OLEDB or an ODBC data source along with an SQL string. Excel assumes the responsibility for generating the recordset and inserting it into the worksheet at the location you specify. Using QueryTables offers several advantages over the CopyFromRecordset method:
- Excel handles the creation of the recordset and its placement into the worksheet.
- The query can be saved with the QueryTable so that it can be refreshed at a later time to obtain an updated recordset.
- When a new QueryTable is added to your worksheet, you can specify that data already existing in cells on the worksheet be shifted to accommodate the new data (see the RefreshStyle property for details).
The following code demonstrates how you could automate Excel 2000, Excel 2002, or Office Excel 2003 to create a new QueryTable in an Excel worksheet using data from the Northwind Sample Database:
Use the clipboard
The Windows Clipboard can also be used as a mechanism for transferring data to a worksheet. To paste data into multiple cells on a worksheet, you can copy a string where columns are delimited by tab characters and rows are delimited by carriage returns. The following code illustrates how Visual Basic can use its Clipboard object to transfer data to Excel:
Create a delimited text file that Excel can parse into rows and columns
Excel can open tab- or comma-delimited files and correctly parse the data into cells. You can take advantage of this feature when you want to transfer a large amount of data to a worksheet while using little, if any, Automation. This might be a good approach for a client-server application because the text file can be generated server-side. You can then open the text file at the client, using Automation where it is appropriate.
The following code illustrates how you can create a comma-delimited text file from an ADO recordset:
Note If you use the Office 2007 version of the Northwind database, you must replace the following line of code in the code example:
Replace this line of code with the following line of code:
If your text file has a .CSV extension, Excel opens the file without displaying the Text Import Wizard and automatically assumes that the file is comma-delimited. Similarly, if your file has a .TXT extension, Excel automatically parse the file using tab delimiters.
In the previous code sample, Excel was launched using the Shell statement and the name of the file was used as a command line argument. No Automation was used in the previous sample. However, if so desired, you could use a minimal amount of Automation to open the text file and save it in the Excel workbook format:
Transfer data to a worksheet by using ADO
Using the Microsoft Jet OLE DB Provider, you can add records to a table in an existing Excel workbook. A "table" in Excel is merely a range with a defined name. The first row of the range must contain the headers (or field names) and all subsequent rows contain the records. The following steps illustrate how you can create a workbook with an empty table named MyTable.
Excel 97, Excel 2000, and Excel 2003
Start a new workbook in Excel.
Add the following headers to cells A1:B1 of Sheet1:
A1: FirstName B1: LastName
Format cell B1 as right-aligned.
On the Insert menu, choose Names and then select Define. Enter the name MyTable and click OK.
Save the new workbook as C:\Book1.xls and quit Excel.
To add records to MyTable using ADO, you can use code similar to the following:
Excel 2007
In Excel 2007, start a new workbook.
Add the following headers to cells A1:B1 of Sheet1:
A1: FirstName B1: LastName
Format cell B1 as right-aligned.
On the Ribbon, click the Formulas tab, and then click Define Name. Type the name MyTable, and then click OK.
Save the new workbook as C:\Book1.xlsx, and then quit Excel.
To add records to the MyTable table by using ADO, use code that resembles the following code example.
When you add records to the table in this manner, the formatting in the workbook is maintained. In the previous example, new fields added to column B are formatted with right alignment. Each record that is added to a row borrows the format from the row above it.
You should note that when a record is added to a cell or cells in the worksheet, it overwrites any data previously in those cells; in other words, rows in the worksheet are not "pushed down" when new records are added. You should keep this in mind when designing the layout of data on your worksheets.
The method to update data in an Excel worksheet by using ADO or by using DAO does not work in Visual Basic for Application environment within Access after you install Office 2003 Service Pack 2 (SP2) or after you install the update for Access 2002 that is included in Microsoft Knowledge Base article 904018. The method works well in Visual Basic for Application environment from other Office applications, such as Word, Excel, and Outlook.
For more information, see the following article:
For more information about using ADO to access an Excel workbook, see How To Query and Update Excel Data Using ADO From ASP.
Use DDE to transfer data to Excel
DDE is an alternative to Automation as a means for communicating with Excel and transferring data; however, with the advent of Automation and COM, DDE is no longer the preferred method for communicating with other applications and should only be used when there is no other solution available to you.
To transfer data to Excel using DDE, you can use the LinkPoke method to poke data to a specific range of cell(s), or you use the LinkExecute method to send commands that Excel will execute.
The following code example illustrates how to establish a DDE conversation with Excel so that you can poke data to cells on a worksheet and execute commands. Using this sample, for a DDE conversation to be successfully established to the LinkTopic Excel|MyBook.xls, a workbook with the name MyBook.xls must already be opened in a running instance of Excel.
When you use Excel 2007, you can use the new .xlsx file format to save the workbooks. Make sure that you update the file name in the following code example. In this example, Text1 represents a Text Box control on a Visual Basic form:
When using LinkPoke with Excel, you specify the range in row-column (R1C1) notation for the LinkItem. If you are poking data to multiple cells, you can use a string where the columns are delimited by tabs and rows are delimited by carriage returns.
When you use LinkExecute to ask Excel to carry out a command, you must give Excel the command in the syntax of the Excel Macro Language (XLM). The XLM documentation is not included with Excel versions 97 and later.
DDE is not a recommended solution for communicating with Excel. Automation provides the greatest flexibility and gives you more access to the new features that Excel has to offer.
В этой статье рассматриваются многочисленные методы передачи данных в Microsoft Excel из приложения Microsoft Visual Basic. В этой статье также представлены преимущества и недостатки каждого метода, чтобы можно было выбрать оптимальное решение.
Дополнительная информация
Чаще всего для передачи данных в Excel используется автоматизация. Автоматизация обеспечивает максимальную гибкость при указании расположения данных в книге, а также возможность форматирования книги и создания различных параметров во время выполнения. С помощью службы автоматизации можно использовать несколько подходов для передачи данных:
- Передача ячейки данных по ячейкам
- Передача данных в массиве в диапазон ячеек
- Передача данных из набора записей ADO в диапазон ячеек с помощью метода CopyFromRecordset
- Создание таблицы QueryTable на Excel, содержащей результат запроса к источнику данных ODBC или OLEDB
- Передача данных в буфер обмена, а затем вставка содержимого буфера обмена в Excel листа
Существуют также методы, которые можно использовать для передачи данных в Excel, которые не обязательно требуют автоматизации. Если вы работаете на стороне сервера приложений, это может быть хорошим подходом для того, чтобы отойдите от клиентов к массовой обработке данных. Для передачи данных без автоматизации можно использовать следующие методы:
- Передача данных в текстовый файл с разделителями-табуляции или запятыми, который Excel позже можно проанализировать в ячейки на листе.
- Передача данных на лист с помощью ADO
- Передача данных в Excel с помощью динамических Exchange данных (DDE)
В следующих разделах приведены более подробные сведения о каждом из этих решений.
Примечание При использовании Microsoft Office Excel 2007 при сохранении книг можно использовать новый формат Excel книги 2007 (*.xlsx). Для этого найдите следующую строку кода в следующих примерах кода:
Замените этот код следующей строкой кода:
Кроме того, база данных Northwind не включена в Office 2007 по умолчанию. Однако базу данных Northwind можно скачать из Microsoft Office Online.
Использование службы автоматизации для передачи данных по ячейкам
С помощью службы автоматизации можно передавать данные на лист по одной ячейке за раз:
Передача ячеек данных по ячейкам может быть вполне приемлемым подходом, если объем данных невелик. Вы можете размещать данные в любом месте книги и условно форматировать ячейки во время выполнения. Однако этот подход не рекомендуется, если у вас есть большой объем данных для передачи в Excel книге. Каждый объект Range, полученный во время выполнения, приводит к запросу интерфейса, чтобы передача данных таким образом была медленной. Кроме того, Microsoft Windows 95 и Windows 98 имеют ограничение в 64 КБ на запросы интерфейса. Если вы достигнете или превысите это ограничение в 64 КБ для запросов интерфейса, сервер автоматизации (Excel) может перестать отвечать на запросы или могут возникнуть ошибки, указывающие на нехватку памяти.
Еще раз перенос ячеек данных допускается только для небольших объемов данных. Если необходимо перенести большие наборы данных в Excel, следует рассмотреть одно из решений, представленных позже.
Дополнительные примеры кода для автоматизации Excel см. в статье об автоматизации Microsoft Excel из Visual Basic.
Использование автоматизации для передачи массива данных в диапазон на листе
Массив данных можно передать в диапазон из нескольких ячеек одновременно:
При передаче данных с помощью массива, а не ячейки по ячейкам вы можете реализовать огромное повышение производительности с большим объемом данных. Рассмотрим следующую строку из приведенного выше кода, который передает данные в 300 ячеек листа:
Эта строка представляет два запроса интерфейса (один для объекта Range, возвращаемого методом Range, и другой для объекта Range, возвращаемого методом Resize). С другой стороны, для передачи ячейки данных по ячейкам требуются запросы 300 интерфейсов к объектам Range. По возможности вы можете воспользоваться преимуществами массовой передачи данных и сокращения количества запросов интерфейса.
Использование автоматизации для передачи набора записей ADO в диапазон листа
Excel 2000 г. появился метод CopyFromRecordset, который позволяет передавать набор записей ADO (или DAO) в диапазон на листе. В следующем коде показано, как автоматизировать Excel 2000, Excel 2002 или Office Excel 2003 и передать содержимое таблицы Orders в образце базы данных Northwind с помощью метода CopyFromRecordset.
Примечание При использовании Office 2007 базы данных Northwind необходимо заменить следующую строку кода в примере кода:
Замените эту строку кода следующей строкой кода:
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:
Использование буфера обмена
Буфер обмена Windows также можно использовать в качестве механизма передачи данных на лист. Чтобы вставить данные в несколько ячеек на листе, можно скопировать строку, в которой столбцы разделены символами табуляции, а строки разделены символами каретки. В следующем коде показано, Visual Basic использовать объект буфера обмена для передачи данных в Excel:
Создание текстового файла с разделителями, который Excel анализировать по строкам и столбцам
Excel могут открывать файлы с разделителями-табуляции или запятыми и правильно анализировать данные в ячейках. Эту функцию можно использовать, если вы хотите перенести большой объем данных на лист, используя небольшой объем (при наличии) службы автоматизации. Это может быть хорошим подходом для клиент-серверного приложения, так как текстовый файл может быть создан на стороне сервера. Затем можно открыть текстовый файл на клиенте с помощью службы автоматизации, где это необходимо.
В следующем коде показано, как создать текстовый файл с разделителями-запятыми из набора записей ADO:
Обратите внимание, что при использовании Office 2007 базы данных Northwind необходимо заменить следующую строку кода в примере кода:
Замените эту строку кода следующей строкой кода:
Если текстовый файл имеет расширение .CSV, Excel открывает файл без отображения мастера импорта текста и автоматически предполагает, что файл разделен запятыми. Аналогичным образом, если файл имеет расширение .TXT, Excel автоматически анализировать файл с помощью разделителей табуляции.
В предыдущем примере кода Excel с помощью инструкции Оболочки, а имя файла использовался в качестве аргумента командной строки. В предыдущем примере автоматизация не использовалась. Однако при необходимости можно использовать минимальный объем автоматизации, чтобы открыть текстовый файл и сохранить его в Excel формате книги:
Передача данных на лист с помощью ADO
С помощью поставщика Microsoft Jet OLE DB можно добавлять записи в таблицу в существующей Excel книге. Таблица в Excel является просто диапазоном с определенным именем. Первая строка диапазона должна содержать заголовки (или имена полей), а все последующие строки — записи. Ниже показано, как создать книгу с пустой таблицей с именем MyTable.
Excel 97, Excel 2000 и Excel 2003
Запустите новую книгу в Excel.
Добавьте следующие заголовки в ячейки A1:B1 листа Sheet1:
A1: FirstName B1: LastName
Отформатировать ячейку B1 как выровненную по правому краю.
В меню "Вставка" выберите "Имена", а затем выберите "Определить". Введите имя MyTable и нажмите кнопку "ОК".
Сохраните новую книгу как C:\Book1.xls и закройте Excel.
Чтобы добавить записи в MyTable с помощью ADO, можно использовать следующий код:
Excel 2007
В Excel 2007 запустите новую книгу.
Добавьте следующие заголовки в ячейки A1:B1 листа Sheet1:
A1: FirstName B1: LastName
Отформатировать ячейку B1 как выровненную по правому краю.
На ленте откройте вкладку "Формулы " и выберите команду "Определить имя". Введите имя MyTable и нажмите кнопку "ОК".
Сохраните новую книгу как C:\Book1.xlsx и закройте Excel.
Чтобы добавить записи в таблицу MyTable с помощью ADO, используйте код, похожий на следующий пример кода.
При добавлении записей в таблицу таким образом форматирование в книге сохраняется. В предыдущем примере новые поля, добавленные в столбец B, форматируются с выравниванием по правому краю. Каждая запись, добавляемая в строку, заимствует формат из строки над ней.
Обратите внимание, что при добавлении записи в ячейку или ячейки листа она перезаписывает все данные, которые ранее были в этих ячейках. Другими словами, строки на листе не "помещаются вниз" при добавлении новых записей. Это следует учитывать при проектировании макета данных на листах.
Метод обновления данных на листе Excel с помощью ADO или DAO не работает в среде Visual Basic for Application в Access после установки Office 2003 с пакетом обновления 2 (SP2) или после установки обновления для Access 2002, включенного в статью базы знаний Майкрософт 904018. Этот метод хорошо работает в Visual Basic приложений из других Office приложений, таких как Word, Excel и Outlook.
Дополнительные сведения см. в следующей статье:
Дополнительные сведения об использовании 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 форме:
При использовании LinkPoke с Excel указывается диапазон в нотации столбца строк (R1C1) для LinkItem. Если данные разделяются на несколько ячеек, можно использовать строку, в которой столбцы разделяются вкладками, а строки разделяются символами возврата каретки.
При использовании LinkExecute для Excel выполнения команды необходимо Excel в синтаксисе языка макросов Excel (XLM). Документация по XLM не входит в Excel 97 и более поздних версий.
DDE не рекомендуется для взаимодействия с Excel. Автоматизация обеспечивает максимальную гибкость и предоставляет больше доступа к новым функциям, Excel может предложить.
Способы передачи данных из Visual Basic в Excel
Применимо к: Microsoft Office Excel 2007Microsoft Office Excel 2003Excel 2010
В данной статье рассматриваются способы передачи данных в 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). Для этого найдите следующую строку кода в приведенных ниже примерах:
Замените этот код следующей строкой кода:
Кроме того, база данных «Борей» не входит в состав Office 2007 по умолчанию. Вы можете скачать базу данных «Борей» из 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"
'Save the Workbook and Quit Excel
Таким образом, передача данных по одной ячейке допустима только для небольших объемов данных. Для передачи больших объемов данных в Excel следует использовать один из способов, описанных ниже.
Примеры сценариев для автоматизации Excel см. в следующей статье базы знаний Майкрософт:
219151 Как использовать Visual Basic для автоматизации Microsoft Excel
Использование автоматизации для передачи массива данных на диапазон на листе
Массив данных может передаваться одновременно на диапазон из нескольких ячеек:
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
'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
Передача большого объема данных с помощью массива происходит значительно быстрее, чем передача данных по одной ячейке. Обратите внимание на строку из приведенного выше сценария, которая одновременно передает данные в 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.
'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
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
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
'Save the Workbook and Quit Excel
'Close the connection
Примечание. При использовании версии базы данных «Борей» для 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 с данными из образца базы данных «Борей».
'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
"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")
'Save the Workbook and Quit Excel
Использование буфера обмена
Буфер обмена Windows также может использоваться как механизм передачи данных на лист Excel. Чтобы вставить данные в несколько ячеек листа, можно скопировать строку, в которой столбцы разделены знаками табуляции, а строки – символами возврата каретки. В приведенном ниже сценарии показано, как 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"
'Create a new workbook in Excel
Dim oExcel As Object
Dim oBook As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
'Save the Workbook and Quit Excel
Создание текстового файла с разделителями, который Excel может разобрать по строкам и столбцам
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
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Set rs = conn.Execute("Orders", , adCmdTable)
'Save the recordset as a tab-delimited file
sData = rs.GetString(adClipString, , vbTab, vbCr, vbNullString)
'Close the connection
'Open the new text file in 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=" & _
Замените эту строку кода следующей строкой:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
Если файл имеет расширение 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
Передача данных на лист Excel с помощью ADO
С помощью Microsoft Jet OLE DB Provider можно добавлять записи в таблицу существующей книги Excel. «Таблицей» в Excel считается диапазон с заданным именем. Первая строка диапазона содержит заголовки (или имена полей), а все последующие строки – записи. Ниже приведен пример пошагового создания книги с пустой таблицей
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')"
В Excel 2007 создайте книгу.
Добавьте следующие заголовки в ячейки A1:B1 листа 1:
A1: FirstName B1: LastName
Выровняйте ячейку 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')"
При подобном добавлении записей в таблицу форматирование книги сохраняется. В приведенном выше примере новые поля, добавляемые в столбец B, выравниваются по правому краю. Каждая запись, добавляемая в строку, форматируется так же, как предыдущая.
Обратите внимание на то, что при добавлении в ячейку или ячейки листа запись заменяет любые данные, находившиеся в этих ячейках ранее; другими словами, строки листа не сдвигаются вниз при добавлении новых записей. Это следует иметь в виду при планировании размещения данных на листе.
Примечание. Обновление данных на листе Excel с помощью ADO или DAO невозможно в среде Visual Basic for Application в Access после установки пакета обновлений 2 (SP2) для Office 2003 или обновления для Access 2002, описанного в статье 904018 базы знаний Майкрософт. Однако этот способ можно использовать в среде Visual Basic for Application в других приложениях Office, то есть в Word, Excel и Outlook.
Для получения дополнительной информации щелкните приведенные ниже номера статей базы знаний Майкрософт:
904953 Невозможно вносить изменения, добавлять или удалять данные в таблицах, источником которых являются книги Excel в Office Access 2003 или в Access 2002
904018 Описание обновления для Access 2002: от 18 октября 2005 г.
Дополнительные сведения об использовании ADO для доступа к книгам Excel см. в следующих статьях базы знаний Майкрософт:
195951 Как создать запросы и выполнить обновление данных 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
'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"
'Execute commands to select cell A1 (same as R1C1) and change the font
Text1.LinkExecute "[FONT.PROPERTIES(""Times New Roman"",""Bold"",10)]"
'Terminate the DDE communication
При использовании метода LinkPoke с Excel необходимо указать диапазон в формате строка-столбец (R1C1) для LinkItem. Если данные вставляются в несколько ячеек, можно использовать строку, в которой столбцы разделены символами табуляции, а строки – символами возврата каретки.
Если метод LinkExecute используется для выполнения команды в Excel, синтаксис команды должен соответствовать языку Excel Macro Language (XLM). Документация по XLM не входит в состав Excel 97 и более поздних версий.
DDE не является рекомендуемым способом связи с Excel. Программирование объектов предоставляет больше возможностей и обеспечивает лучший доступ к новым функциям Excel.
Для получения дополнительной информации щелкните приведенный ниже номер статьи базы знаний Майкрософт:
Читайте также: