PDA

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

GTO
11-21-2013, 08:00 PM
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