Доброго времени суток, уважаемые форумчане! Всех с прошедшим праздником, всем хорошего настроения и долгих лет жизни)))
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
А с чего тогда взяли, что дело именно в обновлении? Если обновлять как в статье написано, циклом по всем подключениями, а не через RefreshAll как у Вас в файле - то все работает. Потому что .BackgroundQuery = False как раз заставляет дождаться окончания запроса и только после этого выполнять следующую строку кода.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Дмитрий Щербаков, убраль обновление отдельным макросом, сделал обновление этой функцией, остальные макросы все равно срабатываю раньше. Хоть со строкой
Цитата
Application.Wait Time:=Now + TimeValue("0:00:05")
Строки, которые запускают другие макросы, которые форматируют текст, срабатываю раньше, чем заканчивается обновление.
falmrom, не углублялся в вашу тему, но можно еще попробовать повесить макрос проверки изменения ячейки. Например, запрос из PQ возвращает таблицу из 1 столбца в диапазон А1:А10, заголовок в А1="Столбец_1", соответственно при обновлении запроса, как только он вернет таблицу сработает макрос события изменения листа. Для пущей надежности можно еще переименовать "Столбец_1" в "Обновленный_Столбец_1" и сравнивать с этим значением.
Доброе время суток. А не всё так просто с обновлением запроса 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 появляется раньше чем заканчивается отображение процесса выполнения запроса.
falmrom, Андрей VG, полностью с вами согласен. Вообще как-то странно RefreshAll срабатывает. Из-за этого пришлось отказаться от запросов в пользу макросного решения. Теперь вместо запроса у меня макрос открывает нужную книгу в фоне и берёт данные в Public-переменную… Я уже не говорю о том, что, если запрос выгружается на лист, то xl считает очень нужным показать этот лист с обновлённым запросом. Если этот лист скрыт, то появляются артефакты с лишним геморроем для пользователя…брр
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Ігор Гончаренко, c Do…While чёт туго (хоть там и также, как и в For…Next). Можно маааахонький пример кода?
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Максим Зеленский, здравствуйте! Если вы мне, то я его пока нигде не использовал…
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous, нет, не вам лично, всем участникам дискуссии. Если после BackgroundQuery = False дописать строку DoEvents - должно, по идее, дождаться окончания загрузки на лист
Максим Зеленский написал: и. Если после 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 не нажмёшь.
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Не знаю как в 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
каждый запрос в отдельности обновляется и сообщение появляется только после полного обновления. Правда, не все запросы у меня в принципе захотели обновляться, т.к. источников нет, поэтому проверить на боевых таблицах полноту получения данных не получилось. Но на своем тестовом файле у меня все отработало как надо. В общем-то, это именно то решение, которое я предлагал изначально.
особо тестить времени пока нет, но, на первый взгляд, в моём 2016 сработало — спасибо, Дим! А обязательно "запоминать" .OLEDBConnection.BackgroundQuery? Можно ли, как обычно, выключить, а потом включить? Я так сделал и всё вроде норм…
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous написал: Можно ли, как обычно, выключить, а потом включить?
Как сказать - как обычно. Правильнее запоминать, было ли для конкретного подключения обновление в фоне установлено или нет. Ведь это свойство можно изменить и вручную в свойствах подключения и иногда оно может быть изначально выставлено в False. А методом "как обычно" мы это свойство принудительно поставим в True. Не очень правильно. Это примерно как запоминанием перед выполнением кода включен ли автопересчет формул или нет
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Дмитрий, да у меня, по косвенным признакам, как значения ячеек, тоже сообщение появляется после смены значений (хотя визуально это не так. Хотя может в оставшееся время PQ просто освобождает ресурсы и не обновляет экран). А вот код ТС с внедрённым вашим предложением так и не показал. Как и не объяснил, что конкретно не работает с ним. Чем не тот результат? Экспериментировать же за него нет желания. Он разработчик - ему и карты в руки.
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
ссыль нашёл Дмитрий Щербаков, Андрей VG, в конце макроса написал переход на лист, макрос перешёл, а потом метнулся на последний лист в книге (лист з выгруженным запросом скрыт). Такие дела ну хоть отдельнын запросы можно обновлять — всё быстрее, чем RefreshAll
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
отключение обновления в фоне перед обновлением запроса макросом действительно позволило дождаться полного и корректного обновления, но не решило проблему автоперехода (после завершения макроса) на лист с выгруженной из запроса таблицей (закрыть и загрузить в…Таблица). В случае, если лист с такой таблицей скрыт, xl переходит на последний (по расположению) лист в книге. Отменить ЭТО никак не получается…
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄