如何使用VBA自动接受Excel提示? [英] How to automatically accept Excel prompt using VBA?

问题描述

我有一个VBA子，它检查工作簿是否打开，然后将数据从另一个工作簿复制并粘贴到其中.这是在application.ontime循环中自动完成的.在处理其他事情时，我通常将其最小化，并偶尔检查Excel数据副本/粘贴.Excel会不时弹出一个框，提示目标工作簿已打开，并询问我是否要重新打开它.在弹出的时间和我检查工作簿的时间之间，错过了许多子运行.

I have a VBA sub which checks if a workbook is open, and then copy and paste data from another workbook into it. This is done automatically in an application.ontime loop. I usually leave it minimized while I work on other things and check back on the Excel data copy/pasting occasionally. From time to time Excel will pop up a box saying that the destination workbook is open, and asks if I will want to reopen it. Many sub runs were missed between the time it pops up and the time that I check the workbook.

出现问题的代码:

If dataset Is Nothing Then
    Set dataset = Workbooks.Open("C:\Users\Ken\Desktop\Df.xlsx")
Else
    Set dataset = Workbooks("Df.xlsx")
End If

如何使Excel自动处理这种情况?

What can I do to have Excel automatically handle this situation?

第二个问题:我怀疑这可能与我在两次运行之间触摸工作簿而不保存它有关，这导致Excel认为自上次保存以来存在更改.如果这是一个原因，有人可以发表评论吗?

Secondary question: I'm suspecting that it may have something to do with the fact that I may have touched the workbook between runs without saving it, causing Excel to think there are changes since the last save. Can someone comment if this could be a reason?

推荐答案

首先检查工作簿是否已打开.如果没有打开，请尝试将其打开.这可以在下面的代码中完成.请参考链接

first check if workbook is already opened. If not open try to open it. This can be done in below code. Refer the link

Sub TestFileOpened()
    Dim fullPath As String

    fullPath = "C:\Df.xlsx"
    ' Test to see if the file is open.
    If IsFileOpen(fullPath) Then
      Set dataset = Workbooks("Df.xlsx")
    Else
        Set dataset = Workbooks.Open(fullPath)
    End If

End Sub

Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer

    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.

    ' Check to see which error occurred.
    Select Case errnum

        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False

        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True

        ' Another error occurred.
        Case Else
            Error errnum
    End Select

End Function

这篇关于如何使用VBA自动接受Excel提示?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！