VBA Excel Checking whether an XL file is already open elsewhere
 

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

VBA Excel Checking whether an XL file is already open elsewhere
 

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.

--
Regards,
Tom Ogilvy

"Kamal Hussain" wrote in message
...
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

VBA Excel Checking whether an XL file is already open elsewhere
 

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


"Kamal Hussain" wrote:

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