Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Как запретить дальнейшие выполнение кода макроса, пока полностью не выполнится команда "Обновить все запросы (PQ)" ?
 
#1
10.05.2018 18:38:35
Доброго времени суток, уважаемые форумчане! Всех с прошедшим праздником, всем хорошего настроения и долгих лет жизни)))


UPD: Админ's, уважаемые, файл меньшего размера приложить не могу, т.к. теряется его целостность и полная функциональность, что влечет за собой возможную  неясность примера другим пользователям. Пожалуйста, не удаляется документ. Спасибо!


Имеется код, который
1) запускает Обновление всех запросов книги,
2) полученные данные форматирует, удаляет лишние символы, приводит файл в читабельный и пригодный для печати вид
3) сохраняет и закрывает файл

Код выполняется автоматически при открытии книги, если выполнены определенные условия (диапазон ячеек непустой), если условие не выполнено, то макрос игнорируется

Проблема в том, что все строки выполняются одновременно, как мне кажется, ибо данные форматируются не полностью, будто что запросом успело загрузится, то и форматируется, что загрузилось с задержкой - игнорируется, т.к. макрос уже кончился.

Что необходимо сделать с кодом, чтобы вызов макроса форматирования и сохранения с закрытием выполнялись только ПОСЛЕ ПОЛНОГО обновления запросов ? Либо, полное обновление + 1 сек, чтобы наверняка :)
Код
Private Sub Workbook_Open()
                                                                                    ' ОТКЛЮЧИТЬ ПОКАЗ ОШИБОК
                                                                                    On Error Resume Next
  
                                                                                    ' ОТКЛЮЧИТЬ ОБНОВЛЕНИЕ ЭКРАНА
                                                                                    Application.ScreenUpdating = True
If (Range("L30").Text) = "" Then ' УСЛОВИЕ: ЕСЛИ ЯЧЕЙКИ = ПУСТО

Else
    If (Range("A12:A15").Text) = "" Then ' УСЛОВИЕ: ЕСЛИ ЯЧЕЙКИ = ПУСТО
    Call Обновить_отчет
    Application.Wait Time:=Now + TimeValue("0:00:00")
                If (Range("A12:A15").Text) = "" Then ' УСЛОВИЕ: ЕСЛИ ЯЧЕЙКИ = ПУСТО
                Else
                Call Выровнять_текст
                End If
    End If
        
End If
                                                                                    ' ВКЛЮЧИТЬ ОБНОВЛЕНИЕ ЭКРАНА
                                                                                    Application.ScreenUpdating = True
End Sub
Изменено: falmrom - 10.05.2018 18:40:14
Улыбнись.
 
 
 
#2
10.05.2018 18:46:33
Только вот сегодня было
https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=1&TID=104501
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#3
10.05.2018 20:03:07
Dyroff,делал. Все равно работает не так, как требуется.
Улыбнись.
 
 
 
#4
10.05.2018 21:01:36
Цитата
falmrom написал:
работает не так, как требуется
А с чего тогда взяли, что дело именно в обновлении? Если обновлять как в статье написано, циклом по всем подключениями, а не через RefreshAll как у Вас в файле - то все работает. Потому что .BackgroundQuery = False как раз заставляет дождаться окончания запроса и только после этого выполнять следующую строку кода.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#5
10.05.2018 21:15:08
и даже запасная секунда - не нужна!
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#6
10.05.2018 22:36:14
Дмитрий Щербаков, убраль обновление отдельным макросом, сделал обновление этой функцией, остальные макросы все равно срабатываю раньше. Хоть со строкой
Цитата
Application.Wait Time:=Now + TimeValue("0:00:05")
Строки, которые запускают другие макросы, которые форматируют текст, срабатываю раньше, чем заканчивается обновление.
Улыбнись.
 
 
 
#7
10.05.2018 23:01:32
Код
qtQueryTable.Refresh BackgroundQuery:=False


Тоже пробовал. Один фиг, когда вызываю макрос форматирования, он не срабатывает
Улыбнись.
 
 
 
#8
11.05.2018 07:41:56
falmrom, не углублялся в вашу тему, но можно еще попробовать повесить макрос проверки изменения ячейки. Например, запрос из PQ возвращает таблицу из 1 столбца в диапазон А1:А10, заголовок в А1="Столбец_1", соответственно при обновлении запроса, как только он вернет таблицу сработает макрос события изменения листа. Для пущей надежности можно еще переименовать "Столбец_1" в "Обновленный_Столбец_1" и сравнивать с этим значением.
 
 
 
#9
11.05.2018 08:54:03
Доброе время суток.
А не всё так просто с обновлением запроса PQ, по крайней мере в 2010. Попробовал варианты как с DDE со связыванием ActiveX TextBox с ячейкой таблицы (предварительно очищенной), и вариант Степана (заголовок столбца предварительно изменён)
Код
Private Sub TextBox1_Change()
    MsgBox "changed textbox" & vbLf & TextBox1.Text
End Sub

Private Sub Worksheet_Calculate()

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "changed head" & vbLf & Me.Range("A1")
End Sub

Оба способа отрабатывают и возвращают новое загруженное значение, но физически видно в панели запросов (Показать область), что запрос ещё обновляется, а Excel на экране не отображает изменённого состояния. Тоже самое происходит и при QueryTable.Refresh BackgroundQuery:=False, MsgBox появляется раньше чем заканчивается отображение процесса выполнения запроса.
 
 
 
#10
11.05.2018 10:12:55
Я так подозреваю, что запрос уже выполнился, а вывод результата в таблицу не закончился. Наверное где-то тут собака порылась.
Вот горшок пустой, он предмет простой...
 
 
 
#11
11.05.2018 10:28:21
PooHkrd,возможно, как вариант!
Улыбнись.
 
 
 
#12
11.05.2018 10:32:37
falmrom, Андрей VG, полностью с вами согласен. Вообще как-то странно RefreshAll срабатывает. Из-за этого пришлось отказаться от запросов в пользу макросного решения. Теперь вместо запроса у меня макрос открывает нужную книгу в фоне и берёт данные в Public-переменную…
Я уже не говорю о том, что, если запрос выгружается на лист, то xl считает очень нужным показать этот лист с обновлённым запросом. Если этот лист скрыт, то появляются артефакты с лишним геморроем для пользователя…брр
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#13
11.05.2018 10:36:37
И это всё, потому что граждане пытаются использовать инструмент не совсем для того, для чего он был задуман.
Вот горшок пустой, он предмет простой...
 
 
 
#14
11.05.2018 11:35:41
а пробкуйте после
Код
RefreshAll
подождать окончания расчетов:
Код
do while application.calculationState <> xlDone
  doEvents
loop
Изменено: Ігор Гончаренко - 11.05.2018 12:20:49
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#15
11.05.2018 11:38:03
Ігор Гончаренко, c Do…While чёт туго (хоть там и также, как и в For…Next). Можно маааахонький пример кода?  :)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#16
11.05.2018 11:46:59
а DoEvents - не вариант в данном случае?
F1 творит чудеса
 
 
 
#17
11.05.2018 11:49:32
Максим Зеленский, здравствуйте! Если вы мне, то я его пока нигде не использовал…
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#18
11.05.2018 11:52:14
Jack Famous, нет, не вам лично, всем участникам дискуссии. Если после BackgroundQuery = False дописать строку DoEvents - должно, по идее, дождаться окончания загрузки на лист
F1 творит чудеса
 
 
 
#19
11.05.2018 12:21:46
Цитата
Jack Famous написал:
маааахонький пример кода
выше и был маленький (рабочий пример кода) взял его в теги "код", так понятнее?))
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#20
11.05.2018 12:22:40
Цитата
Максим Зеленский написал:
и. Если после BackgroundQuery = False дописать строку DoEvents - должно, по идее, дождаться окончания загрузки на лист
Привет, Максим.
Увы, в 2010, тоже самое, о чём писал. В панели запросов идёт отображение обновления, а MsgBox с сообщением уже появляется.
Ещё хуже, если так сделать
Код
Public Sub testRefresh()
    Dim p As ListObject, q As QueryTable, t As Single
    Set p = ActiveSheet.ListObjects(1)
    Set q = p.QueryTable
    t = Timer
    ActiveWorkbook.RefreshAll
    Do Until Timer - t > 1
        DoEvents
    Loop
    Do While q.Refreshing
        DoEvents
    Loop
    MsgBox "Done"
End Sub

Подождал 5 минут, а в панели запросов, так и крутиться круг загрузки. Пока принудительно Stop в редакторе VBA не нажмёшь.
 
 
 
#21
11.05.2018 12:22:49
Ігор Гончаренко, спасибо за пример  :D
Андрей VG, печально  :(
Изменено: Jack Famous - 11.05.2018 12:23:56
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#22
11.05.2018 14:25:35
Можно писать в Майкрософт с этим вопросом?)))
Улыбнись.
 
 
 
#23
11.05.2018 14:43:41
Не знаю как в 2010, но в моем 365 такой код работает:
Код
Sub Обновить_отчет()
    '
    ' ОбновитьФорматировать
    '
    ' ОТКЛЮЧИТЬ ОБНОВЛЕНИЕ ЭКРАНА
    Application.ScreenUpdating = False
    
    ' ОБНОВИТЬ ВСЕ ЗАПРОСЫ
    Dim IsBG_Refresh As Boolean, oc
    For Each oc In ThisWorkbook.Connections        'запоминаем значение обновления в фоне для запроса
        IsBG_Refresh = oc.OLEDBConnection.BackgroundQuery
        'выставляем принудительно ждать завершения запроса
        oc.OLEDBConnection.BackgroundQuery = False
        'обновляем запрос
        oc.Refresh
        'возвращаем обновление в фоне в первоначальное состояние
        oc.OLEDBConnection.BackgroundQuery = IsBG_Refresh
    Next
    MsgBox "Запросы обновлены"
    'ПЕРЕМЕСТИТЬ КУРСОР НА ШАПКУ ТАБЛИЦЫ
    Range("A1:G3").Select
    Application.ScreenUpdating = True
End Sub

каждый запрос в отдельности обновляется и сообщение появляется только после полного обновления. Правда, не все запросы у меня в принципе захотели обновляться, т.к. источников нет, поэтому проверить на боевых таблицах полноту получения данных не получилось. Но на своем тестовом файле у меня все отработало как надо.
В общем-то, это именно то решение, которое я предлагал изначально.
Изменено: Дмитрий Щербаков - 11.05.2018 14:45:01
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#24
11.05.2018 14:47:18
Цитата
Дмитрий Щербаков написал:
в моем 365 такой код работает
особо тестить времени пока нет, но, на первый взгляд, в моём 2016 сработало — спасибо, Дим!  :D
А обязательно "запоминать" .OLEDBConnection.BackgroundQuery? Можно ли, как обычно, выключить, а потом включить?
Я так сделал и всё вроде норм…
Изменено: Jack Famous - 11.05.2018 14:48:11
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#25
11.05.2018 14:50:45
Цитата
Jack Famous написал:
Можно ли, как обычно, выключить, а потом включить?
Как сказать - как обычно. Правильнее запоминать, было ли для конкретного подключения обновление в фоне установлено или нет. Ведь это свойство можно изменить и вручную в свойствах подключения и иногда оно может быть изначально выставлено в False. А методом "как обычно" мы это свойство принудительно поставим в True. Не очень правильно. Это примерно как запоминанием перед выполнением кода включен ли автопересчет формул или нет :)
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#26
11.05.2018 14:51:45
Цитата
Дмитрий Щербаков написал:
после полного обновления.
Дмитрий, да у меня, по косвенным признакам, как значения ячеек, тоже сообщение появляется после смены значений (хотя визуально это не так. Хотя может в оставшееся время PQ просто освобождает ресурсы и не обновляет экран).
А вот код ТС с внедрённым вашим предложением так и не показал. Как и не объяснил, что конкретно не работает с ним. Чем не тот результат? Экспериментировать же за него нет желания. Он разработчик - ему и карты в руки.
 
 
 
#27
11.05.2018 14:52:18
Дмитрий Щербаков, согласен насчёт нюансов для пользователя - спасибо за объяснение   ;)
Изменено: Jack Famous - 11.05.2018 14:52:27
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#28
11.05.2018 18:49:17
ссыль нашёл
Дмитрий Щербаков, Андрей VG, в конце макроса написал переход на лист, макрос перешёл, а потом метнулся на последний лист в книге (лист з выгруженным запросом скрыт). Такие дела  :(  ну хоть отдельнын запросы можно обновлять — всё быстрее, чем RefreshAll
Изменено: Jack Famous - 14.05.2018 11:17:24
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#29
12.05.2018 09:40:51
Цитата
Jack Famous написал:
ссыль[/URL] нашёл
чё-то не то. Ссылки нет. В итоге вообще неясно о чем речь.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#30
14.05.2018 11:23:10
Цитата
Дмитрий Щербаков написал:
Ссылки нет
поправил
Цитата
Дмитрий Щербаков написал:
неясно
отключение обновления в фоне перед обновлением запроса макросом действительно позволило дождаться полного и корректного обновления, но не решило проблему автоперехода (после завершения макроса) на лист с выгруженной из запроса таблицей (закрыть и загрузить в…Таблица). В случае, если лист с такой таблицей скрыт, xl переходит на последний (по расположению) лист в книге. Отменить ЭТО никак не получается…
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
Страницы: 1 2 След.
Наверх