You can use the following function to determine whether a workbook is open.

Function IsWorkbookOpen(WBName As String, Optional WBRef As Variant) As
Boolean
Dim WB As Workbook
For Each WB In Application.Workbooks
If (StrComp(WB.Name, WBName, vbTextCompare) = 0) Or _
(StrComp(WB.FullName, WBName, vbTextCompare) = 0) Then
IsWorkbookOpen = True
If IsMissing(WBRef) = False Then
Set WBRef = WB
End If
Exit Function
End If
Next WB
End Function


You can pass to this function either the simply workbook name (e.g.
"Book1.xls") or the complete file name (e.g., "C:\Test\Book1.xls"). It will
return True if the workbook is open or False if the workbook is not open. If
WBRef is not omitted and the workbook is open, WBRef will be set to
reference the found workbook. For example:

Dim WorkbookName As String
Dim WB As Workbook
WorkbookName = "C:\Book2.xls"
If IsWorkbookOpen(WBName:=WorkbookName, WBRef:=WB) = True Then
MsgBox "Workbook Is Open: " & WB.FullName
Else
MsgBox "Workbook: '" & WorkbookName & "' is not open."
End If




--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Jack" <replyto@it wrote in message
...
There is a slight problem though.
If the spreadsheet is already opened and the code tries to open it again
Excel gets involved asking the question to reload the sheet or not.
How to avoid that?
How to check if the sheet is already opened?
Jack

"Bob Butler" wrote in message
...
"Jack" <replyto@it wrote in message

Doing your way:
Set WB = XLApp.Workbooks.Open(FileName:="C:\whatever.xls") WB is
NOT set

It is in every version of VB and Excel that I've ever worked with.
That's
the appropriate way to opena workbook and get a reference to it. Once
you've done that you can get the worksheets as in:
Set moExcelWS = WB.Worksheets(1)

I have found the following way which works, but I am not sure whether
it is the right one:

If FileExists(XLSheetFullTitle) = True Then
Set moExcelWS =
CreateObject(moExcelApp.Workbooks.Open(XLSheetFull PathTitle))
'moExcelWS is not set but it loads the file
' then I do whats below and moExcelWS is set.

That would open the file and return a Workbook object which is the passed
to
the CreateObject call which will raise an error because even if it pulls
a
default property from the object it's not going to be a valid object
identifier. As written that code makes no sense.

If moExcelWS Is Nothing Then
Set moExcelWS =
moExcelApp.Workbooks(ExSheetTitle).Worksheets(Left (ExSheetTitle,
Len(ExSheetTitle) - 4))
End If

That may or may not pick up the worksheet depending on your naming
conventions for the workbook and worksheets.


--
Reply to the group so all can participate
VB.Net: "Fool me once..."