Добрый день. Формирую отчеты макросами в книге эксель подключенной к кубам. Иногда возникает подвисание экселя(процесс выполняется слишком долго) именно на строчках обновления таблиц через кубы(плохой интернет, слишком большой отчет или что-то еще...). Повторное обновление сводной таблицы через несколько минут вполне спасает ситуацию.
Код
'строчка кода обновления сводной таблицы, где возможны подвисания
ActiveWorkbook.Connections("Tabular Модель1").Refresh
Подвисание обновления таблицы не зависает намертво - макрос обновления можно остановить простым нажатием Esc. Но если этого не сделать, он так и будет обновлять и обновлять довольно долго..... Поэтому важно сделать обработчик "зависаний", который, в случае чего, будет перезапустит невыполненную часть макроса заново для удачного исполнения.
Sub обработчик_зависаний_макроса()
'если зависнет - попытку выполнения повторить 2 раза.
For i = 1 To 2
'время, которым ограничено выполнение части скрипта в цикле
maxtime_for_macro = Now() + TimeValue("00:00:10")
a = Timer 'таймер для контроля работы обработчика зависаний макроса
DoEvents 'Передает управление Windows
Do While Now <= maxtime_for_macro 'нужно выполнять только разрешенное время - 10 сек.
Application.Wait Now() + TimeValue("00:00:15") 'пример процесса, который выполняется слишком долго (больше 10 сек.)
MsgBox Timer - a & "работает, если выполнение около 10 секунд"
Loop
if maxtime_for_macro - Now < 10 Then exit For 'если выполняется меньше чем за 10 сек - повтор цикла не нужен
Next i
End Sub
zavex, На мой непросвещённый взгляд идея в таком виде провальная. Если код завис, то он сам себя уже не сможет контролировать. Но возникает вопрос как именно идет обновление данных?
то есть обновление фоновое и оно длится долго. Цикла бесконечного при этом нет, и не совсем понятно как абортить выполнение обновления , который для вас как черная коробочка.
StoTisteg, процесс выполняется, но очень медленно. И эксель никак не реагирует. А если обновить таблицу повторно, через немного времени - отчет формируется отлично. Поэтому и нужен обработчик написать, что бы в случае слишком долгого обновления отчета, нужные строчки макроса перезапустились повторно.
БМВ, абортить по времени будет достаточно. Если обновление сводной таблицы занимает больше 15 минут (обычно хватает 3-5 мин) - тогда абортить и повторить обновление.
тогда уж надо ставить флаг начала процесса и снимать его по событию успешного обновления. Проверять что если флаг висит дольше нужного, то повторять процесс. вот как отловить событие успешного обновления - трудно сказать не видя примера. Например MSQuery обновление меня ZVI научил держать на контроле
Цитата
Так будет надежнее запускаться при первой загрузке в Excel:
Код
Dim WithEvents q As QueryTable
Private Sub q_AfterRefresh(ByVal Success As Boolean)
Debug.Print "AfterRefresh", Now
End Sub
Private Sub q_BeforeRefresh(Cancel As Boolean)
Debug.Print "BeforeRefresh", Now
End Sub
Private Sub Workbook_Open()
Application.OnTime Now, Me.Name & ".Start"
End Sub
Sub Start()
Set q = Sheet2.QueryTables(1)
End Sub
К слову, что процесс обновления таблицы не зависает намертво - макрос обновления можно остановить простым нажатием Esc. Но если этого не сделать, он так и будет обновлять и обновлять безрезультатно...
БМВ, незнакомая для меня конструкция с флагом. Буду пробовать. Но отладка в ручную точно не подойдет т.к. в макросе запускается сразу формирование нескольких отчетов. Тогда лучше, что бы хоть просто прерывалось обновление сводной таблицы и ифом отмечалось, какой отчет не обновился.
БМВ написал: вот как отловить событие успешного обновления
Проверять изменения на листе который должен обновиться в ячейке которая обязательно измениться. Или заведомо, перед запуском обновления, изменить некоторую ячейку на свой текст. Если обновление это фоновый процесс, и сообщения могут достучаться до Excel, я бы проверял по таймеру. См. вложение
короче, в powershell нарисовался скрипт, который запускается планировщиком и проверяет, если процесс excel висит дольше 15 мин - убивает процесс и перезапускает документ excell заново. ну а по открытию в документе запускается заново макрос на выполнение. Работает, но хотелось бы более гуманного решения в самом экселе. Т.к. taskkill - не лучший способ прерывания, для перезапуска макроса.
zavex, Для готового решения, нужен пример. Все ж в данном случае #8+#14 могут дать результат, Модель то симулятор написать не проблема, а вот что там за событие будет или как заменить данные чтоб по ним судить что обновление выполнено можно только на близком к оригиналу примере.
Алгоритмически все просто. 1. Установка старта процедуры проверки через 15 мин 2. Установка флага старта (глобальная переменная или ячейка листа - на ваше усмотрение) Если запросов несколько , то несколько флагов 3. запуск обновления-обновлений. 4. По событию успешного обновления снимаем флаг 5. через 15 мин стартует проверка 6. если флаг установлен, значит перезапуск идем на 1. 7. Все обновилось
если возможен #14 , то п. 1 - заменяем на "порчу данных", 4. не нужен, данные исправятся, значит флаг снят.
zavex написал: короче, в powershell нарисовался скрипт, который запускается планировщиком и проверяет, если процесс excel висит дольше 15 мин - убивает процесс и перезапускает документ excell заново.
процесс excel, при проверке из сторонней программы, может выдать сигнал занято по многим причинам. Например обновление давно прошло и пользователь вошел в режим редактирования ячейки.
Смотрите в сторону #16. БМВ Вам дал готовый алгоритм
Здравствуйте, коллеги! Для чего сложный путь (#16), если есть простой (#8 ), применимый к объектам QueryTable всех типов (в том числе, из #18). Те действия, которые нужно выполнить по завершению обновления QueryTable, вставляйте в q_AfterRefresh.
Владимир, приветствую. №8 применим для тригера/флага, но весь сыр бор то в том, что если запрос так и не закончился, то его надо прервать, и это (со слов автора) успешно делается повторным запуском. Событие завершения запроса помогает, но не освобождает от прочей аналитики по истечении заветных 15 мин.
Понятно. Я не знаю способа, как можно макросом прервать выполняющийся запрос обновления данных. Грубая сила: закрыть книгу и открыть заново (через Application.Ontime).
Всем спасибо большое!!!! С помощью VBA решение так и не нашел. Писал на powershell Алгоритмически работы следущий: Запуск скрипта1 powershell. Если через 15 мин процесс Excell висит - taskill Excell. Перезапуск документа с последующим перезапуском обновления сводной таблицы Обновление сводной Остальной код макроса Запуск скрипта2 powershell. Находит другие процессы powershell. taskill всех процессов powershell(в том числе убивает таймер на выключения экселя. эксель работает дальше)
У кого возникнет аналогичная проблема рекомендую разбить большую сводную таблицу на несколько маленьких (по 3-4-5 столбцов). И уже после собирать полученные данные в одну таблицу (копирование, впр. Все грузить в массив и только после выгружать в нужную таблицу). Лично у меня скорость формирования отчетов возросла, эксель вроде как не подвисает в конечном счете.