View Full Version : [SOLVED:] Choose File to Open Question
remy988
11-21-2013, 11:02 AM
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
11-21-2013, 11:23 AM
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
11-21-2013, 12:05 PM
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
11-21-2013, 12:24 PM
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
11-21-2013, 01:19 PM
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
11-21-2013, 02:53 PM
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
11-21-2013, 06:34 PM
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
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
11-22-2013, 05:01 AM
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
11-22-2013, 09:54 AM
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
11-22-2013, 11:09 AM
thanks Paul, good to know
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.