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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,650
Members
414,399
Latest member
Ninjee

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