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

Thanks,

John

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

--
Regards
Frank Kabel
Frankfurt, Germany


John T. wrote:
how can I tell (with VB) if a file is already open?

Thanks,

John

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





"John T." wrote in message
...
how can I tell (with VB) if a file is already open?

Thanks,

John

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


--
Regards,
Tom Ogilvy


"John T." wrote in message
...
how can I tell (with VB) if a file is already open?

Thanks,

John