Choose File to Open Question

[remy988]

Hi,

i have an existing script that allows the user to choose an excel file to open which works fine if the file that is chosen is not already opened. is there a way to code around this, so that it does not error out when the file is already open?

thanks

rem

[Paul_Hossler]

I usually do something like this

Option Explicit
Sub test()
    Dim wb As Workbook
    Dim sOpenedName As String
    
    
    sOpenedName = ThisWorkbook.Name '   for test
    For Each wb In Excel.Workbooks
        If wb.Name = sOpenedName Then
            MsgBox sOpenedName & " is open"
            Exit Sub
        End If
    Next

    MsgBox sOpenedName & " is NOT open"

End Sub

Paul

[remy988]

hi Paul,

thanks for looking at this.

my problem is that the user needs to first choose a file to open but sometimes the user forgets that the file is already opened. if the file is opened, then i get an error message :

1004 A document with the name "blah blah" is already open. You cannot open two documents with the same name, even if the documents are in different folders.

i am using excel 2010

[Paul_Hossler]

1. Get the filename the user wants to open (which might be open)

2. Use the code above to see if that file is already open

3. If it's not open, then open it. Otherwise display your error message


Provide more code if you want


Paul

[remy988]

here's what i currently use

Sub OpenSingleFile()
    user = Environ("UserName")
    Dim FileToOpen As Variant
    On Error GoTo exitsub:
    ChDir "C:\Documents and Settings\" & user & "\My Documents\Help\"
    FileToOpen = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
    If FileToOpen = False Then
        halt = True
        MsgBox "You need to select a file"
        ChDir "C:\"
    End If
    
    Workbooks.Open FileToOpen
    'code goes here for the file that is selected and opened. 
exitsub:
End Sub

the directory HELP contains different Excel Files which the user will select to open. i don't know which file the user will choose.

[JKwan]

Try this out:

Sub OpenSingleFile()
    user = Environ("UserName")
    Dim FileToOpen As Variant
    Dim sTempFile As String
    
    On Error GoTo exitsub:
    ChDir "C:\Documents and Settings\" & user & "\My Documents\Help\"
    FileToOpen = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
    
    If FileToOpen = False Then
        halt = True
        MsgBox "You need to select a file"
        ChDir "C:\"
    End If
    
    sTempFile = FileToOpen
    If Not CheckFileOpen(sTempFile) Then
        Workbooks.Open FileToOpen
    Else
        MsgBox "file is opened"
    End If
exitsub:
End Sub
Function CheckFileOpen(sOpenedName As String) As Boolean
    Dim wb As Workbook
    
    CheckFileOpen = False
    For Each wb In Excel.Workbooks
        If wb.FullName = sOpenedName Then
            CheckFileOpen = True
            Exit Function
        End If
    Next
End Function

[Paul_Hossler]

I was thinking something like this

 
Option Explicit
Dim Halt As Boolean
Sub OpenSingleFile()
    Dim FileToOpen As String
    Dim wb As Workbook
    ChDir "C:\Documents and Settings\" & Environ("UserName") & "\My Documents\Help\"
    
    FileToOpen = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
    If UCase(FileToOpen) = "FALSE" Then
        Halt = True
        MsgBox "You need to select a file"
        ChDir "C:\"
        Exit Sub
    End If
    
    For Each wb In Excel.Workbooks
        If wb.Name = FileToOpen Then
            MsgBox FileToOpen & " is already open"
            Exit Sub
        End If
    Next
    
    
    Workbooks.Open FileToOpen
     'code goes here for the file that is selected and opened.
End Sub

Paul

[GTO]

Howdy all,

Is there room for one more?

Option Explicit
  
Dim Halt As Boolean
  
Sub OpenSingleFile()
Dim FileToOpen As String
Dim wb As Workbook
  
  ChDir "C:\Documents and Settings\" & Environ("UserName") & "\My Documents\"
  
  FileToOpen = Application.GetOpenFilename("Excel Files (*.xl*), *.xl*")
  
  If UCase(FileToOpen) = "FALSE" Then
    Halt = True
    MsgBox "You need to select a file"
    ChDir "C:\"
    Exit Sub
  End If
  
  If Not WorkbookIsOpen(FileToOpen) Then
    Set wb = Workbooks.Open(FileToOpen)
  Else
    Set wb = Workbooks(Mid$(FileToOpen, InStrRev(FileToOpen, "\") + 1))
  End If
  
  'code goes here for the file that is selected and opened.
  
End Sub
  
Function WorkbookIsOpen(wbFullName As String) As Boolean
  On Error Resume Next
  WorkbookIsOpen = Workbooks(Mid$(wbFullName, InStrRev(wbFullName, "\") + 1)).FullName = wbFullName
End Function

[remy988]

Thanks JKwan, Paul & GTO. Works great.

Question for Paul & GTO:
my original code :

If FileToOpen = False

did not contain quotes

"False"

this made a difference.

thanks again
rem

[Paul_Hossler]

Application.GetOpenFilename returns a Variant since it can return arrays, etc.

I put the return value into a String variable (personal style) since I like to avoid Variants when I can

If you click [Cancel] from the dialog box, it returns a boolean False, but since I put it into a String, you need to test for "False"

Paul

[remy988]

thanks Paul, good to know