Методы передачи данных в Excel из Visual Basic
Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.
Сводка
В этой статье обсуждаются многочисленные методы передачи данных в 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 имеют ограничение в 64K для запросов интерфейса. Если вы достигнете или превысите это ограничение в 64k для запросов интерфейса, сервер автоматизации (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 г. и передать содержимое таблицы заказов в базе данных образцов Northwind с помощью метода CopyFromRecordset.
Примечание Если вы используете Office 2007 года в базе данных Northwind, необходимо заменить следующую строку кода в примере кода:
Замените эту строку кода следующей строкой кода:
Excel 97 также предоставляет метод CopyFromRecordset, но использовать его можно только с набором записей DAO. CopyFromRecordset с Excel 97 не поддерживает ADO.
Дополнительные сведения об использовании ADO и метода CopyFromRecordset см. в статьи How to transfer data from an ADO recordset to 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 использовать объект Clipboard для передачи данных в Excel:
Создание разнонародных текстовых файлов, Excel можно разрезать на строки и столбцы
Excel могут открывать файлы с запятой или запятой и правильно разбора данных в ячейки. Вы можете воспользоваться этой функцией, если вы хотите передать большой объем данных на таблицу при использовании малого, если таково, автоматизации. Это может быть хорошим подходом для клиентского приложения-сервера, так как текстовый файл может быть создан на стороне сервера. Затем можно открыть текстовый файл на клиенте, используя автоматизацию там, где это необходимо.
В следующем коде показано, как создать текстовый файл с запятой из наборов записей ADO:
Обратите внимание, что Office версии базы данных Northwind 2007 года необходимо заменить следующую строку кода в примере кода:
Замените эту строку кода следующей строкой кода:
Если в текстовом файле есть расширение .CSV, Excel открывает файл без отображения мастера импорта текста и автоматически предполагает, что файл запятой. Аналогично, если в вашем файле .TXT расширение, Excel автоматически разборите файл с помощью делимитеров вкладок.
В предыдущем примере кода Excel был запущен с помощью заявления Shell, а имя файла использовалось в качестве аргумента командной строки. Автоматизация не использовалась в предыдущем примере. Однако при желании можно использовать минимальное количество автоматизации для открытия текстового файла и сохранения его в формате Excel книги:
Передача данных на таблицу с помощью ADO
С помощью поставщика DB Microsoft Jet OLE можно добавить записи в таблицу в существующей Excel книге. "Таблица" в Excel — это просто диапазон с определенным именем. Первая строка диапазона должна содержать заглавные (или имена полей), а все последующие строки содержат записи. Ниже показано, как создать книгу с пустой таблицей MyTable.
Excel 97, Excel 2000 и Excel 2003 г.
Запустите новую книгу в Excel.
Добавьте следующие заглавные таблицы в ячейки A1:B1 листа1:
A1: FirstName B1: LastName
Формат ячейки B1 в виде правой выровненной.
В меню Insert выберите Имена, а затем выберите Определение. Введите имя MyTable и нажмите кнопку ОК.
Сохраните новую книгу как C:\Book1.xls и Excel.
Чтобы добавить записи в MyTable с помощью ADO, можно использовать код, аналогичный следующему:
Excel 2007
В Excel 2007 г. запустите новую книгу.
Добавьте следующие заглавные таблицы в ячейки A1:B1 листа1:
A1: FirstName B1: LastName
Формат ячейки B1 в виде правой выровненной.
На ленте нажмите вкладку Формулы и нажмите кнопку Определить имя. Введите имя MyTable и нажмите кнопку ОК.
Сохраните новую книгу как C:\Book1.xlsx, а затем Excel.
Чтобы добавить записи в таблицу MyTable с помощью ADO, используйте код, похожий на следующий пример кода.
При добавлении записей в таблицу таким образом форматирование в книге сохраняется. В предыдущем примере новые поля, добавленные в столбец B, форматированы с правильным выравниванием. Каждая запись, добавляемая в строку, заимствует формат из строки над ней.
Следует отметить, что при добавлении записи в ячейку или ячейки в таблицу она переописывание любых данных, ранее в этих ячейках; Другими словами, строки в таблице не "сдвинуты" при добавлении новых записей. Это следует иметь в виду при разработке макета данных на ваших таблицах.
Метод обновления данных в Excel таблице с помощью ADO или с помощью DAO не работает в Visual Basic для среды приложения в access после установки Office 2003 Пакет обновления 2 (SP2) или после установки обновления для Access 2002, включенного в статью 904018 Базы знаний Майкрософт. Метод хорошо работает в Visual Basic среде приложений из других Office приложений, таких как Word, Excel и Outlook.
Дополнительные сведения см. в следующей статье:
Дополнительные сведения об использовании ADO для доступа к книге Excel см. в книге How To Query and Update Excel Data Using ADO from 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 представляет собой управление текстовым полем на Visual Basic форме:
При использовании LinkPoke с Excel, вы указываете диапазон в строке-столбце (R1C1) для LinkItem. Если вы подтыкаете данные к нескольким ячейкам, можно использовать строку, в которой столбцы делимитированы вкладками, а строки делимитированы возвращаемой каретой.
При использовании LinkExecute для Excel выполнения команды необходимо Excel команду в синтаксисе Excel макроса (XLM). Документация XLM не включается в Excel версии 97 и более поздней версии.
DDE не рекомендуется для общения с Excel. Автоматизация обеспечивает наибольшую гибкость и предоставляет дополнительный доступ к новым функциям, которые Excel предложить.
Developing.ru
данные из одного файла в другой (VB Excel)
предположим, у меня есть 2 файла 1.xls и 2.xls, и мне надо из первого с листа "Лист1" ячейки (1,1) перенести значение во второй туда же
как это прописать в макросе второго файла?
как в "Worksheets(Лист1).Cells(1, 1) = Worksheets(Лист1).Cells(1, 1)" добавить и сами файлы?
просто ссылкой не катит, тк теоретически надо переносить сотни (если не тысячи) данных, с поисками и тп.
Здравствуйте russian imigration.
"Предположим" я догадался, что Ваш пост является вопросом, но совсем не понятно к кому он обращен, и кому Вы "заранее" благодарны.
Евгений.
P.S. да, и по существу вопроса
http://woodash.ifolder.ru/3765138
DataFromTable.rar 15.23 кб
что-то никак не получается, а файл, что выложил Тесленко не открывается
значит так:
у меня макрос ВБ в одном экселевском документе, ему надо прочитать ячейки из другого экселевского документа
создал переменную FileToOpen с полным путем на второй файл (для чтения). проверил, переменная равна
F:\calc 0.3\шаблон РТМ русифицированный.xls
Добрый день, Евгений Teslenko_EA
Спасибо за ответ.
1. теперь все открывается. раньше писало, что файл по данному адресу не найден, то есть не удавалось скачать его.
2. нет, сам файл оставался закрытым. обязательно ли надо его открывать, чтобы прочитать из него данные? от постоянно требует обновлений данных, что не очень удобно (но, в принципе, с этим можно смириться)
Насчет переменной str я понял еще вчера во время попыток отладки. Что-то задумался и создал такую, а потом, увидев у себя код типа "str = str(ячейка в экселе)", сам удивился, как такое написал
ЗЫ. Еще раз спасибо, что подсказываете, в том числе и как общаться. По ходу у меня с этим проблемы Не обращаясь ни к кому конкретно, я подразумевал, что ответит тот, кто будет знать ответ.
mc-black, спасибо за ответ, но, в принципе, Ваш код совпал с кодом Teslenko_EA