How to automatically accept Excel prompt using VBA

Question

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.

Code on where the problem occurs:

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

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

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?

Answer 1

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