Open file that is already open gives error message 1

[senators40]

I have an button on my userform that allows the user to open a file, however if they select a file that is already open, I get a message saying that the file is already open and reopening it will cause any changes you have made to be discarded. Do you still want to reopen the file.


If you say no you will get the following error message

Runtime error '1004:'

Method 'Open' of objects 'Workbooks' failed


The line where it stalls is the "Workbooks.Open fn" - I would like to use a If then statement which would state that if the file selected is open then the "Workbooks.Open fn" does not proceed. However if there are better ways of doing it please let me know

Please note I am also using a listbox to identify the open workbooks (listbox1) just in case that makes it easier.


Below is the code that I am using.

Private Sub CommandButton1_Click()

Dim fn As Variant
fn = Application.GetOpenFilename("Excel-files,*.xls", _
1, "Select One File To Open", , False)
If TypeName(fn) = "Boolean" Then Exit Sub
' the user didn't select a file
Debug.Print "Selected file: " & fn

Workbooks.Open fn

Any help would be appreciated

Thanks,

Jeff

 

[ajetrumpet]

I'm not big on code, but it sounds like what you what is a statement that will eliminate your RUNTIME and FAILED error messages. I interpret that as "code" modifying "code", is that even possible?

 

[CautionMP]

senators40,
If the workbook is already open you can:
[ol][li]Open it as read only.[/li]
[li]If the workbook is open by the user running the macro you can just grab the instance of the workbook.[/li][/ol]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[Bong]

I think what happens is the variable fn is null, not false.

_________________
Bob Rashkin

 

[AnotherHiggins]

I use this public sub that can be used in other subs to see if a file is already open:

' This function checks to see if a file is open or not. If the file is
' already open, it returns True. If the file is not open, it returns
' False. Otherwise, a run-time error occurs because there is
' some other problem accessing the file.

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

Let's say you want to check on a file named Book1.xls in C:\TestPath.

Sub TestFileOpened()

    ' Test to see if the file is open.
    If IsFileOpen("C:\TestPath\Book1.xls") Then
        ' Add code here to handle case where file is already open
    Else
        Workbooks.Open "c:\Book2.xls" ' Open the file in Microsoft Excel.
        ' Add code here to handle case where file is NOT already open
    End If

End Sub

I think I snagged the code from here:

http://support.microsoft.com/kb/138621

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.