Switch to workbook if already open

[Djblois]

I am using the open dialog box to ask my users what excel spreadsheet they want to use. However, I want to create it so if that file is already open, it will switch to it, instead of getting an error. Is this possible? I know how to do this if I knew the name of the file but not when the user choses the file. Here is my code:

[VBA]Application.Dialogs(xlDialogOpen).Show[/VBA]

and this is the code if I knew the name of the book:

[VBA]On Error Resume Next
Set WB(2) = Workbooks("BusinessReportingReference.xls")

If Err = 9 Then
Workbooks.Open ("H:\@Business_Reporting_Today\References\BusinessReportingReference.xls ")
Else
WB(2).Activate
End If
End Sub[/VBA]

[Bob Phillips]

Use GetOpenFilename rather than the File open dialog, it will return a name string and you can use that to test it.

[Djblois]

How do I capeture and test that name string now?

[Charlize]

[vba]Sub tesing()
Dim yourfilestring As String
On Error GoTo file_error
yourfilestring = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If IsOpen(yourfilestring) = False Then
Workbooks.Open (yourfilestring)
Exit Sub
End If
file_error:
MsgBox ("There was an error with file : " & vbCrLf & yourfilestring)
End Sub
Function IsOpen(FileName As String) As Boolean
' Determine if a workbook is open or not
Dim wb As Workbook
For Each wb In Application.Workbooks
If UCase(wb.Name) = UCase(FileName) Then
IsOpen = True
Exit Function
End If
Next wb
IsOpen = False
End Function[/vba]
Charlize

[Bob Phillips]

[vba]Dim yourfilestring As String
yourfilestring = Application.GetOpenFilename
If IsOpen(yourfilestring) = False Then
Workbooks.Open (ThisWorkbook.Path & "\" & yourfilestring)
End If

Function IsOpen(FileName As String) As Boolean
' Determine if a workbook is open or not
Dim wb As Workbook
For Each wb In Application.Workbooks
If UCase(wb.Name) = UCase(FileName) Then
IsOpen = True
Exit Function
End If
Next wb
IsOpen = False
End Function
[/vba] Charlize

[vba]

Function IsOpen(Filename As String) As Boolean
On Error Resume Next
IsOpen = CBool(Not Workbooks(Filename) Is Nothing)
On Error GoTo 0
End Function
[/vba]

[Charlize]

There seems to be a problem with that function. It's got something to do with the path. When workbook is open, the path is stripped. So when we open the same workbook, the path is included with the filestring. This means that they will never be the same ...

Or am I missing something ???

Charlize

[Charlize]

Revised code (function of xld must be looked at when more time available)
[VBA]Sub tesing()
Dim yourfilestring As String
On Error GoTo file_error
yourfilestring = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If IsOpen(yourfilestring) = False Then
Workbooks.Open (yourfilestring)
Exit Sub
Else
MsgBox yourfilestring & vbCrLf & "is already open."
Exit Sub
End If
file_error:
MsgBox ("There was an error with file : " & vbCrLf & yourfilestring)
End Sub
Function IsOpen(FileName As String) As Boolean
' Determine if a workbook is open or not
Dim wb As Workbook
For Each wb In Application.Workbooks
If UCase(wb.Path & "\" & wb.Name) = UCase(FileName) Then
IsOpen = True
Exit Function
End If
Next wb
IsOpen = False
End Function[/VBA]
Charlize

[Bob Phillips]

There seems to be a problem with that function. It's got something to do with the path. When workbook is open, the path is stripped. So when we open the same workbook, the path is included with the filestring. This means that they will never be the same ...

Or am I missing something ???

Charlize

Only what I didn't say. For this function, you just pass the filename, not the full path.