Is a file already open

[John T.]

how can I tell (with VB) if a file is already open?

Thanks,

John

 

[Frank Kabel]

Hi
try something like the following:

sub foo()
dim wbk as workbook
on error resume next
set wbk = workbooks("your_workbookname.xls")
if err.number<>0 then
msgbox "Workbook is not open"
exit sub
end if
'now your other code
end sub

 

[Chip Pearson]

John,

Try something like

Function IsWorkbookOpen(WBName As String) As Boolean
On Error Resume Next
Dim L As Long
L = Len(Workbooks(WBName).Name)
If L = 0 And StrComp(Right$(WBName, 4), ".xls") <> 0 Then
L = Len(Workbooks(WBName & ".xls").Name)
End If
IsWorkbookOpen = (L > 0)
End Function

You can then call this function with code like

If IsWorkbookOpen("Book1") = True Then


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Tom Ogilvy]

The other interpretation of your question is if it is open, but not
necessarily in the instance of Excel containing the code. Then you could
use:

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

 

[Andy Wiggins]

''
***************************************************************************
'' Purpose : Test if a specific file is open
'' Written : 29-Jun-2001 by Andy Wiggins
'' Syntax : IsWindowOpen("FileName.Xls")
'' Returns : "True" or "False"
''
Function IsWindowOpen(pstr_WindowName As String)
Dim w As Window

For Each w In Windows
If w.Caption = pstr_WindowName Then
IsWindowOpen = True
Exit Function
End If
Next

IsWindowOpen = False

End Function
--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"