延迟VBA代码操作而不冻结Excel [英] Delay VBA code operations without freezing Excel

问题描述

我正在编写一个VBA宏，它将简单地打开另一个工作簿，刷新所有内容，保存然后关闭.它将在第二本工作簿中重复此操作.然后刷新并保存运行VBA代码的主要工作簿.我面临的问题是正在打开的两个工作簿正在使用Microsoft查询从JobBoss数据库中提取数据，并且设置为在打开文件时刷新此数据.因此，文件打开，开始刷新查询数据，然后VBA代码尝试同时刷新，从而导致错误.另外，它不等待数据完成刷新再尝试保存并关闭它.

I am writing a VBA macro that will simply open another workbook, refresh all, save, and then close. It will repeat this with a second workbook. Then it refreshes and save the main workbook that the VBA code was run from. The problem I am facing is that the two workbooks being opened are using Microsoft queries to pull data from our JobBoss database and it is set to refresh this data upon opening the file. So the file opens, starts refreshing the query data and then the VBA code tries to refresh at the same time causing an error. Also, it doesn't wait for the data to finish refreshing before trying to save and close it.

是否可以在不冻结Excel应用程序的情况下延迟每个操作?

Is there a way to delay each operation without freezing the Excel application?

我已经尝试过 application.wait ，但是在此期间Excel处于冻结状态，并且随着时间的流逝，Excel中的数据不会在后台刷新，并且我仍然收到有关尝试执行以下操作的错误在刷新过程中进行操作.

I've tried application.wait but Excel is frozen during this time and the data is not refreshing in the background as once the time is over Excel unfreezes and I still receive the error about trying to do an operation while a refresh is in progress.

这是我到目前为止所拥有的:

This is what I have so far:

Sub Refresh_All()
'
' Refresh_All Macro
'
' Keyboard Shortcut: Ctrl+r
'
    ChDir "Q:\Quality Control"
    Workbooks.Open Filename:= _
        "Q:\Quality Control\Internal Failure Log - Variable Month.xlsm"
    ActiveWorkbook.RefreshAll
    ActiveWorkbook.Save
    ActiveWindow.Close
    ChDir "Q:\Reports"
    Workbooks.Open Filename:= _
        "Q:\Reports\Finished-Transfer Report-variable month.xlsm"
    ActiveWorkbook.RefreshAll
    ActiveWorkbook.Save
    ActiveWindow.Close
    ActiveWorkbook.RefreshAll
    ActiveWorkbook.Save
End Sub

推荐答案

此代码在进入并禁用所有查询表的后台刷新后才起作用.

This code works after going in and disabling background refresh on all of my query tables.

Sub Refresh_All()
'
' Refresh_All Macro
'
' Keyboard Shortcut: Ctrl+Y
'
    ChDir "Q:\Quality Control"
    Workbooks.Open Filename:= _
        "Q:\Quality Control\Internal Failure Log - Variable Month.xlsm"
    Dim endTime As Date
    endTime = DateAdd("s", 2, Now())
    Do While Now() < endTime
DoEvents
    Loop
    ActiveWorkbook.RefreshAll
       endTime = DateAdd("s", 10, Now())
    Do While Now() < endTime
        DoEvents
    Loop
ActiveWorkbook.Save
    endTime = DateAdd("s", 5, Now())
    Do While Now() < endTime
        DoEvents
    Loop
    ActiveWindow.Close
    ChDir "Q:\Reports"
    Workbooks.Open Filename:= _
        "Q:\Reports\Finished-Transfer Report-variable month.xlsm"
        endTime = DateAdd("s", 2, Now())
    Do While Now() < endTime
        DoEvents
    Loop
ActiveWorkbook.RefreshAll
     endTime = DateAdd("s", 10, Now())
    Do While Now() < endTime
        DoEvents
    Loop
ActiveWorkbook.Save
        endTime = DateAdd("s", 5, Now())
    Do While Now() < endTime
        DoEvents
    Loop
ActiveWindow.Close
    ActiveWorkbook.RefreshAll
        endTime = DateAdd("s", 10, Now())
    Do While Now() < endTime
        DoEvents
    Loop
ActiveWorkbook.Save
End Sub

这篇关于延迟VBA代码操作而不冻结Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！