Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/консолидация данных с суммированием для всех файлов в папке - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » консолидация данных с суммированием для всех файлов в папке (Макросы/Sub)
консолидация данных с суммированием для всех файлов в папке
Мурад Дата: Четверг, 29.11.2018, 15:57 | Сообщение № 1
Группа: Проверенные
Ранг: Ветеран
Сообщений: 513
Репутация: 18 ±
Замечаний: 0% ±

Excel 2007
Добрый день! Подскажите, как расширить возможности макроса из темы http://www.excelworld.ru/forum/10-16320-1 ,чтобы была возможность суммировать все файлы (на каждом листе свое суммирование) из папки, выбираемой пользователем. Для результирующего файла формат листа, ширина столбцов берется из любого файла.


Сообщение отредактировал Мурад - Четверг, 29.11.2018, 16:51
 
Ответить
СообщениеДобрый день! Подскажите, как расширить возможности макроса из темы http://www.excelworld.ru/forum/10-16320-1 ,чтобы была возможность суммировать все файлы (на каждом листе свое суммирование) из папки, выбираемой пользователем. Для результирующего файла формат листа, ширина столбцов берется из любого файла.

Автор - Мурад
Дата добавления - 29.11.2018 в 15:57
Мурад Дата: Пятница, 30.11.2018, 09:19 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 513
Репутация: 18 ±
Замечаний: 0% ±

Excel 2007
Коллегой написан макрос, суммирующий данные со всех листов, но время его выполнения удручает. 130 файлов обрабатываются более 30 минут.. Прилагаю файл
К сообщению приложен файл: _--.xlsm (45.6 Kb)
 
Ответить
СообщениеКоллегой написан макрос, суммирующий данные со всех листов, но время его выполнения удручает. 130 файлов обрабатываются более 30 минут.. Прилагаю файл

Автор - Мурад
Дата добавления - 30.11.2018 в 09:19
boa Дата: Пятница, 30.11.2018, 10:20 | Сообщение № 3
Группа: Друзья
Ранг: Ветеран
Сообщений: 549
Репутация: 167 ±
Замечаний: 0% ±

365
Мурад,
в вашей "простыне" долго разбираться не стал, скажу только, что отключение лишь обновления экрана мало, надо еще отключать события листа, автопересчет и т.п. как это сделал Kuzmich в макросе по ссылке. правда, в вашей простыне я его макроса не нашел...
во вторых, рекомендую использовать объектные переменные.
Например, Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111") - это объект и если вы его в макросе используете 101 раз, то замените на переменную.
так же можно использовать конструкцию With ... End With
Один макрос я вам поправил, добавил две функции для включения и отключения обновление экрана и т.п., а остальное сами сделайте по аналогии...

вы удивитесь, на сколько изменится скорость выполнения.




Сообщение отредактировал boa - Пятница, 30.11.2018, 10:44
 
Ответить
СообщениеМурад,
в вашей "простыне" долго разбираться не стал, скажу только, что отключение лишь обновления экрана мало, надо еще отключать события листа, автопересчет и т.п. как это сделал Kuzmich в макросе по ссылке. правда, в вашей простыне я его макроса не нашел...
во вторых, рекомендую использовать объектные переменные.
Например, Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111") - это объект и если вы его в макросе используете 101 раз, то замените на переменную.
так же можно использовать конструкцию With ... End With
Один макрос я вам поправил, добавил две функции для включения и отключения обновление экрана и т.п., а остальное сами сделайте по аналогии...

вы удивитесь, на сколько изменится скорость выполнения.

Автор - boa
Дата добавления - 30.11.2018 в 10:20
Мурад Дата: Пятница, 30.11.2018, 11:59 | Сообщение № 4
Группа: Проверенные
Ранг: Ветеран
Сообщений: 513
Репутация: 18 ±
Замечаний: 0% ±

Excel 2007
boa, огромное Вам спасибо! Сделал по аналогии в других модулях.
Но что делать, если после определения переменной sh, идет активация 2 книг (основная и переменная функции inputbook)? Задавать 2 переменную?
[vba]
Код
Sub КВР_111(inputbook As String)
Dim sh As Worksheet
Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Activate
Application.Workbooks(inputbook).Worksheets("КВР_111").Activate
Dim AutoCalculat As Boolean: AutoCalculat = Prepare
For i = 11 To 12
       Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, 4).Value = Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, 4).Value + Application.Workbooks(inputbook).Worksheets("КВР_111").Cells(i, 4).Value
Next i
For i = 14 To 18
       Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, 4).Value = Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, 4).Value + Application.Workbooks(inputbook).Worksheets("КВР_111").Cells(i, 4).Value
Next i

For i = 11 To 12
       Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, 11).Value = Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, 11).Value + Application.Workbooks(inputbook).Worksheets("КВР_111").Cells(i, 11).Value
Next i
For i = 14 To 18
       Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, 11).Value = Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, 11).Value + Application.Workbooks(inputbook).Worksheets("КВР_111").Cells(i, 11).Value
Next i

For j = 6 To 10
For i = 21 To 28
Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, j).Value = Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, j).Value + Application.Workbooks(inputbook).Worksheets("КВР_111").Cells(i - 10, j).Value

Next i
Next j
Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(22, 5).Value = Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(22, 5).Value + 1

If UserForm1.CheckBox1.Value = True Then
For j = 12 To 17
For i = 11 To 12
       Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, j).Value = Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, j).Value + Application.Workbooks(inputbook).Worksheets("КВР_111").Cells(i, j).Value
Next i
For i = 14 To 18
       Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, j).Value = Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, j).Value + Application.Workbooks(inputbook).Worksheets("КВР_111").Cells(i, j).Value
Next i
Next j
End If
Call Ended(AutoCalculat)
End Sub
[/vba]
 
Ответить
Сообщениеboa, огромное Вам спасибо! Сделал по аналогии в других модулях.
Но что делать, если после определения переменной sh, идет активация 2 книг (основная и переменная функции inputbook)? Задавать 2 переменную?
[vba]
Код
Sub КВР_111(inputbook As String)
Dim sh As Worksheet
Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Activate
Application.Workbooks(inputbook).Worksheets("КВР_111").Activate
Dim AutoCalculat As Boolean: AutoCalculat = Prepare
For i = 11 To 12
       Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, 4).Value = Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, 4).Value + Application.Workbooks(inputbook).Worksheets("КВР_111").Cells(i, 4).Value
Next i
For i = 14 To 18
       Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, 4).Value = Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, 4).Value + Application.Workbooks(inputbook).Worksheets("КВР_111").Cells(i, 4).Value
Next i

For i = 11 To 12
       Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, 11).Value = Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, 11).Value + Application.Workbooks(inputbook).Worksheets("КВР_111").Cells(i, 11).Value
Next i
For i = 14 To 18
       Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, 11).Value = Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, 11).Value + Application.Workbooks(inputbook).Worksheets("КВР_111").Cells(i, 11).Value
Next i

For j = 6 To 10
For i = 21 To 28
Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, j).Value = Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, j).Value + Application.Workbooks(inputbook).Worksheets("КВР_111").Cells(i - 10, j).Value

Next i
Next j
Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(22, 5).Value = Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(22, 5).Value + 1

If UserForm1.CheckBox1.Value = True Then
For j = 12 To 17
For i = 11 To 12
       Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, j).Value = Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, j).Value + Application.Workbooks(inputbook).Worksheets("КВР_111").Cells(i, j).Value
Next i
For i = 14 To 18
       Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, j).Value = Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111").Cells(i, j).Value + Application.Workbooks(inputbook).Worksheets("КВР_111").Cells(i, j).Value
Next i
Next j
End If
Call Ended(AutoCalculat)
End Sub
[/vba]

Автор - Мурад
Дата добавления - 30.11.2018 в 11:59
Мурад Дата: Пятница, 30.11.2018, 12:28 | Сообщение № 5
Группа: Проверенные
Ранг: Ветеран
Сообщений: 513
Репутация: 18 ±
Замечаний: 0% ±

Excel 2007
я имею в виду, как использовать конструкцию With ... End With, если открываем одновременно 2 книги? я начал было писать:
[vba]
Код
Sub КВР_111(inputbook As String)
Dim sh As Worksheet, ph As Worksheet
Set sh = Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111")
Set ph = Application.Workbooks(inputbook).Worksheets("КВР_111")
Dim AutoCalculat As Boolean: AutoCalculat = Prepare
With sh
    With ph
[/vba]
но потом задумался, как vba поймет, к ячейкам каких из 2 книг обращаться...
 
Ответить
Сообщениея имею в виду, как использовать конструкцию With ... End With, если открываем одновременно 2 книги? я начал было писать:
[vba]
Код
Sub КВР_111(inputbook As String)
Dim sh As Worksheet, ph As Worksheet
Set sh = Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111")
Set ph = Application.Workbooks(inputbook).Worksheets("КВР_111")
Dim AutoCalculat As Boolean: AutoCalculat = Prepare
With sh
    With ph
[/vba]
но потом задумался, как vba поймет, к ячейкам каких из 2 книг обращаться...

Автор - Мурад
Дата добавления - 30.11.2018 в 12:28
_Boroda_ Дата: Пятница, 30.11.2018, 12:49 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Примерно так можно
[vba]
Код
sh=Application.Workbooks(inputbook).Worksheets("КВР_111")
with Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111")
    For i = 11 To 12
         .Cells(i, 4).value=.Cells(i, 4).value + sh.Cells(i, 4).value
    next i
end with
[/vba]

* А With на текущий момент кода может быть только один. Конструкция with aaa: with bbb будет обрабатывать последний with (bbb который). Но как только напишете end with, то bbb закончится и станет обрабатываться aaa
[vba]
Код
With aaa
'работаем с aaa
    With bbb
    'работаем с bbb
    end with
'работаем с aaa
end with
[/vba]


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеПримерно так можно
[vba]
Код
sh=Application.Workbooks(inputbook).Worksheets("КВР_111")
with Application.Workbooks("Консолидация.xlsb").Worksheets("КВР_111")
    For i = 11 To 12
         .Cells(i, 4).value=.Cells(i, 4).value + sh.Cells(i, 4).value
    next i
end with
[/vba]

* А With на текущий момент кода может быть только один. Конструкция with aaa: with bbb будет обрабатывать последний with (bbb который). Но как только напишете end with, то bbb закончится и станет обрабатываться aaa
[vba]
Код
With aaa
'работаем с aaa
    With bbb
    'работаем с bbb
    end with
'работаем с aaa
end with
[/vba]

Автор - _Boroda_
Дата добавления - 30.11.2018 в 12:49
Мурад Дата: Пятница, 30.11.2018, 13:47 | Сообщение № 7
Группа: Проверенные
Ранг: Ветеран
Сообщений: 513
Репутация: 18 ±
Замечаний: 0% ±

Excel 2007
Спасибо, Александр. Без вас бы не разобрался! Воспользуюсь первым вашим примером
 
Ответить
СообщениеСпасибо, Александр. Без вас бы не разобрался! Воспользуюсь первым вашим примером

Автор - Мурад
Дата добавления - 30.11.2018 в 13:47
Мурад Дата: Пятница, 30.11.2018, 14:52 | Сообщение № 8
Группа: Проверенные
Ранг: Ветеран
Сообщений: 513
Репутация: 18 ±
Замечаний: 0% ±

Excel 2007
Последний момент. Отключил события экрана, как прописал boa в 3 посте. Но одно сообщение выскакивает в процессе пересчета
 
Ответить
СообщениеПоследний момент. Отключил события экрана, как прописал boa в 3 посте. Но одно сообщение выскакивает в процессе пересчета

Автор - Мурад
Дата добавления - 30.11.2018 в 14:52
Мурад Дата: Пятница, 30.11.2018, 15:37 | Сообщение № 9
Группа: Проверенные
Ранг: Ветеран
Сообщений: 513
Репутация: 18 ±
Замечаний: 0% ±

Excel 2007
Прилагаю последний вариант макроса, в котором отключал обновления экрана и учел замечания boa и Александра.
Как отключить это сообщение?
К сообщению приложен файл: _--______.xlsm (49.7 Kb)


Сообщение отредактировал Мурад - Пятница, 30.11.2018, 16:49
 
Ответить
СообщениеПрилагаю последний вариант макроса, в котором отключал обновления экрана и учел замечания boa и Александра.
Как отключить это сообщение?

Автор - Мурад
Дата добавления - 30.11.2018 в 15:37
boa Дата: Пятница, 30.11.2018, 17:45 | Сообщение № 10
Группа: Друзья
Ранг: Ветеран
Сообщений: 549
Репутация: 167 ±
Замечаний: 0% ±

365
Мурад,
это вам пишет открываемая книга
найдите эту книгу и разорвите в ней связь или установите "не обновлять при открытии"

нажмите кнпку "Изменить связи...", а потом
К сообщению приложен файл: 5746771.jpg (38.9 Kb)


 
Ответить
СообщениеМурад,
это вам пишет открываемая книга
найдите эту книгу и разорвите в ней связь или установите "не обновлять при открытии"

нажмите кнпку "Изменить связи...", а потом

Автор - boa
Дата добавления - 30.11.2018 в 17:45
boa Дата: Пятница, 30.11.2018, 17:58 | Сообщение № 11
Группа: Друзья
Ранг: Ветеран
Сообщений: 549
Репутация: 167 ±
Замечаний: 0% ±

365
Кстати, функции Prepare и Ended не надо вставлять в каждом модуле.
достаточно только в calc они же не приват


 
Ответить
СообщениеКстати, функции Prepare и Ended не надо вставлять в каждом модуле.
достаточно только в calc они же не приват

Автор - boa
Дата добавления - 30.11.2018 в 17:58
Мурад Дата: Пятница, 30.11.2018, 17:58 | Сообщение № 12
Группа: Проверенные
Ранг: Ветеран
Сообщений: 513
Репутация: 18 ±
Замечаний: 0% ±

Excel 2007
Понял, сделаю, спасибо! А разве отключение событий не должно запрещать книге обращаться по связям? Все-таки мы используем макросы, которые могут всё..
 
Ответить
СообщениеПонял, сделаю, спасибо! А разве отключение событий не должно запрещать книге обращаться по связям? Все-таки мы используем макросы, которые могут всё..

Автор - Мурад
Дата добавления - 30.11.2018 в 17:58
Мурад Дата: Пятница, 30.11.2018, 18:00 | Сообщение № 13
Группа: Проверенные
Ранг: Ветеран
Сообщений: 513
Репутация: 18 ±
Замечаний: 0% ±

Excel 2007
Кстати, функции Prepare и Ended не надо вставлять в каждом модуле.
достаточно только в calc они же не приват

:blink: и правда
 
Ответить
Сообщение
Кстати, функции Prepare и Ended не надо вставлять в каждом модуле.
достаточно только в calc они же не приват

:blink: и правда

Автор - Мурад
Дата добавления - 30.11.2018 в 18:00
boa Дата: Пятница, 30.11.2018, 18:05 | Сообщение № 14
Группа: Друзья
Ранг: Ветеран
Сообщений: 549
Репутация: 167 ±
Замечаний: 0% ±

365
А разве отключение событий не должно запрещать книге обращаться по связям?


нет,
но можно открывать файл запретив обновление связей
[vba]
Код
Excel.Application.Workbooks.Open Filename:=sPath, UpdateLinks:=False
[/vba]


 
Ответить
Сообщение
А разве отключение событий не должно запрещать книге обращаться по связям?


нет,
но можно открывать файл запретив обновление связей
[vba]
Код
Excel.Application.Workbooks.Open Filename:=sPath, UpdateLinks:=False
[/vba]

Автор - boa
Дата добавления - 30.11.2018 в 18:05
Мурад Дата: Пятница, 30.11.2018, 18:10 | Сообщение № 15
Группа: Проверенные
Ранг: Ветеран
Сообщений: 513
Репутация: 18 ±
Замечаний: 0% ±

Excel 2007
Насчет того, чтобы заходить в каждую книгу и отключать связи, дабы не выскакивало сообщение, как в посте №8.
Вот надстройка, которая открывает эти же самые книги, не ругаясь на связи. Узнаем, как она это делает и наш макрос будет работать также без ошибок :)
https://psv4.userapi.com/c834603....oj&dl=1
 
Ответить
СообщениеНасчет того, чтобы заходить в каждую книгу и отключать связи, дабы не выскакивало сообщение, как в посте №8.
Вот надстройка, которая открывает эти же самые книги, не ругаясь на связи. Узнаем, как она это делает и наш макрос будет работать также без ошибок :)
https://psv4.userapi.com/c834603....oj&dl=1

Автор - Мурад
Дата добавления - 30.11.2018 в 18:10
Мурад Дата: Пятница, 30.11.2018, 18:13 | Сообщение № 16
Группа: Проверенные
Ранг: Ветеран
Сообщений: 513
Репутация: 18 ±
Замечаний: 0% ±

Excel 2007
нет,
но можно открывать файл запретив обновление связей
Excel.Application.Workbooks.Open Filename:=sPath, UpdateLinks:=False

Мне вставить этот кусок к функции Prepare или в каждый модуль, где идет обращение к файлу?
 
Ответить
Сообщение
нет,
но можно открывать файл запретив обновление связей
Excel.Application.Workbooks.Open Filename:=sPath, UpdateLinks:=False

Мне вставить этот кусок к функции Prepare или в каждый модуль, где идет обращение к файлу?

Автор - Мурад
Дата добавления - 30.11.2018 в 18:13
boa Дата: Пятница, 30.11.2018, 19:41 | Сообщение № 17
Группа: Друзья
Ранг: Ветеран
Сообщений: 549
Репутация: 167 ±
Замечаний: 0% ±

365
Мурад,
Туда, где идет открытие файла (Workbooks.Open), в модуле формы, если не ошибаюсь.




Сообщение отредактировал boa - Суббота, 01.12.2018, 15:51
 
Ответить
СообщениеМурад,
Туда, где идет открытие файла (Workbooks.Open), в модуле формы, если не ошибаюсь.

Автор - boa
Дата добавления - 30.11.2018 в 19:41
Мир MS Excel » Вопросы и решения » Вопросы по VBA » консолидация данных с суммированием для всех файлов в папке (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!