File or Window is open

S

Steven

Is there a way to tell if a file or window name is open in the current
session of excel and not use the full path. I have seen a routine written of
IsFileOpen()

If IsFileOpen("C:\History\Ac01\File001.xls") Then
'Do this
End If


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

I would like to just see if the window is open and not have to use the full
path name. Is that possible.

Thank you,

Steven
 
G

Gary''s Student

Say we want to know if special.xls is open:

Sub cutaneous()
s = "special"
textt = "special.xls is not open"
For Each wb In Workbooks
If wb.Name = "special" Then
textt = "special.xls is open"
End If
Next
MsgBox (textt)
End Sub
 
L

Leith Ross

Hello Steven,

You can check if a particular workbook is open the in the current
instance of Excel by iterating through the *Workbooks* object
collection.

Code:
--------------------

Function IsWorkbookOpen(ByVal Workbook_Name As String) As Boolean

Dim Wkb As Workbook

For Each Wkb In Workbooks
If Wkb.Name = Workbook_Name Then
IsWorkbookOpen = True
Eixt Function
End If
Next Wkb

End Function
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top