Ожидание обновления Excel vba

Я создаю код, в котором я могу щелкнуть по одной кнопке, и он обновит данные запроса, которые у меня есть на этом листе.

Теперь моя проблема в том, что у меня есть больше кода после нового, который копирует некоторую информацию, но этот код запускается сразу после запуска обновления и информация еще не была заменена.

Я хочу создать период ожидания для завершения обновления, а затем остальную часть кода можно продолжить.

Я не хочу просто ждать 5 секунд, но для периода обновления, так что я не жду слишком долго или слишком коротко, в зависимости от скорости интернета и т.д.

Как я могу это сделать?

Edit:

Простой код:

ActiveWorkbook.RefreshAll

Здесь мне нужен код задержки или ожидания, пока все обновления не закончится... Затем

MsgBox("The Refreshing is Completed!")

Что-то в этом направлении. Но он не может сказать msgbox, прежде чем он на самом деле закончен... Иногда в зависимости от скорости интернета обновление происходит короче или дольше, поэтому я хочу, чтобы это была переменная фактического времени обновления.

Ответы

Ответ 1

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

Посмотрите на нижнюю часть этой страницы: http://www.mrexcel.com/tip103.shtml для изображений

Edit:

Вот два макроса, которые показывают желаемый эффект:

Sub AddWebquery()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://de.selfhtml.org/html/tabellen/anzeige/table_tr_th_td.htm", _
        Destination:=Range("$A$1"))
        .Name = "table_tr_th_td"
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "1"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Sub TestRefreshing()
    Range("A1").Clear
    ActiveWorkbook.RefreshAll
    Debug.Print "Test: " & Range("A1").Value
End Sub

Выполните AddWebquery, чтобы добавить запрос, затем выполните TestRefreshing для проверки эффекта. Вы можете изменить строку .BackgroundQuery = False на True, чтобы получить неправильный результат.

Тестирование с 10-секундным сном:

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>SO-Test</title>
    </head>
    <body>
        <?php
        sleep(10);
        ?>
        <table border="1">
            <thead>
                <tr><th>1</th></tr>
            </thead>
            <tbody>
                <tr><td>2</td></tr>
            </tbody>
        </table>
    </body>
</html>

Ответ 2

Я работал с моделью PowerPivot, и я хотел обновить данные, прежде чем сохранить и закрыть модель. Тем не менее, excel только что закрыл модель до завершения обновления, и модель возобновила обновление при открытии.

Добавив следующую строку сразу после метода RefreshAll, сделал трюк:

ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone

Я надеюсь, что это сработает и для вас.

Убедитесь, что вы отключили события, чтобы ускорить процесс.

Обратите внимание, что я использую Excel 2010, я не уверен, доступен ли этот метод в более старых версиях.

Ответ 3

У меня была аналогичная проблема, и мы решили ее следующим образом:

For i = 1 To ActiveWorkbook.Connections.Count
    ActiveWorkbook.Connections(i).OLEDBConnection.BackgroundQuery = False
    'MsgBox ActiveWorkbook.Connections(i).OLEDBConnection.BackgroundQuery
Next

ActiveWorkbook.RefreshAll

Таким образом, мы можем убедиться, что все свойства соединения backgroundQuery определены false перед вызовом обновления.

Ответ 4

Снимите флажок "Включить фоновое обновление" в Данные → Соединение → Свойства

Это отключит обновление в фоновом режиме и дождитесь завершения обновления.

Ответ 5

'От [email protected] 2014-08-11 "Вот простая версия, которая позволит вам полностью контролировать. 'Вместо использования RefreshAll создайте следующую подпрограмму: "Вызовите процедуру из своего Excl VBA, где бы вы ее не выполнили, 'и ничего не происходит до тех пор, пока это не будет сделано. "Еще одно преимущество заключается в том, что он не обновляет таблицы Pivot, поэтому они не мешают, ', и если у вас есть опорные точки, которые полагаются на обновленные данные, вы можете запустить аналогичное обновление 'для ваших опорных точек после завершения обновления запроса.

sub RefreshQueries()
    dim ws as worksheet
    dim qt as QueryTable
    For each ws in thisworkbook.worksheets
        For each qt in ws.querytables
            qt.refresh
        next qt
    next ws
end sub

Ответ 6

Еще один способ - использовать команду Workbooks.Open для загрузки URL-адреса в виде отдельной книги.

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

См. мой ответ на этот вопрос: Как я могу отправить данные из веб-запроса Excel после завершения запроса?

Компромисс между этим подходом заключается в том, что вам нужно управлять обработкой данных, которые вы возвращаете сами, - Excel не будет помещать их в данный пункт назначения для вас.

Мы закончили этот маршрут после того, как попробовали что-то очень похожее на то, что вы, похоже, делали.

Ответ 7

ActiveWorkbook.RefreshAll
        Do While Application.CalculationState <> xlDone
            DoEvents
        Loop

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

Ответ 8

Если вы хотите, чтобы ваш script ждал в vba, вам нужно использовать сон. Но сон иногда не работает в Excel vba.

http://99students.com/macro-sleep-vba/

Вместо этого попробуйте с

Application.Wait (Now + TimeValue("0:01:00"))

Пример кода

Sub Setting_Sleep_Without_Sleep_Function()
 MsgBox Now
 Application.Wait DateAdd("s", 10, Now)
 MsgBox Now
End Sub