PDA

View Full Version : Switch to workbook if already open



Djblois
04-09-2007, 01:22 PM
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:

Application.Dialogs(xlDialogOpen).Show

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

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

Bob Phillips
04-09-2007, 04:28 PM
Use GetOpenFilename rather than the File open dialog, it will return a name string and you can use that to test it.

Djblois
04-11-2007, 09:00 AM
How do I capeture and test that name string now?

Charlize
04-11-2007, 11:56 AM
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
Charlize

Bob Phillips
04-11-2007, 12:24 PM
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
Charlize


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

Charlize
04-11-2007, 01:06 PM
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
04-11-2007, 01:12 PM
Revised code (function of xld must be looked at when more time available)
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
Charlize

Bob Phillips
04-11-2007, 02:05 PM
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.