how tell if file already open?

[Guest]

Thanks for any help.
I have a macro that pastes some info into another workbook. I open the
workbook, then run the macro. I would like to make the macro code to open the
file if not already open. If I make the code open a file without checking if
it is already open, I would get a notice about opening it twice. So I would
like to code it so that it checks if the file is already open. How do I tell
if a file is already open? With workbooks.something? Or windows.something?
Thanks.

 

[Norman Jones]

Hi Ian,

Try a function posted by chip Pearson:

'=================>>
Function WorkBookExists(ByVal WBName As String) As Boolean
On Error Resume Next
Dim Pos As Long
If InStr(1, WBName, Application.PathSeparator) > 0 Then
For Pos = Len(WBName) To 1 Step -1
If Mid$(WBName, Pos, 1) = _
Application.PathSeparator Then
Exit For
End If
Next Pos
WBName = Mid(WBName, Pos + 1)
End If
If Right$(WBName, 4) <> ".xls" Then
WBName = WBName & ".xls"
End If
If Pos > 0 Then
WorkBookExists = CBool(Len(Workbooks(WBName).Name)) _
And (Workbooks(WBName).FullName = WBName)
Else
WorkBookExists = CBool(Len(Workbooks(WBName).Name))
End If
End Function
'<<=================

 

[David]

=?Utf-8?B?SWFuIEVsbGlvdHQ=?= wrote

Thanks for any help.
I have a macro that pastes some info into another workbook. I open the
workbook, then run the macro. I would like to make the macro code to
open the file if not already open. If I make the code open a file
without checking if it is already open, I would get a notice about
opening it twice. So I would like to code it so that it checks if the
file is already open. How do I tell if a file is already open? With
workbooks.something? Or windows.something? Thanks.

I'm currently using the following Function I found (wish I could credit the
author):

Function IsOpen(ByRef BookName As String) As Boolean
On Error Resume Next
IsOpen = Not (Application.Workbooks(BookName) Is Nothing)
End Function

Called like this:

If IsOpen("yourfilename.xls") Then
'<your code here>
Else
Workbooks.Open "yourfilename.xls"
End If

 

[Guest]

Here's a quick, sneaky way to do it.

In the other workbook, create a short, dumb macro like "Range("A1").select"

Now, when you want to open it, in the current book just say "Run other
book's dumb macro" (you can record a macro to do this).

If the other book is open, the dumb macro will run and that's it. If it's
not, the "Run" command will open the book and then run the macro.


HTH
Paul

 

[Guest]

your code will work without the .xls extension in the isopen call.
just fyi
kanan

 

[Tom Ogilvy]

Just for information, that depends on the windows settings. It will always
work with .xls, so suggest always to use .xls.

 

[David]

=?Utf-8?B?S2FuYW4=?= wrote

your code will work without the .xls extension in the isopen call.
just fyi
kanan

Thanks

 

[David]

=?Utf-8?B?S2FuYW4=?= wrote

your code will work without the .xls extension in the isopen call.
just fyi
kanan

Actually, just tested with XL2000 and I need the extension to avoid the
'already open' alert.

 

[David]

Tom Ogilvy wrote

Just for information, that depends on the windows settings. It will
always work with .xls, so suggest always to use .xls.

I see by this that XL version isn't the controlling factor then? I need the
extension as stated in a 2nd reply to OP.

 

[Tom Ogilvy]

Over all versions of Excel and all verions of windows, to the best of my
knowledge, it always works when you use the extension. It doesn't always
work when you don't use the extension. So always use the extension.

 

[David]

Tom Ogilvy wrote

Over all versions of Excel and all verions of windows, to the best of
my knowledge, it always works when you use the extension. It doesn't
always work when you don't use the extension. So always use the
extension.

Ok, thanks.