当前位置：文江博客话题详情

Excel VBA刷新等待

发布于 2024-12-27 20:11:19 字数 495 浏览 1 评论 0原文

我正在创建一些代码，我可以在其中单击一个按钮，它将刷新我在该表上的查询表。

现在，我的问题是，刷新后我有更多代码复制了一些信息，但该代码在刷新开始后立即运行，并且信息尚未被替换。

我想创建一个等待刷新完成的时间，然后代码的其余部分可以继续。

我不想只等待 5 秒，而是等待刷新时间，这样我就不会等待太长或太短，具体取决于互联网速度等。

我该怎么做？

编辑：

简单代码：

ActiveWorkbook.RefreshAll

这里我需要延迟或等待代码，直到所有刷新完成......然后

MsgBox("The Refreshing is Completed!")

朝那个方向进行一些操作。但它不能在实际完成之前说出消息框......有时根据互联网速度，刷新需要更短或更长的时间，所以我希望它是实际刷新时间的变量。

原文

I am creating some code where I can click on a single button and it will refresh the querytables that I have on that sheet.

Now, my problem is that I have more code after the fresh that copies some of the information, but this code is being run right after the refresh has started and the information has not yet been replaced.

I want to create a waiting period for the refresh to complete and then the rest of the code can continue.

I don't want to just wait for 5 seconds but for the refreshing period, so that I am not waiting too long or too short, depending on Internet speed etc.

How can I do this?

Edit:

Simple code:

ActiveWorkbook.RefreshAll

Here I need the delay or waiting code till all the refreshing is finished... Then

MsgBox("The Refreshing is Completed!")

Something in that direction. But it can't say the msgbox before it is actually finished.... Sometimes depending on internet speed the refreshing takes shorter or longer, so I want it to be a variable of the actual refreshing time.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

许一世地老天荒 2025-01-03 20:11:19

我正在使用 PowerPivot 模型，我想在保存并关闭模型之前刷新数据。然而，excel只是在刷新完成之前关闭了模型，而模型在打开时又恢复刷新。

在 RefreshAll 方法之后添加以下行就可以了：

ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone

我希望它也适合您。

确保禁用事件以加快速度。

请注意，我使用的是Excel 2010，我不确定此方法是否在旧版本中可用。

I was working with a PowerPivot model, and I wanted to Refresh the data before I saved and closed the Model. However, excel just closed the model before the refresh was complete, and the model resumed refreshing on opening.

Adding the following line right after the RefreshAll method, did the trick:

ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone

I hope it works for you too.

Make sure that you Disable Events to speed things up.

Note that I am using Excel 2010, I am not sure if this method is available in older versions.

回复收藏 0 原文

帅的被狗咬 2025-01-03 20:11:19

在 Web 查询的外部数据范围属性中，您有一个复选框，上面写着“启用后台刷新”之类的内容，您应该取消选中该复选框以获得所需的效果。

看一下本页底部：http://www.mrexcel.com/tip103.shtml 对于图片

编辑：

这里有两个宏，显示想要的效果：

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添加Query，然后执行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>

In the External Data Range Properties of your Web-Query you have a checkbox saying something like "Enable background refresh" which you should uncheck to achieve the desired effect.

Have a look at the bottom of this page: http://www.mrexcel.com/tip103.shtml for pictures

Edit:

Here are two macros that show the desired effect:

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

Execute AddWebquery to add the Query, then execute TestRefreshing to test the effect. You can change the line .BackgroundQuery = False to True to have the wrong result.

Testpage with 10 second sleep:

<!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>

回复收藏 0 原文

清风疏影 2025-01-03 20:11:19

我刚刚遇到了类似的问题，我们通过以下方法解决了它：

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

ActiveWorkbook.RefreshAll

像这样，我们能够确保所有连接 backgroundQuery 属性肯定是 false在调用刷新之前。

I've just had a similar issue, and we've solved it by the following:

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

ActiveWorkbook.RefreshAll

Like this we're able to make sure all the connections backgroundQuery property is definately false before calling the refresh.

回复收藏 0 原文

罪歌 2025-01-03 20:11:19

取消选中“启用后台刷新”
数据->连接->属性

这将禁用后台刷新并等待刷新完成。

回复收藏 0 原文

折戟 2025-01-03 20:11:19

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

我知道这是一个老问题，但这对我有用。
也适用于公式计算时的等待。

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

I know its an old question, but this worked for me.
Also works for waiting while formulas calculate.

回复收藏 0 原文

空心↖ 2025-01-03 20:11:19

尝试这种方法：

With Selection.ListObject.QueryTable
  .BackgroundQuery = False
  .Refresh
End With

当您按照以下方式放置时，BackgroundQuery = False 似乎不会将BackgroundQuery 属性更改为False。

Selection.ListObject.QueryTable.Refresh BackgroundQuery = False  ' doesn't work

Try this approach:

With Selection.ListObject.QueryTable
  .BackgroundQuery = False
  .Refresh
End With

When you put it this following way, the BackgroundQuery = False doesn't seem to change the BackgroundQuery property to False.

Selection.ListObject.QueryTable.Refresh BackgroundQuery = False  ' doesn't work

回复收藏 0 原文

梦中的蝴蝶 2025-01-03 20:11:19

如果你想让你的脚本在 vba 中等待，你必须使用 sleep。但睡眠有时在 Excel vba 中不起作用。

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

而不是尝试使用

< code>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

If you want to make your script wait in vba you have to use sleep. But sleep sometimes won't work in Excel vba.

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

Instead of that try with

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

Sample Code

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

回复收藏 0 原文

み零 2025-01-03 20:11:19

另一种方法是使用 Workbooks.Open 命令将 URL 作为单独的工作簿加载。

这样您就可以在通话结束后立即完全访问来自 Web 请求的数据。另外，Excel 在加载时会显示进度条，而不是像 Web 查询那样冻结。

请参阅我对此问题的回答：当查询完成时，如何对来自 Excel Web 查询的数据进行后处理？

这种方法的权衡是您必须管理自己处理返回的数据 - Excel 不会将其放入给定的目的地。

在我们尝试了与您似乎一直在做的事情非常相似的事情之后，我们最终走上了这条路线。

回复收藏 0 原文

蓝戈者 2025-01-03 20:11:19

'来自 [电子邮件受保护] 2014-08-11
“这是一个简单的版本，可以让您完全控制。
'不要使用 RefreshAll，而是创建以下子例程：
'从 Excl VBA 中调用例程，无论您想在何处执行它，
'在完成之前不会发生任何其他事情。
“另一个好处是它不会刷新任何数据透视表，因此它们不会干扰，
' 如果您的数据透视表依赖于刷新的数据，则可以运行类似的刷新
查询刷新完成后您的数据透视表。

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

'From [email protected] 2014-08-11
'Here's a simple version that will allow you complete control.
'Instead of using RefreshAll, create the following subroutine:
'Call the routine from your Excl VBA wherever you want to execute it,
'and nothing else happens until it is done.
'Another benefit is that it does not refresh any Pivot tables, so they don't interfere,
' and if you have pivots that rely on the refreshed data, you can run a similar refresh
' for your pivots after the query refresh is completed.

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

回复收藏 0 原文

~没有更多了~