Собрать данные из нескольких файлов excel

Собрать данные из нескольких файлов excel

Этот макрос предназначен для сбора (загрузки) информации из файлов Excel, расположенных в одной папке.

Для работы этого макроса, помимо него самого, вам понадобится добавить в свой файл:

  1. функцию FilenamesCollection для получения списка файлов в папке
  2. функцию GetFolder для вывода диалогового окна выбора папки с запоминанием выбранной папки
  3. прогресс-бар для отображения процесса обработки файлов (модуль класса и форму)

Если при тестировании макроса у вас возникает ошибка, что не найдена та или иная функция,
— проверьте, все ли необходимые компоненты (которые перечислены выше) вы добавили в свой файл.

Этот макрос я публикую прежде всего для себя (поскольку использую этот код чуть ли ни в каждой третьей своей программе),
поэтому я не буду помогать вам в настройке этого макроса, если у вас он вдруг не заработает.

Макрос при запуске выдает диалоговое окно для выбора папки, в которой расположены обрабатываемые файлы,
после чего открывает каждый из файлов, считывает из него данные, помещает их в текущую книгу (из которой запущен макрос),
и закрывает обработанный файл без сохранения изменений.

После того, как очередной файл обработан, он перемещается во вторую папку («архив»).

Во вложении — файл со всеми необходимыми макросами для сбора данных из других файлов Excel

Вложение Размер Загрузки Последняя загрузка
MergeWorkbooks.xls 117 КБ 307 1 год 44 недели назад
  • 137095 просмотров

Комментарии

Как в коде сделать чтоб брал данные с 2 ячеек, а выводил в одну через пробел

Добрый день!
Отличный макрос. Единственный вопрос, не подскажите, каким образом можно дополнить этот макрос, чтобы еще выбирались данные из другой строки/диапазона:
Set ra = sh.Range(sh.Range("b1"), sh.Range("b" & sh.Rows.Count).End(xlUp))

Всё можно
Оформляйте заказ на сайте
https://excelvba.ru/order/send
обязательно прикрепляйте примеры файлов,
и подробно описывайте, что и как должно работать.

Можно ли реализовать возможность обработки word документов из этого же файла?

Я не консультирую по вопросам переделки этого макроса.
Можем сделать макрос под ваши требования под заказ (платно)

Здравствуйте. Подскажите как сделать, чтобы данные из каждого файла записывались в новый столбец (т.е. со сдвигом вправо)?

В файле с макросом то миллион строк, но вот дальше 65000 макрос не вставлял.В общем, проблема была в исходных файлах. Когда они были сохранены как .xlsx, макрос отработал как надо.
П.с. Вообще отличный макрос как заготовка для разных действий с кучей файлов. Спасибо.

Закройте файл после преобразования, и откройте снова
Убедитесь, что в файле миллион строк
Потом только запускайте макрос

У меня 2007 офис. Сохраняю через круглую кнопку сверху, сохранить как, выбираю формат. Ничего не меняется. Макрос перестает работать в районе 65000 строки.
Может такое быть, что это из-за кода?

Файл с макросом надо пересохранить в новом формате.
Меню Excel: Файл — Преобразовать
И на листе будет миллион строк
Потом запускайте сбор данных

в форматах .xlsb и .xlsm всё равно утыкается в 65к строк. Можно что-то ещё сделать?

Алексей, по бесплатным макросам техподдержки нет.
Могу переписать макрос под ваши нужды (платно)

И забыл добавить в диапазоне колонок B, C, D, E, F начиная только со второй строки, т.е. B2 например

Добрый день. Подскажите пожалуйста, а как сделать перебор всех листов файла начиная со второго, первый лист надо исключать.

Игорь большое спасибо

Добрый день!
Пожалуйста подскажите, как сделать, что бы при обрашении запрашивался не первый лист-(Set sh = WB.Worksheets(1)) а определенный с названием Например — Main

Else
Set sh = WB.Worksheets(1)
Set ra = sh.Range(sh.Range("a2"), sh.Range("a" & sh.Rows.Count).End(xlUp)).Resize(, 10)

shd.Range("a" & shd.Rows.Count).End(xlUp).Offset(1).Resize(ra.Rows.Count,ra.Columns.Count).Value = ra.Value
WB.Close False: DoEvents

Спасибо за ответ.

Подскажите, пожалуйста, а как сделать заполнение книги с определенной строки. Сейчас все данные записываются со строки А2, а нужно к примеру с А3?

Нет, не получится.
Это диалоговое окно выбора папки (встроенное в Office) — в таких окнах видны только папки и подпапки.
Не видите, какую книгу выбираете, — потому что выбираете вы не книгу, а папку с файлами.

Игорь, можно еще вопрос — при загрузке (выборе) файлов, я невижу какую книгу выбираю, просто пустая папка, можно ли в коде добавить видимость, расширение книг

Читайте также:  Amd athlon 64 x2 3800 разгон

Аня, код же открыт, — изменяйте его под свои нужды сколько угодно
Если сами не справитесь, — я могу сделать, под заказ (платно)

Попробуйте в этой строке кода

Но не уверен, что этого достаточно будет (зависит от того, какая формула там, — будет ли она работать при переносе в другой файл без изменений)

Здраствуйте, пользуюсь вашим макросом, у меня такая проблема: при загрузки данных не копируются формулы, можно ли изменить код?

Добрый день! Подскажите пожалуйста, с чего мне лучше начать, мне дали задание сделать отчет по менеджерам, тоесть у каждого менеджера и отделения есть план сколько они должены выдать кредиты по плану по факту и итог по всему отделу, но такая проблема данного менеджера могут перевисти в другое отделение, и по всем excel-м файлом нужно найти данного менеджера в конце месяца посчитать сколько он выдал кредитов.

Сергей, могу написать макрос под заказ.
Оформляйте заказ на сайте, прикрепляйте примеры файлов (несколько исходных, и образей итогового), и подробно описывайте, что и куда должно копироваться.

Добрый вечер.
У мня такая проблема. Каждый месяц получаю заказы от магазинов.
160 строчек позиций и 450 столбцов-магазинов. все это отдельными файлами. Приходится вручную по одному копи-пастить столбцов из 450 файлов в одну таблицу. Все это в EXEL.
Попробовал применить Ваш макрос — не срабатывает. То выдает сообщение о том, что книги защищены, то просто не срабатывает.
Посоветуйте, пожалуйста, как мне решить задачу.

Ваш макрос спас меня перед аудитом, спасибо))

Здравствуйте, уважаемые пользователи Тостера.

Передо мной стала задача — нужно автоматизировать рутинную работу. Есть куча Excel файлов и нужно собрать их в один. Делать это придется неоднократно, поэтому стал вопрос в написании программы.

Вопрос заключается в следующем: объективно ли будет писать данную программу на Python? Если нет, то как можно решить данную проблему проще?

Заранее благодарен всем, кто попытается помочь.

  • Вопрос задан более трёх лет назад
  • 56825 просмотров

Предположим, имеется куча книг Excel, все листы из которых надо объединить в один файл. Копировать руками долго и мучительно, поэтому имеет смысл использовать несложный макрос.

Открываем книгу, куда хотим собрать листы из других файлов, входим в редактор Visual Basic (ALT+F11), добавляем новый пустой модуль (в меню Insert — Module) и копируем туда текст вот такого макроса:

Sub CombineWorkbooks()
Dim FilesToOpen
Dim x As Integer

Application.ScreenUpdating = False ‘отключаем обновление экрана для скорости

‘вызываем диалог выбора файлов для импорта
FilesToOpen = Application.GetOpenFilename _
(FileFilter:="All files (*.*), *.*", _
MultiSelect:=True, Title:="Files to Merge")

If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "Не выбрано ни одного файла!"
Exit Sub
End If

‘проходим по всем выбранным файлам
x = 1
While x более трёх лет назад

Постановка задачи

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

Предположим, что у нас есть вот такая папка, в которой содержится несколько файлов с данными из филиалов-городов:

Количество файлов роли не играет и может меняться в будущем. В каждом файле есть лист с именем Продажи, где расположена таблица с данными:

Количество строк (заказов) в таблицах, само-собой, разное, но набор столбцов везде стандартный.

Задача: собрать данные из всех файлов в одну книгу с последующим автоматическим обновлением при добавлении-удалении файлов-городов или строк в таблицах. По итоговой консолидированной таблице затем можно будет строить любые отчеты, сводные таблицы, фильтровать-сортировать данные и т.д. Главное — суметь собрать.

Подбираем оружие

Для решения нам потребуется последняя версия Excel 2016 (в нее нужный функционал уже встроен по умолчанию) или предыдущие версии Excel 2010-2013 с установленной бесплатной надстройкой Power Query от Microsoft (скачать ее можно здесь). Power Query — это супергибкий и супермощный инструмент для загрузки в Excel данных из внешнего мира с последующей их зачисткой и обработкой. Power Query поддерживает практически все существующие источники данных — от текстовых файлов до SQL и даже Facebook 🙂

Читайте также:  Как узнать где телефон по аккаунту гугл

Если у вас нет Excel 2013 или 2016, то дальше можно не читать (шучу). В более древних версиях Excel подобную задачу можно реализовать только программированием макроса на Visual Basic (что весьма непросто для начинающих) или монотонным ручным копированием (что долго и порождает ошибки).

Шаг 1. Импортируем один файл как образец

Для начала давайте импортируем данные из одной книги в качестве примера, чтобы Excel "подхватил идею". Для этого создайте новую пустую книгу и.

  • если у вас Excel 2016, то откройте вкладку Данные и выберите Создать запрос — Из файла — Из книги (Data — New Query- From file — From Excel)
  • если у вас Excel 2010-2013 с установленной надстройкой Power Query, то откройте вкладку Power Query и выберите на ней Из файла — Из книги (From file — From Excel)

Затем в открывшемся окне переходим в нашу папку с отчетами и выбираем любой из файлов-городов (не играет роли какой именно, т.к. они все типовые). Через пару секунд должно появиться окно Навигатор, где нужно в левой части выбрать требуемый нам лист (Продажи), а в правой отобразится его содержимое:

Если нажать в правом нижнем углу этого окна кнопку Загрузить (Load) , то таблица будет сразу импортирована на лист в исходном виде. Для одиночного файла — это хорошо, но нам нужно загрузить много таких файлов, поэтому мы пойдем немного другим путем и жмем кнопку Правка (Edit) . После этого должен в отдельном окне отобразиться редактор запросов Power Query с нашими данными из книги:

Это очень мощный инструмент, позволяющий "допилить" таблицу под нужный нам вид. Даже поверхностное описание всех его функций заняло бы под сотню страниц, но, если совсем кратко, то с помощью этого окна можно:

  • отфильтровывать ненужные данные, пустые строки, строки с ошибками
  • сортировать данные по одному или нескольким столбцам
  • избавляться от повторов
  • делить слипшийся текст по столбцам (по разделителям, количеству символов и т.д.)
  • приводить текст в порядок (удалять лишние пробелы, исправлять регистр и т.д.)
  • всячески преобразовывать типы данных (превращать числа как текст в нормальные числа и наоборот)
  • транспонировать (поворачивать) таблицы и разворачивать двумерные кросс-таблицы в плоские
  • добавлять к таблице дополнительные столбцы и использовать в них формулы и функции на встроенном в Power Query языке М.
  • .

Для примера, давайте добавим к нашей таблице столбец с текстовым названием месяца, чтобы потом проще было строить отчеты сводных таблиц. Для этого щелкните правой кнопкой мыши по заголовку столбца Дата и выберите команду Дублировать столбец (Duplicate Column) , а затем щелкните правой кнопкой мыши по заголовку появившегося столбца-дубликата и выберите команды Преобразование — Месяц — Название месяца:

Должен образоваться новый столбец с текстовыми названиями месяца для каждой строки. Дважды щелкнув по заголовку столбца, его можно переименовать из Копия Дата в более удобное Месяц , например.


Если в каких-то столбцах программа не совсем корректно распознала тип данных, то ей можно помочь, щелкнув по значку формата в левой части каждого столбца:

Исключить строки с ошибками или пустые строки, а также ненужных менеджеров или заказчиков можно с помощью простого фильтра:

Причем все выполненные преобразования фиксируются в правой панели, где их всегда можно откатить (крестик) или изменить их параметры (шестеренка):

Легко и изящно, не правда ли?

Шаг 2. Преобразуем наш запрос в функцию

Чтобы впоследствии повторить все сделанные преобразования данных для каждой импортируемой книги, нужно преобразовать наш созданный запрос в функцию, которая затем будет применяться, по очереди, ко всем нашим файлам. Сделать это, на самом деле, очень просто.

В редакторе запросов перейдите на вкладку Просмотр и нажмите кнопку Расширенный редактор (View — Advanced Editor) . Должно открыться окно, где все наши предыдущие действия будут записаны в виде кода на языке М. Обратите внимание, что в коде жестко прописан путь к файлу, который мы импортировали для примера:

Теперь аккуратно вносим пару правок:

Читайте также:  Winsnap что это за программа

Смысл их прост: первая строка (filepath)=> превращает нашу процедуру в функцию с аргументом filepath , а ниже мы меняем фиксированный путь на значение этой переменной.

Все. Жмем на Готово и должны увидеть вот это:

Не пугайтесь, что пропали данные — на самом деле все ОК, все так и должно выглядеть 🙂 Мы успешно создали нашу пользовательскую функцию, где запомнился весь алгоритм импорта и обработки данных без привязки к конкретному файлу. Осталось дать ей более понятное имя (например getData ) на панели справа в поле Имя и можно жать Главная — Закрыть и загрузить (Home — Close and Load) . Обратите внимание, что в коде жестко прописан путь к файлу, который мы импортировали для примера.. Вы вернетесь в основное окно Microsoft Excel, но справа должна появиться панель с созданным подключением к нашей функции:

Шаг 3. Собираем все файлы

Все самое сложное — позади, осталась приятная и легкая часть. Идем на вкладку Данные — Создать запрос — Из файла — Из папки (Data — New Query — From file — From folder) или, если у вас Excel 2010-2013, аналогично на вкладку Power Query. В появившемся окне указываем папку, где лежат все наши исходные файлы-города и жмем ОК. Следующим шагом должно открыться окно, где будут перечислены все найденные в этой папке (и ее подпапках) файлы Excel и детализация по каждому из них:

Жмем Изменить (Edit) и опять попадаем в знакомое окно редактора запросов.

Теперь нужно добавить к нашей таблице еще один столбец с нашей созданной функцией, которая "вытянет" данные из каждого файла. Для этого идем на вкладку Добавить столбец — Пользовательский столбец (Add Column — Add Custom Column) и в появившемся окне вводим нашу функцию getData , указав для ее в качестве аргумента полный путь к каждому файлу:

После нажатия на ОК созданный столбец должен добавиться к нашей таблице справа.

Теперь удалим все ненужные столбцы (как в Excel, с помощью правой кнопки мыши — Удалить), оставив только добавленный столбец и столбец с именем файла, т.к. это имя (а точнее — город) будет полезно иметь в итоговых данных для каждой строки.

А теперь "вау-момент" — щелкнем мышью по значку со своенным стрелками в правом верхнем углу добавленного столбца с нашей функцией:

. снимаем флажок Использовать исходное имя столбца как префикс (Use original column name as prefix) и жмем ОК. И наша функция подгрузит и обработает данные из каждого файла, следуя записанному алгоритму и собрав все в общую таблицу:

Для полной красоты можно еще убрать расширения .xlsx из первого столбца с именами файлов — стандартной заменой на "ничего" (правой кнопкой мыши по заголовку столбца — Заменить) и переименовать этот столбец в Город. А также подправить формат данных в столбце с датой.

Все! Жмем на Главной — Закрыть и загрузить (Home — Close & Load) . Все собранные запросом данные по всем городам будут выгружены на текущий лист Excel в формате "умной таблицы":

Созданное подключение и нашу функцию сборки не нужно никак отдельно сохранять — они сохраняются вместе с текущим файлом обычным образом.

В будущем, при любых изменениях в папке (добавлении-удалении городов) или в файлах (изменение количества строк) достаточно будет щелкнуть правой кнопкой мыши прямо по таблице или по запросу в правой панели и выбрать команду Обновить (Refresh) — Power Query "пересоберет" все данные заново за несколько секунд.

Поправка. После январских обновлений 2017 года Power Query научился собирать Excel’евские книги сам, т.е. не нужно больше делать отдельную функцию — это происходит автоматически. Таким образом второй шаг из этой статьи уже не нужен и весь процесс становится заметно проще:

  1. Выбрать Создать запрос — Из файла — Из папки — Выбрать папку — ОК
  2. После появления списка файлов нажать Изменить
  3. В окне редактора запросов развернуть двойной стрелкой столбец Binary и выбрать имя листа, который нужно взять из каждого файла
Ссылка на основную публикацию
Сколько человек сидит в одноклассниках
Mail.Ru Group исследовала и сравнила аудитории самых популярных в России социальных сетей — «Одноклассники», «Мой Мир», «ВКонтакте», Facebook и Twitter....
Сигнал flash в телефоне panasonic
● 19.12.08 13:08 - krepsky - 9 / 19.12.08 Два дня ломаю голову… Такая ситуация - купили партию телефонов Panasonic...
Сигналы материнской платы при загрузке
BIOS (Basic Input/Output System – базовая система ввода-вывода). Программа системного уровня, предназначенная для первоначального запуска компьютера, настройки оборудования и обеспечения...
Сколько четырехзначных чисел можно составить из нечетных
Условие Решение 1 Решение 2 Решение 3 Поиск в решебнике Популярные решебники Издатель: Н. Я. Виленкин, В. И. Жохов, А....
Adblock detector