Vba выгрузка данных из excel

Обновлено: 07.07.2024

Добрый вечер форумчане!
Нужна Ваша помощь: имеются 2 файла - основной и файл-выгрузка из системы. В основном файле есть лист, в который из файла-выгрузки нужно копировать определенные столбцы. Суть вопроса следующая - файл-выгрузка содержит достаточно много столбцов из которых для обновления данных в основном файле нужно всего лишь 4 столбца. И если, например эту выгрузку сделать сегодня, порядок столбцов может быть один, а если сделать, например ее завтра - порядок столбцов может быть совершенно другим. При этом названия остаются без изменений, меняется только их (столбцов) порядок. Можно для избавления от рутины как-то оптимизировать этот процесс через макрос, т.е. копировать данные из второго файла опираясь только на названия столбцов, а не на их порядок в книге? И желательно, чтобы при копировании из 2го файла, этот файл не открывался?
Сейчас копирование информации из файла в файл через vba работает, но работает при условии, что порядок столбцов всегда одинаковый и 2й файл при копировании открывается.
Заранее спасибо

Работа с внешними источниками данных Материалы по работе с внешними источниками данных на примере Excel и SQL. Рассмотрим способы передачи данных между Excel и внешней базой данной на SQL сервере с помощью ADO.

Задача первая. Подключаемся к внешней базе данных.

Для начала надо подключиться к внешней базе данных. Подключение возможно если на компьютере установлен драйвер. Список установленных драйверов для подключения к базам данных на компьютере под управлением Windows: Проверить подключение к базе данных можно простым способом. Создаем пустой файл (например, "текстовый документ.txt"), затем изменяем имя и расширение на .udl (например, "connect.udl"). Двойной клик мышкой по новому файлу, далее приступаете к настройке и проверке подключения к базе данных. После того, как удалось настроить корректное подключение к базе данных, сохраняем файл "connect.udl". Открываем файл "connect.udl" обычным текстовым редактором (например, блокнотом), и видим в строке подключения все необходимые параметры. Про подключение к внешним базам данных можно посмотреть на ресурсе ConnectionStrings . Теперь возвращаемся к нашему VBA для Excel. В редакторе VBA подключаем последнюю версию библиотеки: Пример кода:

Задача вторая. Загружаем данные из внешней базы данных на SQL сервере в Excel.

После того, как мы установили подключение к внешней базе данных можно приступать к чтению данных и выводу в Excel. Здесь потребуется знание языка запросов SQL. В результате выполнения SQL запроса к нам возвращается некая таблица с данными в объект RecordSet. Далее из объекта RecordSet можно выгружать данные непосредственно на лист или в сводную таблицу. Пример кода простой процедуры: Для удобства работы. Предлагаю создать собственный класс "tSQL" для работы с базой данных. У класса будет одно свойство: Для чтения данных напишем метод SelectFrom с параметрами TableName и ws. TableName - это имя таблицы, откуда будем считывать данные и ws - лист Excel, куда будем записывать данные. Пример использования класса tSQL в процедуре

Задача третья. Загружаем данные из Excel во внешнюю базу данных.

Для записи данных напишем метод InsertInto с параметрами TableName. rHead и rData. TableName - это имя таблицы, куда будем добавлять данные; rHead - диапазон ячеек, с указанием полей; rData - диапазон ячеек с данными, которые будем добавлять. Пример использования класса tSQL в процедуре

Задача четвертая. Управляем внешней базой данных из Excel

Рекомендую использовать запросы в основном для чтения данных из внешней БД. Можно записывать данные в таблицы внешней БД. Но крайне не желательно использовать Excel для управления внешней базой данных, лучше использовать стандартные средства разработки.

Решил написать статью, о том, как сделать выгрузку данных в Excel файл по шаблону и считывать данные из Excel.
Началось всё с того, что на работе, дали указание, уйти от MS Office, на бесплатные аналоги.
У нас уже была система выгрузки, основанная на библиотеке “Microsoft.Office. Interop.Excel” и много готовых шаблонов, для выгрузки тех или иных отчётов.
Поэтому надо было найти бесплатную библиотеку, работающую с офисом. И сделать так, чтоб выгрузка работала по той же системе, что и раньше. Ибо переделывать все шаблоны и структуру, не хотелось.
Вот я и наткнулся на OpenXML. И думал, сейчас по быстрому найду решение в интернете и всё готово (т.к. на это было выделено мало времени). Но подходящего решения так и не нашёл, поэтому и решил написать эту статью, для тех у кого будет, такая же проблема.
Саму библиотеку, можно скачать бесплатно с сайта Micrisoft (я использовал в проекте OpenXML sdk 2.5 “ OpenXMLSDKV25.msi ”)
здесь.
После скачивания “OpenXMLSDKV25.msi ”, устанавливаем и заходим в папку
“C:\Program Files\Open XML SDK\V2.5\lib” там лежит библиотека, которая нам понадобится, мы её подключим к проекту (ниже будет описано, как).
Проект был написан на Visual Studio 2010 (Framework 4.0).
Ниже пример шаблона (сделан для теста) “C:\Templates\template.xlsx”.

И пример выгруженных данных (как это будет выглядеть в итоге, после выгрузки).

Ключевые слова:
DataField: — Означает, что на этом месте будут выведены наши банные из DataTable.
DataField:[название выводимого поля]
Label: — Означает, что на этом месте будут выводиться данные, которые надо вставить однократно из словаря
Label:[название ключа в словаре]
А это файл из которого мы будем считывать данные “C:\Loading\ReadMePlease.xlsx”.

Теперь создадим в VS2010, Решение в котором будет 4 проекта:
1) OpenXmlPrj – это консольный проект, для запуска теста.
2) Interfaces – это проект типа “Библиотека классов”, будет хранить наши интерфейсы данных для выгрузки.
3) Framework — это проект типа “Библиотека классов”, тут и будет происходить вся работа с Excel-ем.
4) Converter — это проект типа “Библиотека классов”, для конвертирования наших данных в DataTable (т.к. работа происходит с DataTable).

Теперь в проекте “Framework” создаём две папки и подключим ссылку на библиотеку OpenXML и WindowsBase:
“Create” – для работы с выгрузкой данных.
“Load” – для работы с загрузкой данных.
“lib” – в папку, добавим библиотеку OpenXML.
В папке “Create” создаём 4 класса.
1) Worker – это будет наш главный обработчик.

2) Footer – будет содержать строки и их ячейки идущие после наших данных.

3) CellForFooter – содержит в себе координаты ячейки и её значение, используется в Footer-е.

4) Field – будет содержать индекс строки, где находится DataField, координаты ячеек с DataField и название поля, значение которого надо вывести.

Читайте также: