Checking if a file is open before running a macro

RichardMGreen

Well-known Member
Joined
Feb 20, 2006
Messages
2,177
Hi all

I'm using the following code :-
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.
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,364
Code:
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?
 

blablabla

New Member
Joined
Aug 17, 2010
Messages
1
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,526
Messages
5,602,181
Members
414,510
Latest member
mande358

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top