Checking if a file is open before running a macro

[RichardMGreen]

Hi all

I'm using the following code :-

Function IsFileOpen(file_to_open As String) As Boolean
Dim hdlFile As Long
    On Error GoTo FileIsOpen:
    hdlFile = FreeFile
    Open strFileToOpen For Random Access Read Write Lock Read Write As hdlFile
    IsFileOpen = False
    Close hdlFile
    Exit Function
FileIsOpen:
    IsFileOpen = True
    Close hdlFile
End Function

to check if a file is open before allowing a macro to run.
The file in question has a warning about being opened read-only when you open it normally which looks to be making the code fail even when no-one else has the file open.

Anyone any idea on how to bypass this?
I can't change the warning as it's needed by other people.

 

[bobsan42]

Function IsFileOpen([COLOR=Red][B]file_to_open[/B][/COLOR] As String) As Boolean
Dim hdlFile As Long
    On Error GoTo FileIsOpen:
    hdlFile = FreeFile
    Open [B][COLOR=Blue]strFileToOpen[/COLOR] [/B]For Random Access Read Write Lock Read Write As hdlFile
    IsFileOpen = False
    Close hdlFile
    Exit Function
FileIsOpen:
    IsFileOpen = True
    Close hdlFile
End Function

this will normally generate an error i think
or am i wrong?

 

[RichardMGreen]

That won't help!!

I'll change that and try again.

 

[bobsan42]

crossing my fingers

 

[RichardMGreen]

Seems to be working now, cheers!

 

[blablabla]

maybe using displayalerts proprety

I'm quite new on vba but maybe got the solution for you.
check all opened workbooks name to see if the file is already opened, if it is run the macro else open the file and after run the macro.
Here you find the code

Sub check_file()
Dim i As Integer
dim b as workbook
i = 0
For Each b In Workbooks
If b.Name = "Cartel3.xlsm" Then i = i + 1
'replace cartel3.xlsm with the name of the file you want to check
Next b
If i >= 1 Then
macro1
'replace macro1 with the name of the macro you want to run
Else
Application.DisplayAlerts = False
Workbooks.Open "C:\Users\xxx\Desktop\Cartel3.xlsm"
'replace with the file you want to open reference
Application.DisplayAlerts = True
'don't use displayalerts lines if you want the worning message to pop up
macro1
End If
End Sub

hope it helps!