Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2003 saved file will not open without a blank workbook open | Excel Discussion (Misc queries) | |||
In Excel - Use Windows Explorer instead of File Open to open file | Excel Discussion (Misc queries) | |||
Open email windows can't open, excel shreadsheet file .xls ? | Excel Discussion (Misc queries) | |||
How do I stop Excel from closing the open file each time I open a. | Setting up and Configuration of Excel | |||
Using wildcard for checking whether files are open | Excel Programming |