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?

can you see what line actually causes that excel pop up? In my Excel 2013 if i try to set a workbook variable opening a workbook that's already open, it just sets it to the already open workbook and throws no popups — user3598756
– user3598756, Commented Oct 26, 2016 at 6:15
@user3598756 I just tested it in Excel 2016. If you try and open another workbook twice it will not throw an error but if you try and open the workbook that is calling the code then the popup will appear (e.g. Workbooks.Open ThisWorkbook.FullName). — user6432984
– user6432984, Commented Oct 26, 2016 at 6:38
Hey guys I'm using Excel 2010. I think it might have to do with the fact that I "touched the workbooked", eg., F2 a cell and then click Enter to back out from it which causes Excel to think the workbook has been edited after the last save... — A1122
– A1122, Commented Oct 26, 2016 at 6:42

Mukul Varshney · Accepted Answer · 2016-10-26 07:16:33Z

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

Collectives™ on Stack Overflow

How to automatically accept Excel prompt using VBA?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related