Доброго времени суток! Возникла необходимость в автоматизации одновременного обновления нескольких (до 40 шт) сводных таблиц. Что делается сейчас: 1. Отрывается файл i. 2. Обновляется. 3. Сохраняется. 4. Отсылается i списку пользователей. И так до 40 раз.
Первое, что пришло в голову: последовательно открывать, обновлять и закрывать. Написал Макрос_2010(). Вывод: тратится огромное количество времени. Второе: попробовал написать макрос на параллельное выполнение данной задачи - Sub macro() Вывод: при подключении 3-го файла Excel "виснет" В VBA я чайник - что-то делаю не так. Посоветуйте пожалуйста, что необходимо добавить/заменить/изменить. Заранее благодарен
Доброго времени суток! Возникла необходимость в автоматизации одновременного обновления нескольких (до 40 шт) сводных таблиц. Что делается сейчас: 1. Отрывается файл i. 2. Обновляется. 3. Сохраняется. 4. Отсылается i списку пользователей. И так до 40 раз.
Первое, что пришло в голову: последовательно открывать, обновлять и закрывать. Написал Макрос_2010(). Вывод: тратится огромное количество времени. Второе: попробовал написать макрос на параллельное выполнение данной задачи - Sub macro() Вывод: при подключении 3-го файла Excel "виснет" В VBA я чайник - что-то делаю не так. Посоветуйте пожалуйста, что необходимо добавить/заменить/изменить. Заранее благодаренmkotik
Ну как бы в любом случае обновление связей занимает много времени. И никаким макросом с этим ничего не поделаешь. Единственно что я бы предложил открывать файлы в цикле и независимо от их расположения: [vba]
Код
Sub Макрос_2010()
Dim i As Integer
MsgBox prompt:="Откройте обновляемые файлы!" With Application.FileDialog(msoFileDialogOpen) .AllowMultiSelect = True .Show For i = 1 To .SelectedItems.Count If InStr(1, .SelectedItems(i), ".xls", vbTextCompare) <> 0 Then Workbooks.Open fikename:=.SelectedItems(i) ActiveWorkbook.RefreshAll ActiveWorkbook.Save ActiveWindow.Close End If Next i End With
Ну как бы в любом случае обновление связей занимает много времени. И никаким макросом с этим ничего не поделаешь. Единственно что я бы предложил открывать файлы в цикле и независимо от их расположения: [vba]
Код
Sub Макрос_2010()
Dim i As Integer
MsgBox prompt:="Откройте обновляемые файлы!" With Application.FileDialog(msoFileDialogOpen) .AllowMultiSelect = True .Show For i = 1 To .SelectedItems.Count If InStr(1, .SelectedItems(i), ".xls", vbTextCompare) <> 0 Then Workbooks.Open fikename:=.SelectedItems(i) ActiveWorkbook.RefreshAll ActiveWorkbook.Save ActiveWindow.Close End If Next i End With
А вообще насколько я понял, у Вас файлы находятся на сервере. И тут уже я бы смотрел в сторону связей — если они тоже на сервере, это одно. А если на Вашем компе, то перед обновлением имеет смысл файлы скопировать к себе — пинг до сервера процесса не ускоряет...
А вообще насколько я понял, у Вас файлы находятся на сервере. И тут уже я бы смотрел в сторону связей — если они тоже на сервере, это одно. А если на Вашем компе, то перед обновлением имеет смысл файлы скопировать к себе — пинг до сервера процесса не ускоряет...StoTisteg
Интуитивно понятный код - это когда интуитивно понятно, что это код.
В Excel есть такая категория "Сводная таблица" (вкладка Вставка - Сводная таблица). Что Вы имеете ввиду под сводной? Эту категорию или просто у Вас есть excel файл, который вы между сотрудниками называете "сводная таблица"?
В Excel есть такая категория "Сводная таблица" (вкладка Вставка - Сводная таблица). Что Вы имеете ввиду под сводной? Эту категорию или просто у Вас есть excel файл, который вы между сотрудниками называете "сводная таблица"?Karataev
Karataev, я конечно прошу "пардона" за мое неправильное ТЗ или слабое знание VBA... Я не знаю, у кого какие сотрудники что и как называют, но в ТЗ подразумеровались именно Сводные таблицы от Excel. В моем случае данные для сводных таблиц получаются из кубов. С уважением, Игорь
Karataev, я конечно прошу "пардона" за мое неправильное ТЗ или слабое знание VBA... Я не знаю, у кого какие сотрудники что и как называют, но в ТЗ подразумеровались именно Сводные таблицы от Excel. В моем случае данные для сводных таблиц получаются из кубов. С уважением, Игорьmkotik
Просто в качестве предположения - а если все эти 40 сводных таблиц засунуть в один файл, который и обновлять при необходимости. А данные из этого файла потом раскидывать макросом на нужные файлы.
Просто в качестве предположения - а если все эти 40 сводных таблиц засунуть в один файл, который и обновлять при необходимости. А данные из этого файла потом раскидывать макросом на нужные файлы._Boroda_
_Boroda_, спасибо за совет! К сожалению не получится: уже так пробовал! Каждый из 40 файлов содержит в среднем по 20 листов, на каждом листе в среднем по три сводные таблицы, а на первых трёх листах в файле итоги. Самое интересное: ручками получается в течение 1,5 часов сделать эту работу. Открываешь 40 сессий Excel - Ctrl+Alt+F5 в каждой из них и все хорошо. Хотелось автоматизировать этот процесс :( Сорри за флуд :( С уважением, Игорь
_Boroda_, спасибо за совет! К сожалению не получится: уже так пробовал! Каждый из 40 файлов содержит в среднем по 20 листов, на каждом листе в среднем по три сводные таблицы, а на первых трёх листах в файле итоги. Самое интересное: ручками получается в течение 1,5 часов сделать эту работу. Открываешь 40 сессий Excel - Ctrl+Alt+F5 в каждой из них и все хорошо. Хотелось автоматизировать этот процесс :( Сорри за флуд :( С уважением, Игорьmkotik
Сообщение отредактировал mkotik - Воскресенье, 27.03.2016, 23:30
mkotik, Помимо обновления, время еще тратится на открытие(закрытие) файла и его сохранение. Может в каждую из книг добавить обновление при сохранении книги? Или перед закрытием: [vba]
Код
Private Sub Workbook_BeforeClose(Cancel As Boolean) ThisWorkbook.RefreshAll End Sub
[/vba] Тогда вообще не понадобится перебирать все эти файлы.
mkotik, Помимо обновления, время еще тратится на открытие(закрытие) файла и его сохранение. Может в каждую из книг добавить обновление при сохранении книги? Или перед закрытием: [vba]
Код
Private Sub Workbook_BeforeClose(Cancel As Boolean) ThisWorkbook.RefreshAll End Sub
[/vba] Тогда вообще не понадобится перебирать все эти файлы.Manyasha
Доброе время суток Можно воспользоваться vbscript - он позволяет запускать асинхронно копии Excel и в каждой копии открывать и обновлять файл. Архив распаковать в папку c:\path, в файле files.txt прописать по образцу пути файлов для обновления. Запустить RefreshPivotTables.vbs на выполнение. Так как работа асинхронна, то никаких сообщений о завершении выполнения не делал, так что можно будет судить о завершении по закрытию всех Excel.exe в диспетчере задач <_< Успехов.
Доброе время суток Можно воспользоваться vbscript - он позволяет запускать асинхронно копии Excel и в каждой копии открывать и обновлять файл. Архив распаковать в папку c:\path, в файле files.txt прописать по образцу пути файлов для обновления. Запустить RefreshPivotTables.vbs на выполнение. Так как работа асинхронна, то никаких сообщений о завершении выполнения не делал, так что можно будет судить о завершении по закрытию всех Excel.exe в диспетчере задач <_< Успехов.anvg
Доброе время суток Можно воспользоваться vbscript - он позволяет запускать асинхронно копии Excel и в каждой копии открывать и обновлять файл. Архив распаковать в папку c:\path, в файле files.txt прописать по образцу пути файлов для обновления. Запустить RefreshPivotTables.vbs на выполнение. Так как работа асинхронна, то никаких сообщений о завершении выполнения не делал, так что можно будет судить о завершении по закрытию всех Excel.exe в диспетчере задач <_< Успехов.
И так как файлов 40 или больше то придется сделать проверку на колво запущенных процессов excel в скрипте RefreshPivotTables.vbs в цикле перебора файлов иначе быстро все оперативка уйдет да и если не уйдет то будет работать явно дольше чем 1,5 часа. А потоков желательно запускать равное колву ядер на машине. [moder]Еще одно нарушение Правил - начну зверствовать. Исправляйте оба косяка в двух своих ответах.
Доброе время суток Можно воспользоваться vbscript - он позволяет запускать асинхронно копии Excel и в каждой копии открывать и обновлять файл. Архив распаковать в папку c:\path, в файле files.txt прописать по образцу пути файлов для обновления. Запустить RefreshPivotTables.vbs на выполнение. Так как работа асинхронна, то никаких сообщений о завершении выполнения не делал, так что можно будет судить о завершении по закрытию всех Excel.exe в диспетчере задач <_< Успехов.
И так как файлов 40 или больше то придется сделать проверку на колво запущенных процессов excel в скрипте RefreshPivotTables.vbs в цикле перебора файлов иначе быстро все оперативка уйдет да и если не уйдет то будет работать явно дольше чем 1,5 часа. А потоков желательно запускать равное колву ядер на машине. [moder]Еще одно нарушение Правил - начну зверствовать. Исправляйте оба косяка в двух своих ответах.zopa
А потоков желательно запускать равное колву ядер на машине
Так вам кто-то мешает это осуществить? Разбиваете входной список книг на список книг кратно числу процессоров и сохраняете во временные файлы. Запускаете execute.vbs с очередным временным файлом, при выполнении создав один экземпляр Excel, обновляющий книги этого списка. По завершении - закрываете Excel и удаляете временный файл. В RefreshPivotTables дописываете бесконечный цикл, пока все временные файлы не будут удалены - получите возможность сообщить о завершении процесса обновления Успехов, коллега.
Цитата
А потоков желательно запускать равное колву ядер на машине
Так вам кто-то мешает это осуществить? Разбиваете входной список книг на список книг кратно числу процессоров и сохраняете во временные файлы. Запускаете execute.vbs с очередным временным файлом, при выполнении создав один экземпляр Excel, обновляющий книги этого списка. По завершении - закрываете Excel и удаляете временный файл. В RefreshPivotTables дописываете бесконечный цикл, пока все временные файлы не будут удалены - получите возможность сообщить о завершении процесса обновления Успехов, коллега.anvg
anvg, Спасибо за совет - попробовал - "насмерть" зависла система - пришлось Task Manager-ом сносить :) На текущий момент пробую следующую процедуру: [vba]
Код
Sub RefreshAll() RefreshFile "\\fileserv0\ArchiveDocuments$\1.xlsx" RefreshFile "\\fileserv0\ArchiveDocuments$\2.xlsx" RefreshFile "\\fileserv0\ArchiveDocuments$\3.xlsx" End Sub
Sub RefreshFile(fullFileName As String) Dim wb As Workbook Set wb = Workbooks.Open(filename:=fullFileName)
For Each conn In wb.Connections originalBackgroundQuery = conn.OLEDBConnection.BackgroundQuery conn.OLEDBConnection.BackgroundQuery = False conn.Refresh conn.OLEDBConnection.BackgroundQuery = originalBackgroundQuery Next
wb.Save wb.Close End Sub
[/vba]
Выдает ошибку: Invalid procedure call or argument в строке: originalBackgroundQuery = conn.OLEDBConnection.BackgroundQuery
anvg, Спасибо за совет - попробовал - "насмерть" зависла система - пришлось Task Manager-ом сносить :) На текущий момент пробую следующую процедуру: [vba]
Код
Sub RefreshAll() RefreshFile "\\fileserv0\ArchiveDocuments$\1.xlsx" RefreshFile "\\fileserv0\ArchiveDocuments$\2.xlsx" RefreshFile "\\fileserv0\ArchiveDocuments$\3.xlsx" End Sub
Sub RefreshFile(fullFileName As String) Dim wb As Workbook Set wb = Workbooks.Open(filename:=fullFileName)
For Each conn In wb.Connections originalBackgroundQuery = conn.OLEDBConnection.BackgroundQuery conn.OLEDBConnection.BackgroundQuery = False conn.Refresh conn.OLEDBConnection.BackgroundQuery = originalBackgroundQuery Next
wb.Save wb.Close End Sub
[/vba]
Выдает ошибку: Invalid procedure call or argument в строке: originalBackgroundQuery = conn.OLEDBConnection.BackgroundQuerymkotik
Сообщение отредактировал mkotik - Среда, 30.03.2016, 10:20