I wish to export data to another workbook. I have added a msgbox before close asking if I'm ready to export the data. When I select yes the data exports. The issue I'm having is that the workbook the data exports to is regularly being used. I want to add a feature to my code that will notify the user the data won't be exported as the workbook is open elsewhere. If the workbook is closed I would like the data to export.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Output As String

Output = MsgBox("Are you ready to export the data?", vbYesNo, "Data Export")

If Output = vbYes Then

'Summary Page (APD+MTC)

'APD Premium

Worksheets("SEC 1 (APD)").Range("AJ" & Cells.Rows.Count).End(xlUp).Copy

Workbooks.Open Filename:="S:\US Div\Accounts\Paramount\2017 Data\Summary\2017 Summary - Edit.xlsm"

Workbooks("2017 Summary - Edit.xlsm").Worksheets("Input P").Activate

Range("C18").PasteSpecial Paste:=xlPasteValues, operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

ActiveWorkbook.Close SaveChanges:=True

End If

End Sub

1 Answers

Zack E On

I got this function from here on SO (Dont remember the post to give proper credit to unfortnuatley.

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

Then in your Sub you call it using something like this:

Dim ret

ret = IsWorkBookOpen(Path to workbook here)

Select Case ret
    Case Is True:  code here
    Case Is False: code here
End Select