Warning when the workbook is still being opened (VBA code)

[feni1388]

Hello,

I have a question in regard to saving the current workbook with a certain name.
The current code below that I'm using works OK as long as that workbook isn't opened by another user.
But sometimes when another user is still using the workbook, there's no warning that the workbook can't be saved because it's still being opened.
I just realised it at the end when checking the data whether it's been updated or not.

ActiveWorkbook.SaveAs Filename:="\\obcsvr\Share\Salesdata\Sales" & CurYearOnly & "Year" & CurMonNbr & "Month" & "ForSales" & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True

Is there a way to make the macro stop or give a warning that the file is being opened and can't proceed to the next command?

Thank you.

 

[nemmi69]

Could try this function. I believe there are some other file error codes as well but this is what I use.

Function IsFileOpen(fileName As String)

Dim fileNum As Integer
Dim errNum As Integer

'Allow all errors to happen
On Error Resume Next
fileNum = FreeFile()

'Try to open and close the file for input.
'Errors mean the file is already open
Open fileName For Input Lock Read As #fileNum
Close fileNum

'Get the error number
errNum = Err

'Do not allow errors to happen
On Error GoTo 0

'Check the Error Number
Select Case errNum

    'errNum = 0 means no errors, therefore file closed
    Case 0
    IsFileOpen = False
 
    'errNum = 70 means the file is already open
    Case 70
    IsFileOpen = True

    'Something else went wrong
    Case Else
    IsFileOpen = errNum

End Select

End Function

 

[feni1388]

Thank you.
It seems to be working fine when I try it.