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

Вход

Регистрация

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

 

= Мир MS Excel/Обновление сводных таблиц в разных сессиях Excel - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Обновление сводных таблиц в разных сессиях Excel (Макросы/Sub)
Обновление сводных таблиц в разных сессиях Excel
mkotik Дата: Воскресенье, 27.03.2016, 00:56 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 21
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
Доброго времени суток!
Возникла необходимость в автоматизации одновременного обновления нескольких (до 40 шт) сводных таблиц.
Что делается сейчас:
1. Отрывается файл i.
2. Обновляется.
3. Сохраняется.
4. Отсылается i списку пользователей.
И так до 40 раз.

Первое, что пришло в голову: последовательно открывать, обновлять и закрывать. Написал Макрос_2010(). Вывод: тратится огромное количество времени.
Второе: попробовал написать макрос на параллельное выполнение данной задачи - Sub macro() Вывод: при подключении 3-го файла Excel "виснет"
В VBA я чайник - что-то делаю не так.
Посоветуйте пожалуйста, что необходимо добавить/заменить/изменить.
Заранее благодарен
К сообщению приложен файл: 2__2010.xlsm(15Kb)
 
Ответить
СообщениеДоброго времени суток!
Возникла необходимость в автоматизации одновременного обновления нескольких (до 40 шт) сводных таблиц.
Что делается сейчас:
1. Отрывается файл i.
2. Обновляется.
3. Сохраняется.
4. Отсылается i списку пользователей.
И так до 40 раз.

Первое, что пришло в голову: последовательно открывать, обновлять и закрывать. Написал Макрос_2010(). Вывод: тратится огромное количество времени.
Второе: попробовал написать макрос на параллельное выполнение данной задачи - Sub macro() Вывод: при подключении 3-го файла Excel "виснет"
В VBA я чайник - что-то делаю не так.
Посоветуйте пожалуйста, что необходимо добавить/заменить/изменить.
Заранее благодарен

Автор - mkotik
Дата добавления - 27.03.2016 в 00:56
StoTisteg Дата: Воскресенье, 27.03.2016, 13:14 | Сообщение № 2
Группа: Авторы
Ранг: Ветеран
Сообщений: 541
Репутация: 45 ±
Замечаний: 0% ±

Excel 2010
Вывод: тратится огромное количество времени

Ну как бы в любом случае обновление связей занимает много времени. И никаким макросом с этим ничего не поделаешь. Единственно что я бы предложил открывать файлы в цикле и независимо от их расположения:
[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
    
End Sub
[/vba]


Проверь всё. ThisWorkbook.Save. On Error Resume Next.
 
Ответить
Сообщение
Вывод: тратится огромное количество времени

Ну как бы в любом случае обновление связей занимает много времени. И никаким макросом с этим ничего не поделаешь. Единственно что я бы предложил открывать файлы в цикле и независимо от их расположения:
[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
    
End Sub
[/vba]

Автор - StoTisteg
Дата добавления - 27.03.2016 в 13:14
StoTisteg Дата: Воскресенье, 27.03.2016, 13:25 | Сообщение № 3
Группа: Авторы
Ранг: Ветеран
Сообщений: 541
Репутация: 45 ±
Замечаний: 0% ±

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


Проверь всё. ThisWorkbook.Save. On Error Resume Next.
 
Ответить
СообщениеА вообще насколько я понял, у Вас файлы находятся на сервере. И тут уже я бы смотрел в сторону связей — если они тоже на сервере, это одно. А если на Вашем компе, то перед обновлением имеет смысл файлы скопировать к себе — пинг до сервера процесса не ускоряет...

Автор - StoTisteg
Дата добавления - 27.03.2016 в 13:25
Karataev Дата: Воскресенье, 27.03.2016, 21:03 | Сообщение № 4
Группа: Проверенные
Ранг: Ветеран
Сообщений: 726
Репутация: 260 ±
Замечаний: 0% ±

Excel
В Excel есть такая категория "Сводная таблица" (вкладка Вставка - Сводная таблица). Что Вы имеете ввиду под сводной? Эту категорию или просто у Вас есть excel файл, который вы между сотрудниками называете "сводная таблица"?


 
Ответить
СообщениеВ Excel есть такая категория "Сводная таблица" (вкладка Вставка - Сводная таблица). Что Вы имеете ввиду под сводной? Эту категорию или просто у Вас есть excel файл, который вы между сотрудниками называете "сводная таблица"?

Автор - Karataev
Дата добавления - 27.03.2016 в 21:03
mkotik Дата: Воскресенье, 27.03.2016, 21:32 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 21
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
Karataev, я конечно прошу "пардона" за мое неправильное ТЗ или слабое знание VBA... Я не знаю, у кого какие сотрудники что и как называют, но в ТЗ подразумеровались именно Сводные таблицы от Excel. В моем случае данные для сводных таблиц получаются из кубов.
С уважением, Игорь
 
Ответить
СообщениеKarataev, я конечно прошу "пардона" за мое неправильное ТЗ или слабое знание VBA... Я не знаю, у кого какие сотрудники что и как называют, но в ТЗ подразумеровались именно Сводные таблицы от Excel. В моем случае данные для сводных таблиц получаются из кубов.
С уважением, Игорь

Автор - mkotik
Дата добавления - 27.03.2016 в 21:32
Karataev Дата: Воскресенье, 27.03.2016, 21:47 | Сообщение № 6
Группа: Проверенные
Ранг: Ветеран
Сообщений: 726
Репутация: 260 ±
Замечаний: 0% ±

Excel
mkotik, а без макроса, когда Вы вручную обновляете, быстрее происходит обновление?


 
Ответить
Сообщениеmkotik, а без макроса, когда Вы вручную обновляете, быстрее происходит обновление?

Автор - Karataev
Дата добавления - 27.03.2016 в 21:47
_Boroda_ Дата: Воскресенье, 27.03.2016, 22:48 | Сообщение № 7
Группа: Модераторы
Ранг: Экселист
Сообщений: 9818
Репутация: 4147 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Просто в качестве предположения - а если все эти 40 сводных таблиц засунуть в один файл, который и обновлять при необходимости. А данные из этого файла потом раскидывать макросом на нужные файлы.


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеПросто в качестве предположения - а если все эти 40 сводных таблиц засунуть в один файл, который и обновлять при необходимости. А данные из этого файла потом раскидывать макросом на нужные файлы.

Автор - _Boroda_
Дата добавления - 27.03.2016 в 22:48
mkotik Дата: Воскресенье, 27.03.2016, 22:58 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 21
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
Karataev, на текущий момент ручками проще, но время очень дорого 1,5 часа :(
С уважением, Игорь


Сообщение отредактировал mkotik - Воскресенье, 27.03.2016, 23:29
 
Ответить
СообщениеKarataev, на текущий момент ручками проще, но время очень дорого 1,5 часа :(
С уважением, Игорь

Автор - mkotik
Дата добавления - 27.03.2016 в 22:58
mkotik Дата: Воскресенье, 27.03.2016, 23:09 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 21
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
_Boroda_, спасибо за совет!
К сожалению не получится: уже так пробовал!
Каждый из 40 файлов содержит в среднем по 20 листов, на каждом листе в среднем по три сводные таблицы, а на первых трёх листах в файле итоги.
Самое интересное: ручками получается в течение 1,5 часов сделать эту работу.
Открываешь 40 сессий Excel - Ctrl+Alt+F5 в каждой из них и все хорошо.
Хотелось автоматизировать этот процесс :(
Сорри за флуд :(
С уважением, Игорь


Сообщение отредактировал mkotik - Воскресенье, 27.03.2016, 23:30
 
Ответить
Сообщение_Boroda_, спасибо за совет!
К сожалению не получится: уже так пробовал!
Каждый из 40 файлов содержит в среднем по 20 листов, на каждом листе в среднем по три сводные таблицы, а на первых трёх листах в файле итоги.
Самое интересное: ручками получается в течение 1,5 часов сделать эту работу.
Открываешь 40 сессий Excel - Ctrl+Alt+F5 в каждой из них и все хорошо.
Хотелось автоматизировать этот процесс :(
Сорри за флуд :(
С уважением, Игорь

Автор - mkotik
Дата добавления - 27.03.2016 в 23:09
Manyasha Дата: Понедельник, 28.03.2016, 11:25 | Сообщение № 10
Группа: Модераторы
Ранг: Старожил
Сообщений: 1721
Репутация: 722 ±
Замечаний: 0% ±

Excel 2007, 2010
mkotik, Помимо обновления, время еще тратится на открытие(закрытие) файла и его сохранение.
Может в каждую из книг добавить обновление при сохранении книги? Или перед закрытием:
[vba]
Код
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.RefreshAll
End Sub
[/vba]
Тогда вообще не понадобится перебирать все эти файлы.


marinamorozova_box@mail.ru
ЯД: 410013299366744 WM: R193491431804
 
Ответить
Сообщениеmkotik, Помимо обновления, время еще тратится на открытие(закрытие) файла и его сохранение.
Может в каждую из книг добавить обновление при сохранении книги? Или перед закрытием:
[vba]
Код
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.RefreshAll
End Sub
[/vba]
Тогда вообще не понадобится перебирать все эти файлы.

Автор - Manyasha
Дата добавления - 28.03.2016 в 11:25
anvg Дата: Понедельник, 28.03.2016, 20:39 | Сообщение № 11
Группа: Друзья
Ранг: Обитатель
Сообщений: 287
Репутация: 153 ±
Замечаний: 0% ±

2010, 2016
Доброе время суток
Можно воспользоваться vbscript - он позволяет запускать асинхронно копии Excel и в каждой копии открывать и обновлять файл.
Архив распаковать в папку c:\path, в файле files.txt прописать по образцу пути файлов для обновления. Запустить RefreshPivotTables.vbs на выполнение. Так как работа асинхронна, то никаких сообщений о завершении выполнения не делал, так что можно будет судить о завершении по закрытию всех Excel.exe в диспетчере задач <_<
Успехов.
К сообщению приложен файл: Path.zip(1Kb)


Сообщение отредактировал anvg - Понедельник, 28.03.2016, 20:39
 
Ответить
СообщениеДоброе время суток
Можно воспользоваться vbscript - он позволяет запускать асинхронно копии Excel и в каждой копии открывать и обновлять файл.
Архив распаковать в папку c:\path, в файле files.txt прописать по образцу пути файлов для обновления. Запустить RefreshPivotTables.vbs на выполнение. Так как работа асинхронна, то никаких сообщений о завершении выполнения не делал, так что можно будет судить о завершении по закрытию всех Excel.exe в диспетчере задач <_<
Успехов.

Автор - anvg
Дата добавления - 28.03.2016 в 20:39
zopa Дата: Вторник, 29.03.2016, 14:48 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 100% ±

Excel 2010
Доброе время суток
Можно воспользоваться 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
Дата добавления - 29.03.2016 в 14:48
anvg Дата: Вторник, 29.03.2016, 21:22 | Сообщение № 13
Группа: Друзья
Ранг: Обитатель
Сообщений: 287
Репутация: 153 ±
Замечаний: 0% ±

2010, 2016
Цитата
А потоков желательно запускать равное колву ядер на машине
Так вам кто-то мешает это осуществить? Разбиваете входной список книг на список книг кратно числу процессоров и сохраняете во временные файлы. Запускаете execute.vbs с очередным временным файлом, при выполнении создав один экземпляр Excel, обновляющий книги этого списка.
По завершении - закрываете Excel и удаляете временный файл. В RefreshPivotTables дописываете бесконечный цикл, пока все временные файлы не будут удалены - получите возможность сообщить о завершении процесса обновления ;)
Успехов, коллега.
 
Ответить
Сообщение
Цитата
А потоков желательно запускать равное колву ядер на машине
Так вам кто-то мешает это осуществить? Разбиваете входной список книг на список книг кратно числу процессоров и сохраняете во временные файлы. Запускаете execute.vbs с очередным временным файлом, при выполнении создав один экземпляр Excel, обновляющий книги этого списка.
По завершении - закрываете Excel и удаляете временный файл. В RefreshPivotTables дописываете бесконечный цикл, пока все временные файлы не будут удалены - получите возможность сообщить о завершении процесса обновления ;)
Успехов, коллега.

Автор - anvg
Дата добавления - 29.03.2016 в 21:22
mkotik Дата: Среда, 30.03.2016, 10:19 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 21
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
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


Сообщение отредактировал mkotik - Среда, 30.03.2016, 10:20
 
Ответить
Сообщение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

Автор - mkotik
Дата добавления - 30.03.2016 в 10:19
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Обновление сводных таблиц в разных сессиях Excel (Макросы/Sub)
Страница 1 из 11
Поиск:

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