Error on Open Workbook

[MichaelH]

Hi Guys,

From a sub in one workbook I am issuing an Open Workbook for another workbook.
What I have found is:

A. If the workbook to open doesn't exist then I get an Error that I can trap for.
B. If the workbook is already open, but has not been changed, or has been saved, then no error arises and I can continue as if I have just opened it.
C. If the workbook is already open and has not been saved following any changes, then Excel throws up a dialog asking whether the workbook should be re-opened (and any changes lost) or not. If not and I continue then this arises an error.

What I want to trap is for C before the dialog appears so that the user doesn't have to make a choice about re-opening or not, can this be done?

Thanks

Michael

[Zack Barresse]

Hey Michael,


This could get you started with some error trapping to check if the workbook is open or not ...





Option Explicit

Sub CheckForOpenWorkbook()
Dim origWb As Workbook, targetWb As Workbook
Set origWb = ThisWorkbook
Application.ScreenUpdating = False
On Error Resume Next
Workbooks("Book1.xls").Activate
If Err <> 0 Then
Err.Clear
Workbooks.Open "C:\Documents and Settings\Rob\Desktop\Book1.xls"
'** Path must be valid, else other errors!
If ActiveWorkbook.Name <> origWb.Name Then
Set targetWb = ActiveWorkbook
End If
Else
Set targetWb = ActiveWorkbook
End If
origWb.Activate
If Not targetWb Is Nothing Then
MsgBox "Your target workbook is " & targetWb.Name & "."
Else
MsgBox "Worbook not found!"
End If
Application.ScreenUpdating = True
End Sub

[mvidas]

Hi Michael,

To check if I have a file open already, I will often use:


Function IsWBOpen(ByVal wbName As String) As Boolean
Dim WB As Workbook
Do While wbName Like "*" & Application.PathSeparator & "*"
wbName = Mid(wbName, InStr(1, wbName, Application.PathSeparator, 1) + 1)
Loop
On Error Resume Next
Set WB = Workbooks(wbName)
If Err.Number = 0 Then IsWBOpen = True Else IsWBOpen = False
End Function

The only limitation to that is if you're checking a network file, and someone else has it open other than you. In cases like that (and in most cases, actually), I will use:


Function IsFileInUse(ByVal flName As String) As Boolean
Dim flNum As Integer
flNum = FreeFile()
On Error GoTo AlreadyOpened:
Open flName For Binary Access Read Lock Read As #flNum
Close #flNum
IsFileInUse = False
GoTo ExitFunc
AlreadyOpened:
IsFileInUse = True
ExitFunc:
On Error GoTo 0
End Function

That will tell you if someone has it open, it would even work if you have multiple instances of excel for some reason, and the file is open in the other one. As I said, I use that most of the time, but sometimes I'm sure the first would be fine so I'll use that. I don't think theres very much difference in runtime, if any.


To check if the file is saved or not, you could use code like:

[vCode]If Workbooks("filename.xls").Saved = False Then[/Code]

That will return True if the file has not changed since last save.


Hope this helps!
Matt

[johnske]

Hi Michael,

Sounds like you could use something like this function by Joseph Ruben...
'//This function is used to determine if a workbook is already open
Function WorkbookIsOpen(WorkBookName As String) As Boolean
'//Returns TRUE if the workbook is open
WorkbookIsOpen = False
On Error GoTo WorkbookIsNotOpen
If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
WorkbookIsOpen = True
Exit Function
End If
WorkbookIsNotOpen:
End Function[/vba]This is used in conjunction with something like this:[vba] If WorkbookIsOpen(MyName) Then
Workbooks(MyName).Activate
Else
Application.Workbooks.Open("C:\Windows\Desktop\" & _
"MyFolder\PersonalFolder\" & MyName & ".xls") _
.Activate
End If

[MichaelH]

Thanks everyone,

The code from johnske works a treat, nice and easy to understand too.

Cheers

Michael
:)