Копирование нескольких файлов в excel

Сборка листов из разных книг в одну

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

Открываем книгу, куда хотим собрать листы из других файлов, входим в редактор Visual Basic сочетанием клавиш Alt+F11 одноименной кнопкой на вкладке Разработчик (Developer — Visual Basic), добавляем новый пустой модуль (в меню 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 <= UBound(FilesToOpen)
        Set importWB = Workbooks.Open(Filename:=FilesToOpen(x))
        Sheets().Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        importWB.Close savechanges:=False
        x = x + 1
    Wend

    Application.ScreenUpdating = True
End Sub

После этого можно вернуться в Excel и запустить созданный макрос на вкладке Разработчик кнопкой Макросы (Developer — Macros) или нажав Alt+F8. Отобразится диалоговое окно открытия файла, где необходимо указать один или несколько (удерживая Ctrl или Shift) файлов, листы из которых надо добавить к текущей книге — и задача решена!

Ссылки по теме

  • Что такое макросы, куда вставлять код макроса на Visual Basic
  • Автоматическая сборка заданных листов из заданных книг с помощью надстройки PLEX
  • Автоматическая сборка данных с нескольких листов на один итоговый лист с помощью надстройки PLEX
Источник

When you’re working in Microsoft Excel, you may find that your data has become a little hard to follow, with data sets spread across separate sheets, pivot tables, and more. You don’t always need to use multiple worksheets or Excel files to work on your data, however, especially if you’re working as a team.

To help you keep your data organized, you can merge data in Excel. You can merge worksheets from separate files, merge separate Excel files into one, or use the consolidate feature to combine your data instead. 

Here’s how to merge Excel files and data together using these methods.

How To Move Or Copy Single Worksheets In Excel

A typical Microsoft Excel file is broken up into different sheets (or worksheets) which are listed as tabs at the bottom of the Excel window. They act like pages, allowing you to spread data across multiple sheets in a single file.

You can move or copy worksheets between different Excel files (or the same file, if you wish to duplicate your data sets).

  • To start, open your Excel file (or files). In the open window of the Excel file you wish to copy from, click on the worksheet you wish to select at the bottom of the Excel window. You can select multiple sheets by holding Shift and clicking on each sheet tab.
  • To begin copying or moving sheets, press the Home tab in the ribbon bar at the top.
  • From here, click Format > Move or Copy Sheet.
  • In the Move or Copy box, select the Excel file you wish to copy or move to from the To Box drop-down menu. Select where you wish to place the sheets in the Before Sheet section. By default, Excel will move the sheets—click the Create a copy checkbox if you’d prefer to copy them instead. 
  • Press OK to move or copy the worksheets once you’re ready.

The worksheets you selected will then be moved or copied, as desired, although this won’t merge them together entirely.

You can use the Move or Copy Sheet tool in Excel to merge data in multiple Excel files together. You can do this by selecting all of the active worksheets in a file, then merging them into your single target file, repeating this process for multiple files.

  • To do this, open your Excel files. In the open window of an Excel file you wish to move or copy into another file, select all of the sheet tabs at the bottom of the window by holding the Shift key and clicking on each sheet tab.
  • Next, press Home > Format > Move or Copy Sheet from the ribbon bar.
  • In the open Move or Copy dialog box, select the target Excel file to merge to from the To Box drop-down menu. Select where you wish to place your merged sheets in the Before sheet section. If you want to leave the original file intact, press Create a copy to copy the sheets rather than move them, then press OK to begin the moving or copying process.

If you have more than one Excel file, you’ll need to repeat these steps to merge them together into a single file.

Using The Consolidate Tool To Merge Data In Excel Together

Using the methods above, you can move and copy sheets between different Excel spreadsheet files. This moves the data, but it doesn’t integrate it particularly well—the data is still kept in separate sheets.

To get around this problem, you can use the Consolidate tool in Excel to merge numerical data together from multiple worksheets into a new, single worksheet. Unfortunately, this process doesn’t work with cells using text—you’ll need to cut and paste this data manually, or create a VBA script in Excel to do it for you.

For this to work, your data will need to be presented in the same way across your sheets with matching header labels. You’ll also need to delete any blank data (for instance, empty cells) from your data before you begin.

  • To merge data in Excel using this method, open your Excel files and, in the target Excel file for merging data, create a new worksheet by pressing the + (plus) button next to the sheet tabs at the bottom of the window.
  • In your new worksheet, press Data > Consolidate.
  • In the Consolidate window, make sure that Sum is selected in the Function drop-down menu. Click the button next to the Reference entry box to begin selecting your first data set—this is the data you wish to merge. You can also type the reference to the cell range in yourself, if you’d prefer.
  • To select the data using your mouse, click on the sheet containing your worksheet once the Consolidate – Reference box is visible, select the data, then press the Insert button.
  • The cell range will appear in the Reference entry box—click the Add button to add it to the All References list.
  • Repeat the steps above to select additional sets of data, clicking Add to add each set to your sheet. If your data has column or row labels, make sure that these are processed correctly by pressing the Top row or Left column checkboxes in the Use labels section.
  • If you want to continue to edit the data in the original, separate worksheets, click to enable the Create links to source data checkbox. This will ensure that any changes to your original data are reflected in your merged sheet later.
  • Once you’re ready to merge your data into a single sheet, press the OK button.
  • Your merged data will appear, with an options menu next to the row labels to be able to switch between different data views—click on these options (for instance, the 1 button) to hide or view the data you’ve merged in part or in full.

If you choose to create links to the source data, your new worksheet will act as a mirrored version of your separate sheets and files. Otherwise, your data will be copied into this sheet for you to edit directly.

Using Merged Excel Data

Whether you decide to merge data in Excel into a single sheet or a file, or if you prefer to spread your working across multiple files, these tips should help you to keep organized. When you’re ready, you can begin to share your Excel file with others to collaborate and analyze your data effectively as a team.

If you’re worried about losing track of changes to your merged data, don’t worry—you can track changes in Excel easily using its built-in tracking feature. 

Let us know your Excel data analysis tips in the comments section below.

Источник

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Еще…Меньше

Используйте Power Query, чтобы объединить несколько файлов с одной схемой, храняной в одной папке, в одну таблицу. Например, каждый месяц необходимо объединить бюджетные книги из нескольких отделов, где столбцы одинаковы, но количество строк и значений в каждой книге различается. После ее настроив, вы можете применить дополнительные преобразования, как к любому импортируемму источнику данных, а затем обновить данные, чтобы увидеть результаты за каждый месяц.  

Концептуальный обзор объединения файлов папок

Примечание.    В этой теме показано, как объединять файлы из папки. Вы также можете объединять файлы, хранимые в SharePoint, azure BLOB-служба хранилища и Azure Data Lake служба хранилища. Процесс аналогичный.

Не изумять:

  • Убедитесь, что все файлы, которые вы хотите объединить, содержатся в выделенной папке без лишних файлов. В противном случае все файлы в папке и все вложенные папки, которые вы выбрали, будут включены в данные для совместной обработки.

  • У каждого файла должна быть та же схема, что и для согласованных столбцов, типов данных и количества столбцов. Столбцы не должны быть в том же порядке, что и имена столбцов.

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

Каждый из этих файлов имеет простой шаблон, в каждом из которых есть только одна таблица данных.

  1. Выберите Данные > Получить данные > из файла > из папки. Появится диалоговое окно Обзор.

  2. Найдите папку с файлами, которые вы хотите объединить.

  3. Список файлов в папке появится в диалоговом <путь к> папке. Убедитесь, что в списке указаны все нужные файлы.

    Пример диалогового окна импорта текста

  4. Выберите одну из команд в нижней части диалогового окна, например Объединить> Объединить & Загрузить. В разделе Обо всех этих командах обсуждаются дополнительные команды.

  5. Если выбрать команду Объединить, появится диалоговое окно Объединение файлов. Чтобы изменить параметры файла, выберите каждый файл в поле Образец файла, задав нужные параметры Источник файла,Делитер и Обнаружение типов данных. Кроме того, в нижней части диалогового окна можно выбрать или отобрать диалоговое окно Пропускать файлы с ошибками.

  6. Нажмите кнопку ОК.

Результат

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

  1. Выберите Данные > Получить данные > из файла > из папки. Появится диалоговое окно Обзор.

  2. Найдите папку с файлами, которые вы хотите объединить.

  3. Список файлов в папке появится в диалоговом <путь к> папке. Убедитесь, что в списке указаны все нужные файлы.

  4. Выберите одну из команд в нижней части диалогового окна, например Объединить> Объединить & Transform. В разделе Обо всех этих командах обсуждаются дополнительные команды.

    Появится редактор Power Query.

  5. Столбец Значение является структурированным столбцом списка. Выберите значок Развернуть Значок развертывания столбца , а затем выберите Развернуть до новых строк.

    Расширение списка JSON

  6. Столбец Value (Значение) теперь является структурированным столбцом Record (Запись).  Выберите значок Значок развертывания столбца развернуть. Появится диалоговое окно с drop-down.

    Расширение записи JSON

  7. Не избирать все столбцы. Возможно, вам нужно будет очистить поле Использовать имя исходного столбца в качестве префикса. Нажмите кнопку ОК.

  8. Вы можете выбрать все столбцы, содержащие значения данных. На лентевыберите Главная , стрелку рядом с клавишей Remove Columns, а затем выберите Удалить другие столбцы.

  9. Выберите Главная> закрыть & загрузить.

Результат

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

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

  1. Выберите Данные > Получить данные > из файла > из папки. Появится диалоговое окно Обзор.

  2. Найдите папку с файлами, которые вы хотите объединить.

  3. Список файлов в папке появится в диалоговом <путь к> папке. Убедитесь, что в списке указаны все нужные файлы.

  4. Выберите одну из команд в нижней части диалогового окна, например Объединить> Объединить & Загрузить. В разделе Обо всех этих командах обсуждаются дополнительные команды.

  5. В диалоговом окне Объединение файлов:

    • В поле Образец файла выберите файл, который будет использоваться в качестве примера данных для создания запросов. Нельзя выбрать объект или выбрать только один объект. Однако выбрать несколько из них нельзя.

    • Если объектов много, используйте поле Поиск, чтобы найти объект, или параметры отображения, а также кнопку Обновить для фильтрации списка.

    • В нижней части диалогового окна можно выбрать или отобирать для файлов с ошибками поле Пропускать файлы с ошибками.

  6. Нажмите кнопку ОК.

Результат

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

Для большей гибкости вы можете явным образом объединить файлы в редакторе Power Query с помощью команды Объединить файлы. Предположим, что в исходных папках есть как типы файлов, так и вложенные папки, и вы хотите подцелить определенные файлы с тем же типом файлов и схемой, но не с другими. Это может повысить производительность и упростить преобразования.

  1. Выберите data > Get Data > Data > From File > From Folder. Появится диалоговое окно Обзор.

  2. Найдите папку с файлами, которые вы хотите объединить, и выберите открыть.

  3. Список всех файлов в папке и вложенных папках появится в диалоговом<путь>папки. Убедитесь, что в списке указаны все нужные файлы.

  4. Внизу выберите преобразовать данные. Откроется редактор Power Query со всеми файлами в папке и во вложенных папках.

  5. Чтобы выбрать нужные файлы, отфильтруем столбцы, например Расширение или Путь к папке.

  6. Чтобы объединить файлы в одну таблицу, выберите столбец Содержимое, содержащий каждый двоичный (обычно первый столбец), а затем выберите Главная > Объединить файлы. Появится диалоговое окно Объединение файлов.

  7. Power Query анализирует пример файла (по умолчанию первый файл в списке), чтобы использовать правильный соединительщик и определить совпадающие столбцы.

    Чтобы использовать другой файл для примера, выберите его в списке Образец файла.

  8. При желании внизу выберите пропустить файлы с ошибкой s,чтобы исключить эти файлы из результата.

  9. Нажмите кнопку ОК.

Результат

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

Существует несколько команд, которые можно выбрать, и каждая из них имеет разные цели.

  • Объединение и преобразование данных    Чтобы объединить все файлы с запросом, а затем запустить редактор Power Query, выберите объединить> объединить и преобразовать данные.

  • Объединение и загрузка   Чтобы отобразить диалоговое окно Образец файла, создайте запрос, а затем загрузите на таблицу, выберите объединить> Объединить и загрузить.

  • Объединение и загрузка в   Чтобы отобразить диалоговое окно Образец файла, создайте запрос, а затем в диалоговом окне Импорт выберите объединить> Объединить и загрузить в.

  • Нагрузки    Чтобы создать запрос с одним шагом, а затем загрузить на таблицу, выберите загрузить > загрузить.

  • Загрузить в   Чтобы создать запрос одним шагом и отобразить диалоговое окно Импорт, выберите загрузить > Загрузить в.

  • Преобразование данныхЧтобы создать запрос с одним шагом и запустить редактор Power Query, выберите Преобразовать данные.

Тем не менее при объединения файлов в области Запросы в группе «Запросы-справки» создается несколько вспомогательных запросов.

Список запросов, созданных в области "Запросы"

  • Power Query создает запрос «Образец файла» на основе примера запроса.

  • Запрос функции Transform File (Файл преобразования) использует запрос Parameter1 для указания каждого файла (двоичного) в качестве входного в запрос «Образец файла». Этот запрос также создает столбец Содержимое, содержащий содержимое файла, и автоматически расширяет его, чтобы добавить данные столбца в результаты. Запросы «Преобразовать файл» и «Образец файла» связаны, поэтому изменения в запросе «Образец файла» отражаются в запросе «Преобразовать файл».

  • Запрос, содержащий окончательные результаты, находится в группе «Другие запросы». По умолчанию он называется папкой, из которого вы импортировали файлы.

Для дальнейшего изучения щелкните каждый запрос правой кнопкой мыши и выберите Изменить, чтобы изучить каждый шаг запроса и увидеть, как работают запросы на этапе.

Чтобы начать процесс объединения нескольких файлов, сначала поместите их все в одну папку.

Примечание: Поддерживаются файлы Excel и Access, текстовые файлы, а также файлы в форматах CSV, JSON и XML.

  1. На вкладке Power Query выберите из файла > из папки.

    Power Query > параметры "Из > из папки"

  2. Нажмите кнопку Обзор, чтобы перейти к нужной папке.

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

    Диалоговое окно объединения двоичных файлов, в котором показаны файлы, доступные для объединения

  3. Убедившись в том, что все нужные файлы присутствуют в списке, нажмите в столбце Содержимое кнопку Объединить двоичные данные.

    Нажмите кнопку "Объединить двоичные данные" в столбце "Содержимое" или в разделе "Объединить" на ленте Power Query.

  4. Будет выполнен анализ каждого файла и определен правильный формат, например текстовый, Excel или JSON. В этом примере отображается список листов из первой книги Excel. Выберите нужный лист и нажмите кнопку ОК.

    Диалоговое окно объединения двоичных файлов, в котором показаны листы Excel, доступные для выбора в качестве основной цели для размещения объединенных данных

  5. При преобразовании разных файлов автоматически создается запрос для консолидации данных из каждого файла и предварительного просмотра. Если результат вас устраивает, нажмите кнопку Закрыть & загрузить.

    Диалоговое окно для предварительного просмотра результатов объединения. Нажмите "Закрыть и загрузить", чтобы принять результаты и импортировать их в Excel.

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

  6. Если исходные файлы данных изменятся, вы всегда сможете обновить импортируемые данные. Щелкните в любом месте диапазона данных, а затем перейдите в инструменты запросов > обновить. Кроме того, вы можете легко применить дополнительные шаги преобразования или извлечения, изменяя автоматически созданный запрос-образец, не беспокоясь об изменении или создании дополнительных шагов запроса функций. любые изменения запроса-образец автоматически создаются в связанном запросе функции.

Известные проблемы

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

Сообщение об ошибке при объединении двоичных файлов. Это известная ошибка, которая будет устранена.

Если вы проигнорируете ошибки и нажмете кнопку ОК, то процесс объединения двоичных файлов будет выполнен и правильно загрузит данные, несмотря на это сообщение. Мы работаем над устранением этой ошибки.

См. также

Справка по Power Query для Excel

Добавление запросов

Обзор объединения файлов (docs.com)

Объединение CSV-файлов в Power Query (docs.com)

Нужна дополнительная помощь?

Источник

0 / 0 / 0

Регистрация: 06.08.2019

Сообщений: 5

1

Excel

06.08.2019, 16:22. Показов 19859. Ответов 9

Студворк — интернет-сервис помощи студентам

Здравствуйте!

Помогите сделать два макроса в excel, которые будут копировать данные из множества файлов excel в один файл excel.
Я полный новичок в данной теме… Находил различные варианты в интернете, но отредактировать под себя не получается.

Все файлы excel, из которых будут копироваться данные, одинаковой структуры, а файл в который необходимо записать данные другой структуры. Все данные находятся на первом листе.

К примеру:
Первый макрос.
В каждом файле excel необходимые данные для копирования находятся в ячейке C3 и в диапазоне C6 : C13, а собрать все эти данные необходимо в единственном файле excel и расположить в нем нужно так —> из первого файла C3->A5 (из следующего файла C3->A6 и т.д.), из первого файла диапазон C6 : C13 -> B5 : I5 (из следующего файла C6 : C13 -> B6 : I6 и т.д.) получается из столбца в строку. Данные каждого нового файла с новой строки.

Второй макрос точно такой же и с теми же файлами, но берется диапазон D6 : D13 вместо C6 : C13 и записывается так же, только это уже будет для другого файла.

По факту будут две папки с файлами excel в одной будет работать макрос по диапазону C6 : C13, в другой папке те же файлы только макрос там будет работать по диапазону D6 : D13

Заранее спасибо!

К этой теме прикрепил две папки с образцами файлов из которых будут копироваться данные и файлы в которые все будет собираться.

P.S Буду учиться делать макросы на этом примере и на других что найду…



0

1813 / 1135 / 346

Регистрация: 11.07.2014

Сообщений: 4,002

07.08.2019, 05:33

2

Just67, включаете макрорекодер (Разработчик — Запись макроса) и проделываете все действия, которые вы написали, потом Остановить запись и получаете макрос ваших действий вручную. Потом подрабатываете его, например, вставляете в цикл по строкам, при этом заменяете конкретный номер строки на переменную цикла.



1

Just67

0 / 0 / 0

Регистрация: 06.08.2019

Сообщений: 5

07.08.2019, 10:18

 [ТС]

3

Спасибо за совет. Макрос я записал своих действий, но дальше для меня не понятно. Как я говорил, я в этом полный новичок(знаний практически нет).
Если вам не трудно, можете написать работающий макрос?
Вот результат записи макроса:

Visual Basic 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

Sub Макрос3()
'
' Макрос3 Макрос
'
 
'
    Windows("файл 1.xlsx").Activate
    Range("C3").Select
    Selection.Copy
    Windows("Сюда собираются данные из файлов.xlsx").Activate
    Range("A5").Select
    ActiveSheet.Paste
    Windows("файл 1.xlsx").Activate
    Range("C6:C13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Сюда собираются данные из файлов.xlsx").Activate
    Range("B5").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Windows("файл 2.xlsx").Activate
    Range("C3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Сюда собираются данные из файлов.xlsx").Activate
    Range("A6").Select
    ActiveSheet.Paste
    Windows("файл 2.xlsx").Activate
    Range("C6:C13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Сюда собираются данные из файлов.xlsx").Activate
    Range("B6").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
End Sub

Количество файлов и их название из которых информация копируется не известно.



0

Burk

1813 / 1135 / 346

Регистрация: 11.07.2014

Сообщений: 4,002

07.08.2019, 14:19

4

Just67, ну этот макрос тоже должен работать, хотя его можно значительно упростить. Я могу, конечно, без проверки это сделать т.к. не хочется ещё и создавать ваши файлы с данными. Но можете прислать файлы, тогда с проверкой. Какой вариант вам больше подходит?

Добавлено через 42 минуты
Посмотрите, что получилось из вашего макроса

Visual Basic 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

Sub Perenos()
'
Dim From As Workbook, Ins As Workbook, Win As Workbook, I As Integer, S As String
Set Ins = Workbooks("Сюда собираются данные из файлов.xlsm")
Ins.Activate
For I = 1 To 2
  Set From = Workbooks("файл " & I & ".xlsx")
  With From.Sheets(1)
     Range("A" & (I + 4)) = .Range("C3")
    .Range("C6:C13").Copy
    Range("B" & (I + 4)).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
         SkipBlanks:=False, Transpose:=True
  End With
Next
End Sub



1

0 / 0 / 0

Регистрация: 06.08.2019

Сообщений: 5

07.08.2019, 15:29

 [ТС]

5

Не получилось запустить макрос выдал ошибку Run-time error ‘9’: Subscript out of range
макрос останавливается на 7 строке «Set From = Workbooks(«файл » & I & «.xlsx»)».

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

Скриншот в приложении

Миниатюры

Макросы на копирование данных из нескольких файлов excel в один файл excel
 



0

1813 / 1135 / 346

Регистрация: 11.07.2014

Сообщений: 4,002

07.08.2019, 16:25

6

Just67, макрос работает, вы что не видите, что имя файлов, из которых беруться данные, должно быть файл 1.xlsx и файл 2.xlsx

как в вашем макросе

И давайте запустите этот, а что будет дальше, то потом. Конечно надо расширить цикл, если три файла, то и цикл до трёх и т.д., если названия непохожие друг на друга, то нужно будет создать массив имен и обращаться к массивам через него. Вы пока не думайте так далеко, вредно это.
И в начале сообщения нажимайте на мой ник, тогда я буду знать, что есть упоминание в теме
перед числом в имени файла пробел

Добавлено через 6 минут
обращаться к файлам через него

Добавлено через 33 минуты
перед запуском макроса все три файла должны быть загружены в эксель



1

Burk

1813 / 1135 / 346

Регистрация: 11.07.2014

Сообщений: 4,002

08.08.2019, 04:47

7

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

Visual Basic 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

Sub Perenos2()
Dim From As Workbook, Ins As Workbook, I As Integer
Set Ins = Workbooks("Сюда собираются данные из файлов.xlsm")
Ins.Activate: I = 4
For Each From In Workbooks
  If From.Name <> ThisWorkbook.Name Then
    I = I + 1
    With From.Sheets(1)
      Range("A" & I) = .Range("C3")
      .Range("C6:C13").Copy
      Range("B" & I).Select
      Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
           SkipBlanks:=False, Transpose:=True
    End With
  End If
Next
Application.CutCopyMode = False
If I = 4 Then MsgBox "Не загружены файлы с исходной информацией!"
End Sub



0

Just67

0 / 0 / 0

Регистрация: 06.08.2019

Сообщений: 5

08.08.2019, 12:31

 [ТС]

8

Burk, Спасибо за подробное разъяснение. Я когда запускал макрос имена не поменял, после того как поменял и открыл нужные таблицы макрос сделал все как надо.
И последний макрос запустил все работает как надо, спасибо.
Можете еще посоветовать каким образом лучше в макросе сделать проверку на пустую ячейку?
К примеру: если в ячейки «C3» нет данных(пустая), то в ячейку «A» файла «Сюда собираются данные из файлов.xlsm» указывается название файла, в котором ячейка «C3» пустая.

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

Код:

Visual Basic 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

Sub Perenos()
Dim From As Workbook, Ins As Workbook, I As Integer
Set Ins = Workbooks(".Сюда собираются данные из файлов.xlsm")
Ins.Activate: I = 4
For Each From In Workbooks
  If From.Name <> ThisWorkbook.Name Then
    I = I + 1
    With From.Sheets(1)
        If .Range("C3") = "" Then
        Range("A" & I) = From.Name
        Else
      Range("A" & I) = .Range("C3")
        End If
      .Range("C6:C13").Copy
      Range("B" & I).Select
      Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
           SkipBlanks:=False, Transpose:=True
    End With
  End If
Next
Application.CutCopyMode = False
If I = 4 Then MsgBox "Не загружены файлы с исходной информацией!"
End Sub

Добавлено через 10 минут
Burk, Еще было бы интересно узнать каким образом можно сделать проверку на пустые ячейки в диапазоне C6 : C13 и если такие есть, то вместо пустой ячейки записывать «0».

Добавлено через 10 минут
Извиняюсь, что задаю много вопросов.

Что нужно дописать в макрос, чтобы не нужно было загружать файлы в эксель?



0

Burk

1813 / 1135 / 346

Регистрация: 11.07.2014

Сообщений: 4,002

08.08.2019, 15:40

9

Just67, первый вопрос

Visual Basic 
1
2
3
4
5
6

    Range("A" & I) = Left(From.Name, InstrRev(From.Name,".") - 1)
'второй вопрос - видимо имеются в виду нули в копиях этих ячеек файла "Сюда собираются ..."?
'Если так, то после строки 17 вставить
For J = 2 To 9
     If Cells(I, J) = "" Then Cells(I, J) = 0
Next

третий вопрос — У меня не было необходимости вытаскивать значения ячеек из закрытой книги, но подобные темы выплывают регулярно и на этом форуме, последний раз где-то 2-3 месяца назад. Наберите в поиске типа Получить информацию из закрытой книги или обратитесь к Hugo121 или pashulka, они, по-моему, писали ответы, мне бы не хотелось с этим разбираться.

Добавлено через 2 часа 34 минуты
Just67, чтобы не привязываться к имени файла, в который копируется, можно строку 4 в макросе записать так (описание для Ins можно убрать) и удалить строку 3

Visual Basic 
1

ThisWorkbook.Sheets(1).Activate: I = 4



0

0 / 0 / 0

Регистрация: 06.08.2019

Сообщений: 5

08.08.2019, 17:02

 [ТС]

10

Burk, Спасибо, все работает.



0
Источник

Skip to content

Как быстро объединить несколько файлов Excel

Мы рассмотрим три способа объединения файлов Excel в один: путем копирования листов, запуска макроса VBA и использования инструмента «Копировать рабочие листы» из надстройки Ultimate Suite.

Намного проще обрабатывать данные в одном файле, чем переключаться между многочисленными книгами. Однако объединение нескольких книг Excel в один файл может быть сложным и долгим процессом, особенно если книги, которые вам нужно объединить, содержат много листов. Итак, как подойти к этой проблеме? Вы будете копировать их вручную или с помощью кода VBA? Или вы используете один из специализированных инструментов для объединения файлов Excel? 

Ниже вы найдете несколько хороших способов, позволяющих реализовать объединение.

  • Самое простое — копировать вручную.
  • Объединение файлов Excel при помощи VBA.
  • Как объединить несколько файлов с помощью Ultimate Suite.

Примечание. В этой статье мы рассмотрим, как копировать листы из нескольких книг Excel в одну книгу. Если вы ищете быстрый способ скопировать данные с нескольких листов на один общий лист, вы найдете подробную инструкцию в другой статье: Как объединить несколько листов в один.

Простой метод — копировать листы руками.

Если вам нужно объединить всего пару файлов Excel, вы можете вручную скопировать или переместить листы из одного файла в другой. Вот как это можно сделать:

  1. Откройте книги, которые мы планируем объединить.
  2. Выберите листы в исходной книге, которые вы хотите скопировать в основную книгу.

Чтобы выбрать несколько листов, используйте один из следующих приемов:

  • Чтобы выбрать соседние листы, щелкните вкладку первого, который вы хотите скопировать, нажмите и удерживайте клавишу Shift, а затем щелкните вкладку последнего. Это действие выберет все листы между ними.
  • Чтобы выбрать несмежные, удерживайте клавишу Ctrl и щелкайте вкладку каждого из них по отдельности.
  • Выделив все нужные листы, щелкните правой кнопкой мыши любую из выделенных вкладок и выберите «Переместить» или «Копировать…» .

  1. В диалоговом окне «Перемещение или копирование» выполните следующие действия:
    • В раскрывающемся списке «Переместить выбранные листы в книгу» выберите целевую книгу, в которую вы хотите объединить другие файлы.
    • Укажите, где именно должны быть вставлены вкладки. В нашем случае мы выбираем вариант вставки в конец списка.
    • Установите флажок «Создать копию», если хотите, чтобы исходные данные оставались оригинальном файле.
    • Нажмите ОК, чтобы завершить операцию.

Чтобы объединить вкладки из нескольких файлов Excel, повторите описанные выше шаги для каждой книги отдельно.

Замечание. При копировании листов вручную помните о следующем ограничении, налагаемом Excel: невозможно переместить или скопировать группу листов, если какой-либо из них содержит «умную» таблицу. В этом случае вам придется либо преобразовать таблицу в диапазон, либо использовать один из других методов, не имеющих этого ограничения.

Как объединить файлы Excel с VBA

Если у вас есть несколько файлов Excel, которые необходимо объединить в один файл, более быстрым способом будет автоматизировать процесс с помощью макроса VBA.

Ниже вы найдете код VBA, который копирует все листы из всех файлов Excel, которые вы выбираете, в одну книгу. Этот макрос MergeExcelFiles написан Алексом.

Важное замечание! Макрос работает со следующим ограничением — объединяемые файлы не должны быть открыты физически или находиться в памяти, в буфере обмена. В таком случае вы получите ошибку во время выполнения.

Sub MergeExcelFiles()
    Dim fnameList, fnameCurFile As Variant
    Dim countFiles, countSheets As Integer
    Dim wksCurSheet As Worksheet
    Dim wbkCurBook, wbkSrcBook As Workbook
 
    fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)
 
    If (vbBoolean <> VarType(fnameList)) Then
 
        If (UBound(fnameList) > 0) Then
            countFiles = 0
            countSheets = 0
 
            Application.ScreenUpdating = False
            Application.Calculation = xlCalculationManual
 
            Set wbkCurBook = ActiveWorkbook
 
            For Each fnameCurFile In fnameList
                countFiles = countFiles + 1
 
                Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)
 
                For Each wksCurSheet In wbkSrcBook.Sheets
                    countSheets = countSheets + 1
                    wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
                Next
 
                wbkSrcBook.Close SaveChanges:=False
 
            Next
 
            Application.ScreenUpdating = True
            Application.Calculation = xlCalculationAutomatic
 
            MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
        End If
 
    Else
        MsgBox "No files selected", Title:="Merge Excel files"
    End If
End Sub

Как добавить этот макрос в книгу

Если вы хотите вставить макрос в свою книгу, выполните следующие обычные действия:

  1. нажимать Alt + F11 , чтобы открыть редактор Visual Basic.
  2. Щелкните правой кнопкой мыши ThisWorkbook на левой панели и выберите « Вставить» > « Модуль» в контекстном меню.
  3. В появившемся окне (Окно кода) вставьте указанный выше код.

Более подробная инструкция описана в разделе Как вставить и запустить код VBA в Excel .

Кроме того, вы можете загрузить макрос в файле Excel, открыть его в этой книге (включить выполнение макросов, если будет предложено), а затем переключиться на свою собственную книгу и нажать Alt + F8 для его запуска. Если вы новичок в использовании макросов в Excel, следуйте подробным инструкциям ниже.

Как использовать макрос MergeExcelFiles

Откройте файл Excel, в котором вы хотите объединить листы из других книг, и выполните следующие действия:

  1. Нажмите комбинацию Alt + F8, чтобы открыть окно диалога.
  2. В разделе « Имя макроса» выберите MergeExcelFiles и нажмите «Выполнить».

  1. Откроется стандартное окно проводника, вы выберите одну или несколько книг, которые хотите объединить, и нажмите «Открыть» . Чтобы выбрать несколько файлов , удерживайте нажатой клавишу Ctrl, указывая на их имена.

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

Как объединить несколько файлов с помощью Ultimate Suite.

Если вам не очень комфортно с VBA и вы ищете более простой и быстрый способ объединить файлы Excel, обратите внимание на инструмент «Копирование листов (Copy Sheets)» — одну из более чем 60 функций, включенных в невероятно функциональную программу Ultimate Suite for Excel. Она работает в версиях Excel 2010-2019.

С Ultimate Suite объединение нескольких файлов Эксель в один так же просто, как раз-два-три (буквально, всего 3 быстрых шага). Вам даже не нужно открывать те из них, которые вы хотите объединить. И это могут быть два файла или несколько — не важно.

  1. Открыв главную книгу, перейдите на вкладку «Ablebits Data» и нажмите «Копировать листы (Copy Sheets)» > «Выбранные в одну книгу (Selected Sheets to one workbook)».

  1. В диалоговом окне выберите файлы (а в них — листы), которые вы хотите объединить, и нажмите «Далее (Next)» .

Советы:

  • Чтобы выбрать все листы в определенной книге, просто поставьте галочку в поле рядом с именем книги, и все они в этом файле будут выбраны автоматически.
  • Чтобы объединить листы из закрытых книг, нажмите кнопку «Добавить файлы…» и выберите столько книг, сколько нужно. Это добавит выбранные файлы только в окно копирования, не открывая их в Excel.
  • По умолчанию копируются все данные. Однако, в разных листах можно выбрать разные диапазоны для объединения. Чтобы скопировать только определенную область, наведите указатель мыши на имя вкладки, затем щелкните значок    и выберите нужный диапазон. 
  • При необходимости укажите один или несколько дополнительных параметров и нажмите «Копировать» . На снимке скриншоте а ниже показаны настройки по умолчанию: Вставить все (формулы и значения) и Сохранить форматирование.

Дайте мастеру копирования листов несколько секунд для обработки и наслаждайтесь результатом!

На этой странице есть подробное описание всех возможностей работы мастера копирования.

Чтобы поближе познакомиться с этим и другими инструментами для Excel, вы можете загрузить ознакомительную версию Ultimate Suite.

Итак, я надеюсь, вы получили ответ на вопрос — как быстро объединить несколько файлов Excel в один.

Источник

Понравилась статья? Поделить с друзьями:

А вот еще интересные статьи:

  • Копирование нескольких листов на один лист excel
  • Копирование значений ячеек в excel без формулы
  • Копирование название файлов в excel
  • Копирование значений ячеек vba excel
  • Копирование мышью в excel

    • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии