Workbook.Open & Error Handling Best Practice

Hi again... I know how to check to see if the workbook exists and I found a routine to check to see if the workbook is currently open but instead of exiting the sub early, I would like to allow the user to either have the opportunity to close the open workbook prior to proceeding or even better by making use of the open workbook if it is indeed open.
I currently use the set wb1 & wb2 to open specific workbooks if they exist and are not open... can I use error handling to set an already open workbook and continue with my code?
Thus using:

Set wb2 = Workbooks.Open(wPath & w1stFolder & wFolder & wFileName2) 'If the workbook is not open

Set wb2 = the open workbook 'If workbook is already open... not sure how that would be coded!

I have been bad at using error handling but decided that I should start... I always try to code to cover all situations but I now realize that if other users are using, they will find new ways of creating errors. You can't cover everything and if you try... you will have a lot of extra un-necessary code (which is what I have now!)

Thanks
Craig

Public Sub save_current_data() 'Save verification and backup file on exit

    Dim wPath As String
    Dim w1stFolder As String
    Dim wFolder As String
    Dim wFileName1 As String
    Dim wFileName2 As String
    Dim x As Integer
    Dim y As Long
    Dim currentWB As Workbook
    Dim iStr As String
    Dim oldDate As Date
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim sht As Worksheet
    Dim lastRow As Long

    wPath = Sheets("data").Range("D2").Value 'MyDocuments Path
    w1stFolder = Sheets("data").Range("D9").Value '1St Level Folder in MyDocuments "LabourForecastTool"
    wFolder = Sheets("data").Range("D4").Value 'Data Folder in MyDocuments/LabourForecastTool  "LabourForecastData"
    wFileName1 = Sheets("data").Range("D6").Value 'Data Backup File in MyDocuments/LabourForecastTool  "LabourForecastData" for Saving all data
    wFileName2 = Sheets("data").Range("D7").Value 'Verification File in MyDocuments/LabourForecastTool  "LabourForecastData" for Saving verification data

    oldDate = Sheets("data").Cells(Sheets("data").Range("E11").Value + 11, 4).Value 'Get current working date for comparison
    iStr = "Backup" & Sheets("data").Range("E11").Value 'Get current working date for comparison
    
    Sheets("data").Cells(Sheets("data").Range("E11").Value + 11, 5).Value = "Yes" 'Mark that there is backup data on this program worksheet


    Application.ScreenUpdating = False
    Application.EnableEvents = False


    If Dir(wPath & w1stFolder & wFolder & wFileName1) = "" Or Dir(wPath & w1stFolder & wFolder & wFileName2) = "" Then
        
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        MsgBox "One of the files is missing, unable to backup your data. The program will close without saving.", vbCritical, "CraigsWorld Error"
        Exit Sub
    End If

    If IsFileOpen(wPath & w1stFolder & wFolder & wFileName1) = True Or IsFileOpen(wPath & w1stFolder & wFolder & wFileName2) = True Then
        
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        MsgBox "Either the backup file or Verification file is currently running," & vbNewLine & _
        "we are unable to save your data before exiting!", vbCritical, "CraigsWorld Error"
        Exit Sub
    End If

    Set currentWB = ActiveWorkbook 'Track current workbook name for future use
    
    
    'Save Verification Data to File
    Set wb2 = Workbooks.Open(wPath & w1stFolder & wFolder & wFileName2) 'Verification File to populate current program
    wb2.Unprotect Password:=currentWB.Sheets("data").Range("D10").Value 'Unprotect Workbook in our verification file
    For Each sht In wb2.Worksheets
        sht.Unprotect Password:=currentWB.Sheets("data").Range("D10").Value 'Unprotect all worksheets in our verification file
    Next
        
		'Start of Code to copy all info from the verification file
        
		  'verification code here

		'End of Code to copy all info from the verification file
		
    wb2.Application.Calculation = xlCalculationAutomatic
    wb2.Application.Calculate
    For Each sht In wb2.Worksheets
        sht.Protect Password:=currentWB.Sheets("data").Range("D10").Value 'Protect all worksheets in our verification file before close it
    Next
    wb2.Protect Structure:=True, Windows:=False, Password:=currentWB.Sheets("data").Range("D10").Value 'Protect Workbook in our verification file before close
    wb2.Close savechanges:=True  'Close our data file after saving verification data

    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True



    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual


    'Saving Data Backup to File
    Set wb1 = Workbooks.Open(wPath & w1stFolder & wFolder & wFileName1) 'Data File to populate current program
    wb1.Unprotect Password:=currentWB.Sheets("data").Range("D10").Value 'Unprotect Workbook in our data file
    For Each sht In wb1.Worksheets
        sht.Unprotect Password:=currentWB.Sheets("data").Range("D10").Value 'Unprotect all worksheets in our data file
    Next
		'Start of Code to copy all info from the backup data file
        
		  'code here

		'End of Code to copy all info from the backup data file
 
 wb1.Application.Calculate
        wb1.Application.Calculation = xlCalculationAutomatic
        For Each sht In wb1.Worksheets
            sht.Protect Password:=currentWB.Sheets("data").Range("D10").Value 'Protect all worksheets in our data file before close it
        Next
        wb1.Protect Structure:=True, Windows:=False, Password:=currentWB.Sheets("data").Range("D10").Value 'Protect Workbook in our data file before close
        wb1.Close savechanges:=True  'Close our data file after importing all data
        
    
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

I needed to add the Set statement in for this to work but work it does! Thanks
To also add a check to see if the file exists would I be better to add another error handler or use a - if Dir("c:\excelforum.xls")

Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks("excelforum.xls")
On Error GoTo 0
If wb Is Nothing Then Set wb = Workbooks.Open("c:\excelforum.xls")

Awesome thanks....

So with 8 or 9 lines of code, I checked if the file exist, if the file is open, set workbooks.open or Set the open file.
This will save many lines of code, I wonder why I see other functions and ways to check these items using many many more lines of code. This way is so simple!

Is there any situation that doing this could fail?

Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks("excelforum.xls")
If wb Is Nothing Then Set wb = Workbooks.Open("c:\excelforum.xls")
On Error GoTo 0
If wb Is Nothing Then
MSgbox "File doesn't Exist!"
Exit Sub 'Exit if there is more code to run
end if

'Run additional code

Sorry, in testing I used my own version of the code with my own workbooks...
I see what you mean, based on this code sample I made adjustments using my own references.

Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks("D:\Test\myTest.xlsx")
If wb Is Nothing Then Set wb = Workbooks.Open("D:\Test\myTest.xlsx")
On Error GoTo 0
If wb Is Nothing Then
MSgbox "File doesn't Exist!"
Exit Sub 'Exit if there is more code to run
end if

'Run additional code

So in this case if the workbook was open it would be set else if the workbook is closed it would be opened and set else after the error reset and wb is still equal to nothing it would exit the sub.

Craig

After testing both above methods a little more I found that neither sample code was able to detect if the path file was open in another instance of excel, only if it was open in the same instance.


This version opened a Read Only version of the file that was already open in another instance.

Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks("D:\Test\myTest.xlsx")
If wb Is Nothing Then Set wb = Workbooks.Open("D:\Test\myTest.xlsx")
On Error GoTo 0
If wb Is Nothing Then
MSgbox "File doesn't Exist!"
Exit Sub 'Exit if there is more code to run
end if

'Run additional code

This version reported that the file wasn't open but existed when the file was open in another instance of excel...

Sub Main()
  Const sFullName As String = "C:\mypath\myFile.xlsx"
  Dim wkb As Workbook
  
  Select Case GetWorkbook(sFullName, wkb)
    Case 1
      Debug.Print wkb.FullName & " is open"
    Case 2
      Debug.Print wkb.FullName & " is open, not " & sFullName
    Case 3
      Debug.Print sFullName & " isn't open, but is on disk"
    Case 4
      Debug.Print sFullName & " doesn't exist"
  End Select
  
End Sub

Function GetWorkbook(sFullName As String, wkb As Workbook) As Long
  Dim sFile         As String

  sFile = LCase(Mid(sFullName, InStrRev(sFullName, "\") + 1))

  On Error Resume Next
  Set wkb = Workbooks(sFile)
  On Error GoTo 0

  If Not wkb Is Nothing Then
    If LCase(wkb.FullName) = LCase(sFullName) Then
      ' that file is open
      GetWorkbook = 1
    Else
      ' a file by that name is open, but it's not that file
      GetWorkbook = 2
    End If

  Else
    If Len(Dir(sFullName)) Then
      ' no file by that name open, but it's on disk
      GetWorkbook = 3
    Else
      ' no file by that name, period
      GetWorkbook = 4
    End If
  End If
End Function

Is there a way to detect if the file was open in another instance of excel, using the above methods?

Craig

Yes, in my tests... I used your code as sent, I am not directly referencing the path. This code will detect the open workbook only if it is open with the same instance of excel, the same as the code from JasperD. If another instance of excel is open... the return generated states that the file isn't open but is on disk.

Dim sFile As String

  sFile = LCase(Mid(sFullName, InStrRev(sFullName, "\") + 1))

  On Error Resume Next
  Set wkb = Workbooks(sFile)
  On Error GoTo 0