VBA Excel Checking whether an XL file is already open elsewhere

[Kamal Hussain]

I am trying to write a sub routine when checks to see if
an excel spreadsheet is already opened by someone else on
the network.

Basically, I need to update a "summary" spreadsheet with
data ... but it is often open and being view by others ..
so i need to check if it is indeed being read and halt the
update.

hope someone can help

thanks

 

[Tom Ogilvy]

http://support.microsoft.com?kbid=138621
XL: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=291295
XL2002: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=213383
XL2000: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=184982
WD97: VBA Function to Check If File or Document Is Open


Should give you some ideas.

 

[keepitcool]

Kamal,

I've wrapped it in a function..
that you may use like with test example


Sub Test()
Select case IsFileFree("c:\openme.xls")
Case -1
'process file here
Case 0
MsgBox "file is not free"
Case 1
Msgbox "file not found"
End Select
End Sub


Function IsFileFree(sFileName As String) As Integer
Dim wb As Workbook
Dim iReturn As Integer
Dim bScreen As Boolean

bScreen = Application.ScreenUpdating
Application.ScreenUpdating = False

Set wb = Workbooks.Open( _
Filename:=sFileName, _
ReadOnly:=False, _
IgnoreReadonlyRecommended:=True, _
Notify:=False, _
AddToMru:=False)

If wb Is Nothing Then
iReturn = 1
Else
If wb.ReadOnly Then
iReturn = 0
Else
iReturn = -1
End If
wb.Close False
End If
Application.ScreenUpdating = bScreen

'-1 ReadWrite, 0 Readonly, 1 NotFound
IsFileFree = iReturn

End Function


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >