Дано: Есть 4 файла: 1. Файл1.xlsb 2. Файл2.xlsb 3. Файл3.xlsb 4. Общий. xlsb У всех файлов одинаковый заголовок из 3х столбцов: № п/п, ФИО, Дата. В Файл1 - заполнено 10 строк данных (со временем может меняться) В Файл2 - заполнено 20 строк данных (со временем может меняться) В Файл3 - заполнено 30 строк данных (со временем может меняться)
Задача. Как собрать эти 3 таблицы в одну большую в отдельный файл Общий.xlsb? Еще важный момент - со временем значения ячеек и количество строк в первых 3х файлах может меняться, нужно, чтоб в общем файле эти изменения также обновлялись.
Что НЕ предлагать, потому что это не является решением. Вариант "копи-паст" - НЕ предлагать. Нужно создать связь с источником. Вариант создания связи при копи-пасте я тоже знаю (в excel 2019 это дословно называется "Вставить ссылку (Ь)"), но при таком варианте не решается вопрос обновления количества строк - поэтому его тоже НЕ предлагать.
Возможно ли вообще такое стандартными способами? Или же нужно использовать VBA? Может быть есть решение с помощью формул массивов? Также не имею опыта работы с PQ - может через него такие задачи решаются?
=QUERY(
{
IMPORTRANGE("1UEYpgEaxdo7WwJ5nE0ek89mTcP3-5xyhmZ7xaHpPSmo";"A:A");
IMPORTRANGE("1Pn_86UmcXHHR18vw3hL17MjdwXg4Kx5VKm7x6zsoVug";"A:A");
IMPORTRANGE("1SZv9hrTB41UizfzD17_6plqREiYlHz6opPVNFwa1NYk";"A:A")
};"where Col1 is not null")
Option Explicit
Sub Собрать()
Dim aFiles As Variant
aFiles = ShowFileDialog()
Dim wb1 As Workbook
Dim wb2 As Workbook
Set wb1 = Workbooks.Add(1)
Dim r As Range
Dim t As Range
Set r = wb1.Sheets(1).Cells(1, 1)
Dim i As Long
For i = 1 To UBound(aFiles)
Set wb2 = Workbooks.Open(aFiles(i), False, True)
Set t = wb2.Sheets(1).UsedRange
If i > 1 Then
If t.Rows.Count > 1 Then
Set t = t.Offset(1, 0)
Set t = t.Resize(t.Rows.Count - 1)
End If
End If
t.Copy r
Application.CutCopyMode = False
Set r = r.Offset(t.Rows.Count)
wb2.Close
Next
End Sub
Function ShowFileDialog() As Variant
Dim arr As Variant
ReDim arr(0 To 0)
Dim oFD As FileDialog
Dim x, lf As Long
'назначаем переменной ссылку на экземпляр диалога
Set oFD = Application.FileDialog(msoFileDialogFilePicker)
With oFD 'используем короткое обращение к объекту
'так же можно без oFD
'With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = True
.Title = "Выбрать файлы отчетов" 'заголовок окна диалога
.Filters.Clear 'очищаем установленные ранее типы файлов
.Filters.Add "Excel files", "*.xls*, 1 'устанавливаем возможность выбора только файлов Excel"
'.Filters.Add "Text files", "*.txt", 2 'добавляем возможность выбора текстовых файлов
.FilterIndex = 1 'устанавливаем тип файлов по умолчанию - Text files(Текстовые файлы)
.InitialFileName = ThisWorkbook.Path 'назначаем папку отображения и имя файла по умолчанию
.InitialView = msoFileDialogViewDetails 'вид диалогового окна(доступно 9 вариантов)
If oFD.Show <> 0 Then
'цикл по коллекции выбранных в диалоге файлов
ReDim arr(1 To .SelectedItems.Count)
For lf = 1 To .SelectedItems.Count
arr(lf) = .SelectedItems(lf) 'считываем полный путь к файлу
Next
End If
End With
ShowFileDialog = arr
End Function