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 |
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 |
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 |
All times are GMT +1. The time now is 12:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com