VBA If workbook already open then message box & exit sub

[Roggy_Swiss]

Hi,
I have a code taking data of a one workbook (Workbook1) and putting the specified data into another workbook ("MasterFile" {Workbook2}). The code works fine if the MasterFile is NOT open, because the code works on the workbooks.open event.
What I would like is, when the MasterFile is ALREADY open, then populate a MsgBox saying something like "Workbook currently in use, please contact HR and try again later" AND exit the whole sub and return to the original data sheet (Workbook1). Please also note that the Workbook2 is password protected. I know this has probably been asked before, but I cannot find an answer corresponding to this specific issue.

Here is what I have now (not all of it, but the relevant code):

Private Sub CommandButton1_Click()

Dim MasterFile As Workbook
Set MasterFile = Workbooks.Open("G:\Time Tracking\FileName.xlsx", Password:="XY")

Worksheets("MasterFileHours").Select
Worksheets("MasterFileHours").Range("A1").Select
RowCount = Worksheets("MasterFileHours").Range("A1").CurrentRegion.Rows.Count

Application.ScreenUpdating = False
Application.Visible = False

With Worksheets("MasterFileHours").Range("A1")
.Offset(RowCount, 0).Value = WeekNo
.Offset(RowCount, 1).Value = xDate
.Offset(RowCount, 2).Value = EOWEEK
.Offset(RowCount, 3).Value = Employee
.Offset(RowCount, 4).Value = HoursEffective
.Offset(RowCount, 5).Value = HoursTarget
.Offset(RowCount, 6).Value = OverUnder
.Offset(RowCount, 7).Value = Pot60Increase
.Offset(RowCount, 8).Value = Pot60Decrease
End With

MasterFile.Save
MasterFile.Close

End Sub


THANKS IN ADVANCE!!!
BTW: A big thank you to all of you guys!! YOU ARE AMAZING! You can't believe how much impact VBA macros has in the company I work for (since I starting learning it and putting into practice, thanks to you)
Best regards, Roger

 

[Kemmuniemans]

Option Explicit

Sub Sample()
    Dim Ret

    Ret = IsWorkBookOpen("C:\myWork.xlsx")

    If Ret = True Then
        MsgBox "File is open"
    Else
        'Place your code here!'
    End If
End Sub

Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long

    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
    Case 0:    IsWorkBookOpen = False
    Case 70:   IsWorkBookOpen = True
    Case Else: Error ErrNo
    End Select
End Function

 

[njimack]

Take a look at the last function on this page: Spreadsheet Page Excel Tips: Some Useful VBA Functions

 

[Kemmuniemans]

Take a look at the last function on this page: Spreadsheet Page Excel Tips: Some Useful VBA Functions

That might work if you have the file opened yourself, but not if another user has it opened on a network drive.

 

[Roggy_Swiss]

Thank you Njimack & Kemmuniemans for the quick replies.
Yes, the Worksheet2 is located on a network drive.

However, I can't quite follow your code Kemmuniemans. Where do I put my original code in your provided code?
I just tried this:

Dim Ret
Ret = IsOpen.Workbooks("G:\Time Tracking\FileName.xlsx")
If Ret = True Then
MsgBox "The MasterFile is already in use..."
Else
Set MasterFile = Workbooks.Open("G:\Time Tracking\FileName.xlsx", Password:="XY")

Worksheets("MasterFileHours").Select
Worksheets("MasterFileHours").Range("A1").Select
RowCount = Worksheets("MasterFileHours").Range("A1").CurrentRegion.Rows.Count

Application.ScreenUpdating = False
Application.Visible = False

With Worksheets("MasterFileHours").Range("A1")
.Offset(RowCount, 0).Value = WeekNo
.Offset(RowCount, 1).Value = xDate
.Offset(RowCount, 2).Value = EOWEEK
.Offset(RowCount, 3).Value = Employee
.Offset(RowCount, 4).Value = HoursEffective
.Offset(RowCount, 5).Value = HoursTarget
.Offset(RowCount, 6).Value = OverUnder
.Offset(RowCount, 7).Value = Pot60Increase
.Offset(RowCount, 8).Value = Pot60Decrease
End With

MasterFile.Save
MasterFile.Close

End If

End Sub

Note: This whole code should be in the "CommandButton1" click routine.

THANK YOU!

BR, Roger

 

[Kemmuniemans]

This should work:

Option Explicit

Private Sub CommandButton1_Click()
    Dim MasterFile As Workbook
    Dim Ret

    Ret = IsWorkBookOpen("G:\Time Tracking\FileName.xlsx")

    If Ret = True Then
        MsgBox "File is open"
    Else
Set MasterFile = Workbooks.Open("G:\Time Tracking\FileName.xlsx", Password:="XY")

 Worksheets("MasterFileHours").Select
 Worksheets("MasterFileHours").Range("A1").Select
 RowCount = Worksheets("MasterFileHours").Range("A1").CurrentRegion.Rows.Count

 Application.ScreenUpdating = False
 Application.Visible = False

 With Worksheets("MasterFileHours").Range("A1")
 .Offset(RowCount, 0).Value = WeekNo
 .Offset(RowCount, 1).Value = xDate
 .Offset(RowCount, 2).Value = EOWEEK
 .Offset(RowCount, 3).Value = Employee
 .Offset(RowCount, 4).Value = HoursEffective
 .Offset(RowCount, 5).Value = HoursTarget
 .Offset(RowCount, 6).Value = OverUnder
 .Offset(RowCount, 7).Value = Pot60Increase
 .Offset(RowCount, 8).Value = Pot60Decrease
 End With

 MasterFile.Save
 MasterFile.Close
End If
End Sub

Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long

    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
    Case 0:    IsWorkBookOpen = False
    Case 70:   IsWorkBookOpen = True
    Case Else: Error ErrNo
    End Select
End Function

 

[Roggy_Swiss]

THANK YOU VERY MUCH!!! This works just fine.
This is very useful, also for the future!!
BR, Roger

 

[Roggy_Swiss]

Hi again,
I have an interesting issue in relation to the code above.
First of all the actual function works fine. But, ANY other open workbooks kind of hide/close after the code was run, so no excel files (no matter which) show as 'open' in the Taskbar. When I open any other workbook, the other workbooks, which were "Hidden/Closed" pop up again.
You can already see this not being very user friendly. Unfortunately I have no clue why the code does this, since also i've set only the MasterFile and at the end of the code it only closes THIS MasterFile, and not ANY OTHER workbook.
Hopefully this makes sense and you're able to help/advice on this.
Thank you
BR, Roger

 

[Roggy_Swiss]

Issue is solved. I only had to put in this bit of code at the very end: Application.visible = true