VBA - If file is open then , else

george hart

Board Regular
Joined
Dec 4, 2008
Messages
241
Hi

The code below opens a file and does some stuff -works fine. However, I want to check whether the file is open first. If it is, then I need a message box to advise "File is open by "Name", ty again in two minutes.

Workbooks.Open Filename:= _
"C:\Documents and Settings\HartG\My Documents\Projects\Sickness\Ops Employee LookUp.xls"
Windows("Absence Macro-Catering.xls").Activate

Any ideas most apreciated
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
Put this function in your module

Code:
Function isfileopen(ByVal fstr As String) As Boolean
    On Error Resume Next
    isfileopen = Len(Workbooks(fstr).Name) > 0
End Function
If the file is open, you get true, otherwise it'll return false

Don't use the full file path, but you will need to include the .xls, although you could probably change this.

HTH
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,990
Messages
5,834,742
Members
430,315
Latest member
bobh63

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