Страницы: 1
RSS
Обработчик зависаний макроса (контроль выполнения макроса по времени)
 
Добрый день. Формирую отчеты макросами в книге эксель подключенной к кубам. Иногда возникает подвисание экселя(процесс выполняется слишком долго) именно на строчках обновления таблиц через кубы(плохой интернет, слишком большой отчет или что-то еще...).  Повторное обновление сводной таблицы через несколько минут вполне спасает ситуацию.
Код
'строчка кода обновления сводной таблицы, где возможны подвисания
ActiveWorkbook.Connections("Tabular Модель1").Refresh
Подвисание обновления таблицы не зависает намертво - макрос обновления можно остановить простым нажатием Esc. Но если этого не сделать, он так и будет обновлять и обновлять довольно долго.....
Поэтому важно сделать обработчик "зависаний", который, в случае чего, будет перезапустит невыполненную часть макроса заново для удачного исполнения.

Пример отчета: https://drive.google.com/uc?authuser=0&id=1SQB5kRL6BU6-rzQBRPZw2CvE8uVEjxfS&export=download
Прошу помочь решить задачку. Подскажите, как исправить код или другое действенное решение
Мой нерабочий черновик кода:
Код
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 - 06.07.2018 19:49:20
 
zavex,  На мой непросвещённый взгляд идея в таком виде провальная. Если код завис, то он сам себя уже не сможет контролировать. Но возникает вопрос как именно идет обновление данных?
По вопросам из тем форума, личку не читаю.
 
Процесс не зависает, он скорее переходит в бесконечный цикл...

строчка обновления данных сводной таблицы подключенной к кубам. Именно на ней может произойти так званое зависание(слишком долгое выполнение)
Код
'обновить подключение к сводной таблице
ActiveWorkbook.Connections("Tabular Модель1").Refresh

контролировать остальную часть кода помогает команда
DoEvents 'Передает управление Windows, должна дать возможности остановки цикла, если зависнет.  
Изменено: zavex - 31.05.2018 17:57:14
 
zavex, так ведь если оно таки зависло, то ой всё, никто никому ничего не передаёт...
 
то есть обновление фоновое и оно длится долго. Цикла бесконечного при этом нет, и не совсем понятно как  абортить выполнение обновления , который для вас как черная коробочка.
По вопросам из тем форума, личку не читаю.
 
StoTisteg, процесс выполняется, но очень медленно. И эксель никак не реагирует. А если обновить таблицу повторно, через немного времени - отчет формируется отлично.
Поэтому и нужен обработчик написать, что бы в случае слишком долгого обновления отчета, нужные строчки макроса перезапустились повторно.

БМВ, абортить по времени будет достаточно.
Если обновление сводной таблицы занимает больше 15 минут (обычно хватает 3-5 мин) - тогда абортить и повторить обновление.

Или каким-то более интересным способом
Изменено: zavex - 06.07.2018 19:50:07
 
тогда уж надо ставить флаг начала процесса и снимать его по событию успешного обновления. Проверять что если флаг висит дольше нужного, то повторять процесс.
вот как отловить событие успешного обновления - трудно сказать не видя примера. Например 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


Цитата
Для отладки достаточно вручную запустить Start

С уважением,
Владимир Захаров (ZVI)
По вопросам из тем форума, личку не читаю.
 
К слову, что процесс обновления таблицы не зависает намертво - макрос обновления можно остановить простым нажатием Esc. Но если этого не сделать, он так и будет обновлять и обновлять безрезультатно...
 
пробуйте:
Try ... Catch .. Finaly .. End Try
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
БМВ, незнакомая для меня конструкция с флагом. Буду пробовать.
Но отладка в ручную точно не подойдет т.к. в макросе запускается сразу формирование нескольких отчетов.
Тогда лучше, что бы хоть просто прерывалось обновление сводной таблицы и ифом отмечалось, какой отчет не обновился.
 
Ігор Гончаренко, такая конструкция Try ... Catch .. Finaly .. End Try - в vba не развита.
https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=87640&MID=s

а конструкция
On Error GoTo
не применю, т.к. ошибки по факту и нет в главном коде макроса.
Изменено: zavex - 31.05.2018 19:53:36
 
пример нашел. осталось подпилить под задачку.
https://www.myonlinetraininghub.com/pausing-or-delaying-vba-using-wait-sleep-or-a-loop
и еще пример
http://www.codenet.ru/progr/vbasic/vbloop.php
но шото никак не допилю....
Изменено: zavex - 06.07.2018 19:50:55
 
Цитата
БМВ написал:
вот как отловить событие успешного обновления
Проверять изменения на листе который должен обновиться в ячейке которая обязательно измениться. Или заведомо, перед запуском обновления, изменить некоторую ячейку на свой текст.
Если обновление это фоновый процесс, и сообщения могут достучаться до Excel, я бы проверял по таймеру. См. вложение
Изменено: Anatoliy Pychev - 01.06.2018 03:20:31
 
короче, в 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. БМВ Вам дал готовый алгоритм
Цитата
zavex написал:
Уважаемые, прошу ваших вариантов, желательно готовых
Весь код который Вам нужен уже есть в этой ветке, Вам осталось только собрать все воедино.
 
БМВ, Загрузил ПРИМЕР отчета
Изменено: zavex - 01.06.2018 10:15:26
 
Цитата
БМВ написал:
Если запросов несколько , то несколько флагов
Я бы их в коллекцию засунул, ибо Бог весть, сколько там этих запросов. А так запустили запрос — добавили флаг.
А, не, ерунду написал...
Изменено: StoTisteg - 01.06.2018 10:19:30
 
Здравствуйте, коллеги! Для чего сложный путь (#16), если есть простой (#8 ), применимый к объектам QueryTable всех типов (в том числе, из #18). Те действия, которые нужно выполнить по завершению обновления QueryTable, вставляйте в q_AfterRefresh.
Изменено: sokol92 - 01.06.2018 13:20:59
Владимир
 
zavex, Я в VBA не очень, я только стратегию вырабатываю.

Цитата
sokol92 написал:
q_AfterRefresh.
Владимир, приветствую.  №8 применим для тригера/флага, но весь сыр бор то в том, что если запрос так и не закончился, то его надо прервать, и это (со слов автора) успешно делается повторным запуском. Событие завершения запроса помогает, но не освобождает от прочей аналитики по истечении заветных 15 мин.
По вопросам из тем форума, личку не читаю.
 
Понятно. Я не знаю способа, как можно макросом прервать выполняющийся запрос обновления данных. Грубая сила: закрыть книгу и открыть заново (через Application.Ontime).
Владимир
 
Всем спасибо большое!!!!
С помощью VBA решение так и не нашел.
Писал на powershell
Алгоритмически работы следущий:
Запуск скрипта1 powershell. Если через 15 мин процесс Excell висит - taskill Excell. Перезапуск документа с последующим перезапуском обновления сводной таблицы
   Обновление сводной
   Остальной код макроса
Запуск скрипта2 powershell. Находит другие процессы powershell. taskill всех процессов powershell(в том числе убивает таймер на выключения экселя. эксель работает дальше)

У кого возникнет аналогичная проблема рекомендую разбить большую сводную таблицу на несколько маленьких (по 3-4-5 столбцов). И уже после собирать полученные данные в одну таблицу (копирование, впр. Все грузить в массив и только после выгружать в нужную таблицу). Лично у меня скорость формирования отчетов возросла, эксель вроде как не подвисает в конечном счете.
Страницы: 1
Наверх