Is there a way to check and see if a particular file is open in another session of Excel. I know how to open the file but lack the knowledge to check first if it is open.
Is there a way to check and see if a particular file is open in another session of Excel. I know how to open the file but lack the knowledge to check first if it is open.
Last edited by SDruley; 06-20-2011 at 01:57 AM.
Turn Data into Information
Turn Information into Knowledge
Turn Knowledge into Direction
Turn Direction into Leadership
Turn Leadership into Results
Stephen Druley
It's not how quickly you think
But how deeply you think
The quality of thinking is measured
by remoteness to conformance
Stephen Druley
Hi SDruley, perhaps this example will help you out:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=468
Hope it helps!
That suggestion could be reduced to:
Function IsFileOpen_snb(FileName As String) On Error Resume Next Open FileName For Input Lock Read As 1 Close IsFileOpen_snb = Err.Number <> 0 End Function Sub tst() c01 = "E:\OF\adressen 001.xls" MsgBox c01 & " is " & IIf(IsFileOpen_snb("E:\OF\adressen 001.xls"), "", "not ") & "in use" End Sub
It is amazing how Excel vba programming can be a measure of how deeply one can think through a solution. I am totally blown away by snb's response here. Sensational programming.
snb, since you already specified the variable c01 and set it to the file path/name, could you not use it in the IIf statement, e.g.
instead ofIIf(IsFileOpen_snb(c01)...
?IIf(IsFileOpen_snb("E:\OF\adressen 001.xls")...
@Paul,
Yes, of course. You are quite right. Thanks !
You triggered me not to use a redundant variable at all, resulting in :
Please scrutinize this as thoroughly as you did my previous suggestion.
Please scrutinize this code as thoroughly as you did my previous suggestion.Sub tst() MsgBox IsFileOpen_snb("E:\OF\adressen 001.xls") End Sub Function IsFileOpen_snb(FileName As String) On Error Resume Next Open FileName For Input Lock Read As 1 Close IsFileOpen_snb = FileName & " is " & IIf(Err.Number = 0, "not ", "") & "in use" End Function
Last edited by snb; 06-20-2011 at 02:54 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks