Совет. Другие языки - Google-Translated. Вы можете посетить English версия этой ссылки.
Войти
x
or
x
x
Регистрация
x

or

Как перемещать файлы в каталоге и копировать данные в мастер-лист в Excel?

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

Пронумеруйте файлы в каталог и скопируйте данные в мастер-лист с кодом VBA


Легко копировать / импортировать данные из закрытой книги в основной рабочий лист:

С правилами Вставить файл в курсор полезности Kutools for Excel, вы можете легко скопировать данные из указанного листа закрытой книги в открытую книгу. Смотрите скриншот:

Kutools for Excel: с более чем 200 удобными надстройками Excel, бесплатно попробовать без ограничений в 60-дни. Скачать и бесплатно пробную версию!

Вкладка «Office» Включите редактирование и просмотр с вкладками в Office и упростите свою работу ...
Kutools для Excel добавляет расширенные функции 300 в Excel и повышает производительность на 80%
  • Супер Формула Бар (легко редактировать несколько строк текста и формул); Макет чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы и хранение данных; Содержание сплит-клеток; Объедините дублирующиеся строки и сумму / среднее... предотвратить повторяющиеся клетки; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выберите пустые строки (все ячейки пусты); Супер найти и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения формулы ссылки; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Любимые и быстро вставляемые формулы, Диапазоны, графики и рисунки; Шифрование ячеек с паролем; Создать список рассылки и отправлять электронные письма ...
  • Извлечение текстаДобавить текст, Удалить по позиции, Удалить пространство; Создание и печать промежуточных итогов подкачки; Преобразование содержимого ячеек и комментариев...
  • Суперфильтр (сохранить и применить схемы фильтров к другим листам); Расширенный поиск по месяцам / неделям / дням, частоте и более; Специальный фильтр жирным шрифтом, курсивом ...
  • Объединить рабочие тетради и рабочие листы; Объединение таблиц на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Kutools работает с Office 2007-2019 и 365. Он поддерживает все языки и очень прост в установке и развертывании. Полнофункциональная 60-дневная бесплатная пробная версия.

Пронумеруйте файлы в каталог и скопируйте данные в мастер-лист с кодом VBA


Если вы хотите скопировать указанные данные в диапазоне A1: D4 со всего листаXXUMX книг в определенной папке на мастер-лист, сделайте следующее.

1. В рабочей книге вы создадите мастер-лист, нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окна.

2. в Microsoft Visual Basic для приложений окна, нажмите Вставить > модуль, Затем скопируйте ниже код VBA в окно кода.

Код VBA: перебирать файлы в папке и копировать данные в мастер-лист

Sub Merge2MultiSheets()
    Dim xRg As Range
    Dim xSelItem As Variant
    Dim xFileDlg As FileDialog
    Dim xFileName, xSheetName, xRgStr As String
    Dim xBook, xWorkBook As Workbook
    Dim xSheet As Worksheet
    On Error Resume Next
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    xSheetName = "Sheet1"
    xRgStr = "A1:D4"
    Set xFileDlg = Application.FileDialog(msoFileDialogFolderPicker)
    With xFileDlg
        If .Show = -1 Then
            xSelItem = .SelectedItems.Item(1)
            Set xWorkBook = ThisWorkbook
            Set xSheet = xWorkBook.Sheets("New Sheet")
            If xSheet Is Nothing Then
                xWorkBook.Sheets.Add(after:=xWorkBook.Worksheets(xWorkBook.Worksheets.Count)).Name = "New Sheet"
                Set xSheet = xWorkBook.Sheets("New Sheet")
            End If
            xFileName = Dir(xSelItem & "\*.xlsx", vbNormal)
            If xFileName = "" Then Exit Sub
            Do Until xFileName = ""
               Set xBook = Workbooks.Open(xSelItem & "\" & xFileName)
                Set xRg = xBook.Worksheets(xSheetName).Range(xRgStr)
                xRg.Copy xSheet.Range("A65536").End(xlUp).Offset(1, 0)
                xFileName = Dir()
                xBook.Close
            Loop
        End If
    End With
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Внимание:

1. В коде "A1: D4(Основной ключ) и Sheet1"Означает, что данные в диапазоне A1: D4 всего Sheet1 будут скопированы в мастер-лист. А также "Новый лист"- это имя нового созданного мастер-листа.

2. Файлы Excel в конкретной папке не должны открываться.

3. нажмите F5 для запуска кода.

4. В открытии просмотреть окна, выберите папку, в которую будут вставляться файлы, а затем нажмите OK кнопка. Смотрите скриншот:

Затем в конце текущей книги создается рабочий лист с именем «Новый лист». И данные в диапазоне A1: D4 всего Sheet1 в выбранной папке перечислены внутри рабочего листа.


Статьи по теме:


Kutools для Excel - лучший инструмент для повышения производительности в офисе Повысьте производительность на 80%

  • Супер Формула Бар (легко редактировать несколько строк текста и формул); Макет чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы и хранение данных; Содержание сплит-клеток; Объедините дублирующиеся строки и сумму / среднее... предотвратить повторяющиеся клетки; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выберите пустые строки (все ячейки пусты); Супер найти и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения формулы ссылки; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Любимые и быстро вставляемые формулы, Диапазоны, графики и рисунки; Шифрование ячеек с паролем; Создать список рассылки и отправлять электронные письма ...
  • Извлечение текстаДобавить текст, Удалить по позиции, Удалить пространство; Создание и печать промежуточных итогов подкачки; Преобразование содержимого ячеек и комментариев...
  • Суперфильтр (сохранить и применить схемы фильтров к другим листам); Расширенный поиск по месяцам / неделям / дням, частоте и более; Специальный фильтр жирным шрифтом, курсивом ...
  • Объединить рабочие тетради и рабочие листы; Объединение таблиц на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Работает с Office 2007-2019 и 365 и поддерживает все языки. Это легко развернуть в вашей компании. Полнофункциональная 60-дневная бесплатная пробная версия.
вкладка kte 201905

Вкладка «Office» предоставляет интерфейс с вкладками для Office и упрощает работу

  • Включить редактирование и чтение с вкладками в Word, Excel, PowerPoint, Издатель, Доступ, Visio и Проект.
  • Открывайте и создавайте несколько документов в новых вкладках одного и того же окна, а не в новых окнах.
  • Увеличивает вашу производительность на 50% и уменьшает сотни щелчков мышью для вас каждый день!
нижняя часть офиса
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Trish · 2 months ago
    Hi there, This works great. Is there a way to change to just pull over the values and not the formula?
    Thanks!!
  • To post as a guest, your comment is unpublished.
    Robertson · 3 months ago
    Hello,

    Thank you for the tutorial.

    How would I: Only copy the row in "Sheet1" with values from the "total" row and paste with [filename] in master worksheet named “New Sheet”. Noting the row with Total can be different in each worksheet.

    For example:
    File1: Sheet1
    Col1,Col2,Colx
    1,2,15
    Result,10,50

    File2: Sheet1
    Col1,Col2,Colx
    1,5,10
    2,4,16
    3,3,6
    4,5,6
    5,7,10
    Result,300,500

    MasterFile: "New Sheet":
    file1, 10, 50
    file2, 300, 500
  • To post as a guest, your comment is unpublished.
    Paul Gill · 4 months ago
    Hi, thanks for the code. Please can you let me know how I can include the Excel file name from which the data range was copied? This would be a great help!

    Thank you.
  • To post as a guest, your comment is unpublished.
    Lai Ling · 7 months ago
    thank you for the vba code! It works perfectly! Would like to know what is the code if i need to PASTE AS VALUE instead? Thx in advance!
    • To post as a guest, your comment is unpublished.
      crystal · 6 months ago
      Hi Lai Ling,
      The following code can help you solve the problem. Thank you for your comment.

      Sub Merge2MultiSheets()
      Dim xRg As Range
      Dim xSelItem As Variant
      Dim xFileDlg As FileDialog
      Dim xFileName, xSheetName, xRgStr As String
      Dim xBook, xWorkBook As Workbook
      Dim xSheet As Worksheet
      On Error Resume Next
      Application.DisplayAlerts = False
      Application.EnableEvents = False
      Application.ScreenUpdating = False
      xSheetName = "Sheet1"
      xRgStr = "A1:D4"
      Set xFileDlg = Application.FileDialog(msoFileDialogFolderPicker)
      With xFileDlg
      If .Show = -1 Then
      xSelItem = .SelectedItems.Item(1)
      Set xWorkBook = ThisWorkbook
      Set xSheet = xWorkBook.Sheets("New Sheet")
      If xSheet Is Nothing Then
      xWorkBook.Sheets.Add(after:=xWorkBook.Worksheets(xWorkBook.Worksheets.count)).Name = "New Sheet"
      Set xSheet = xWorkBook.Sheets("New Sheet")
      End If
      xFileName = Dir(xSelItem & "\*.xlsx", vbNormal)
      If xFileName = "" Then Exit Sub
      Do Until xFileName = ""
      Set xBook = Workbooks.Open(xSelItem & "\" & xFileName)
      Set xRg = xBook.Worksheets(xSheetName).Range(xRgStr)
      xRg.Copy xSheet.Range("A65536").End(xlUp).Offset(1, 0)
      xFileName = Dir()
      xBook.Close
      Loop
      End If
      End With
      Set xRg = xSheet.UsedRange
      xRg.ClearFormats
      xRg.UseStandardHeight = True
      xRg.UseStandardWidth = True
      Application.DisplayAlerts = True
      Application.EnableEvents = True
      Application.ScreenUpdating = True
      End Sub