Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
checking file already open status
Another user has a tab delimited text file named myFile.xls open on a
server, writing to the file with Labview, and periodically flushing to disk. While he still has the file open, I open the same file in excel using workbooks.open, with ReadOnly:= true. The Labview user tells me this freezes the file so that he can no longer write to it. How can I avoid having Excel open for exclusive use, even though it is read only, and already open by someone else? If I can't do that, then how can I check the status of the file to make sure that no one has it open before I try to open it? John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
checking file already open status
Hello John, Here is a macro from Microsoft that checks if a file is already open It returns true if the file is open, false otherwise. Insert a VB module into your workbook and paste this code into it. EXAMPLE Test = IsFileOpen("C:\My Documents\MyFile.xls") Code ------------------- ' This function checks to see if a file is open or not. If the file is ' already open, it returns True. If the file is not open, it returns ' False. Otherwise, a run-time error occurs because there is ' some other problem accessing the file. Function IsFileOpen(filename As String) Dim filenum As Integer, errnum As Integer On Error Resume Next ' Turn error checking off. filenum = FreeFile() ' Get a free file number. ' Attempt to open the file and lock it. Open filename For Input Lock Read As #filenum Close filenum ' Close the file. errnum = Err ' Save the error number that occurred. On Error GoTo 0 ' Turn error checking back on. ' Check to see which error occurred. Select Case errnum ' No error occurred. ' File is NOT already open by another user. Case 0 IsFileOpen = False ' Error number for "Permission Denied." ' File is already opened by another user. Case 70 IsFileOpen = True ' Another error occurred. Case Else Error errnum End Select End Function ------------------- Sincerely, Leith Ros -- Leith Ros ----------------------------------------------------------------------- Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846 View this thread: http://www.excelforum.com/showthread.php?threadid=48751 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
checking file already open status
here's one from bob phillips
Dim oWB As Workbook On Error Resume Next Set oWB = Workbooks("myBook") On Error GoTo 0 If oWB Is Nothing Then Set oWB = Workbooks.Open("C:\myDir\myBook.xls") End If oWB.Worksheets("Sheet1").Activate -- Gary "John F. Collins" wrote in message ... Another user has a tab delimited text file named myFile.xls open on a server, writing to the file with Labview, and periodically flushing to disk. While he still has the file open, I open the same file in excel using workbooks.open, with ReadOnly:= true. The Labview user tells me this freezes the file so that he can no longer write to it. How can I avoid having Excel open for exclusive use, even though it is read only, and already open by someone else? If I can't do that, then how can I check the status of the file to make sure that no one has it open before I try to open it? John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional adding by checking status | Excel Discussion (Misc queries) | |||
Checking Protect status | Excel Programming | |||
Check file open status | Excel Programming | |||
Checking for an open file in Excel | Excel Programming | |||
VBA Excel Checking whether an XL file is already open elsewhere | Excel Programming |