ExcelBanter - Checking to see if a sheet is open

dim otherWkbk as workbook
dim Title as string

title = "something.xls" '<--better to include the extension, too!

set otherwkbk = nothing
on error resume next
set otherwkbk = workbooks(title)
on error goto 0

if otherwkbk is nothing then
set otherworkbook = workbooks.open(filename:="C:\All\Info\" & Title)
end if

Chip Pearson posted this:
http://google.com/groups?threadm=%23...GP11.p hx.gbl

Function IsWorkbookOpen(WBName As String) As Boolean
On Error Resume Next
IsWorkbookOpen = CBool(Len(Workbooks(WBName).Name))
End Function

Then, you can call it from code with a statement like

If IsWorkbookOpen(WBName:="Stats Manager.xls") = True Then
' do you thing
End If

This makes it easier to check lots of times.

Ben H wrote:

Application.DisplayAlerts = False

Work_Area = ActiveWindow.Name

ChDir "C:\All\Info\"
Workbooks.Open Filename:="C:\All\Info\" & Title & ""
ActiveWindow.Visible = False

Where Title is the name of the file I am opening. I want to check to see if
it has been open before and if not, open it. I also don't know how to close
all workbooks that aren't the main one.

Let me try this: I am running the code out of the file "Bob" and need to
access data in the file "John" and "Ben" muliply times. Currently I open
"John"/"Ben", grab the data then close it. I think leaving them open is
quicker but I don't know how to code it.

"Frank Kabel" wrote:

Hi
normally no need to 'open' a sheet. Maybe post your relevant code for
analysis to avoid activate/select statements

--
Regards
Frank Kabel
Frankfurt, Germany

Ben H wrote:
Hi all! I need to check to see if a sheet is already open, and if it
isn't, then open it and hide it. I am doing this in an attempt to
speed up a program that is taking way too long since I open the sheet
then close it and repeat that about 30 times.

THanks!

--

Dave Peterson