My app checks for the (specified) opened Excel spreadsheet and if it is not
opened tries to open it.
Everything works fine if that is a single worksheet.
However, if the xls file contains several sheets (workbooks?) my method
fails miserably.
Below is the code:
XLSheetFullTitle --- full path to the .xls file
ExSheetTitle --- sheet's title (excluding path)
If FileExists(XLSheetFullTitle) = True Then
If moExcelApp.Workbooks.Count 0 Then 'checking if specified
sheet is opened already
For i = 1 To moExcelApp.Worksheets.Count
If moExcelApp.Worksheets(i).Name = Left(ExSheetTitle,
Len(ExSheetTitle) - 4) Then j = 77: Exit For
Next
End If
If j < 77 Then Set moExcelWS =
CreateObject(moExcelApp.Workbooks.Open(XLSheetFull Title)) 'loads (with
error) spreadsheet
If moExcelWS Is Nothing Then Set moExcelWS =
moExcelApp.Workbooks(ExSheetTitle).Worksheets(Left (ExSheetTitle,
Len(ExSheetTitle) - 4))

Your comments appreciated,
Jack

"Jack" <replyto@it wrote in message
...
My app checks for the (specified) opened Excel spreadsheet and if it is
not opened tries to open it.
Everything works fine if that is a single worksheet.
However, if the xls file contains several sheets (workbooks?) my method
fails miserably.
Below is the code:
XLSheetFullTitle --- full path to the .xls file
ExSheetTitle --- sheet's title (excluding path)
If FileExists(XLSheetFullTitle) = True Then
If moExcelApp.Workbooks.Count 0 Then 'checking if specified
sheet is opened already
For i = 1 To moExcelApp.Worksheets.Count
If moExcelApp.Worksheets(i).Name = Left(ExSheetTitle,
Len(ExSheetTitle) - 4) Then j = 77: Exit For
Next
End If
If j < 77 Then Set moExcelWS =
CreateObject(moExcelApp.Workbooks.Open(XLSheetFull Title)) 'loads (with
error) spreadsheet
If moExcelWS Is Nothing Then Set moExcelWS =
moExcelApp.Workbooks(ExSheetTitle).Worksheets(Left (ExSheetTitle,
Len(ExSheetTitle) - 4))

Your comments appreciated,
Jack



I don't know if you can adapt these routines. FindWorkbook searches an
instance of Excel for a specific workbook (file) and, if not open, it opens
it. SelectWorksheet then takes that workbook instance and activates a
specific worksheet (tab). I've had to rip out some of the code that's
specific to myapp and the error traps, but you should be able to do
something with this.

Steve


Private Function FindWorkbook() As Boolean
'---------------------------------------------------------------------------------------
' Procedu FindWorkbook
'
' Purpose: We want to open a specific spreadsheet. Since we may be
interacting with
' an already running copy of Excel, we should check whether the
file is
' open already or whether we need to load it.
'
' oExcel is an open copy of excel (Excel.application)
' oWorkbook is a module level Excel.Workbook variable
' strWorkbook is a module level string containing the file name (full
+ path)
'
' Author: Steve Barnett : 08 Apr 2003
'---------------------------------------------------------------------------------------
Dim oBook As Object

'*** Assume we won't find the book. oWorkbook is a module level variable
(Object)
Set oWorkbook = Nothing

'*** Check already loaded workbooks for out file name
For Each oBook In oExcel.Workbooks
If LCase$(oBook.FullName) = LCase$(strWorkbook) Then
'*** We found our workbook - connect to it.
Set oWorkbook = oBook
oWorkbook.Activate
Exit For
End If
Next

'*** If we didn't find out workbook, load it
If oWorkbook Is Nothing Then
'*** Not loaded
Set oWorkbook = oExcel.Workbooks.Open(strWorkbook, , false)
oWorkbook.Activate
oWorkbook.RunAutoMacros xlAutoOpen
End If

'*** Set the return status to show whether we found the book or not.
FindWorkbook = Not (oWorkbook Is Nothing)

Exit Function
End Function

Private Sub SelectWorksheet()
'---------------------------------------------------------------------------------------
' Procedu SelectWorksheet
'
' Purpose: If the user specified one, select the specific worksheet they
want. If they
' didn't specify one, assume the currently selected sheet.
'
' oExcel is an open copy of excel (Excel.application)
' oWorkbook is a module level Excel.Workbook variable
' strWorksheet is a module level string containing the name of the
tab to open
'
' Author: Steve Barnett : 08 Apr 2003
'---------------------------------------------------------------------------------------
Dim oSheet As Object

If Len(strWorksheet) < 0 Then
For Each oSheet In oWorkbook.Worksheets
If LCase$(oSheet.Name) = LCase$(strWorksheet) Then
Set oWorkSheet = oSheet
Exit For
End If
Next

'*** Was the sheet found, or do we use the default sheet?
If oWorkSheet Is Nothing Then
Set oWorkSheet = oWorkbook.ActiveSheet
End If
Else
'*** No worksheet was specified - use the currently active one.
Set oWorkSheet = oWorkbook.ActiveSheet
End If

'*** Activate the worksheet
oWorkSheet.Activate

Exit Sub
End Sub

There seems to be a problem here

If moExcelApp.Workbooks.Count 0 Then 'checking if specified
sheet is opened already
For i = 1 To moExcelApp.Worksheets.Count
If moExcelApp.Worksheets(i).Name = Left(ExSheetTitle,
Len(ExSheetTitle) - 4) Then j = 77: Exit For
Next

moExcelApp refers to the Excel instance, you then count the workboosk within
that Excel (fine), but the try to address the worksheets within that Excel
app. Worksheets are a collection within the workbook object, so you need to
address them via a workbook.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Jack" <replyto@it wrote in message
...
My app checks for the (specified) opened Excel spreadsheet and if it is
not opened tries to open it.
Everything works fine if that is a single worksheet.
However, if the xls file contains several sheets (workbooks?) my method
fails miserably.
Below is the code:
XLSheetFullTitle --- full path to the .xls file
ExSheetTitle --- sheet's title (excluding path)
If FileExists(XLSheetFullTitle) = True Then
If moExcelApp.Workbooks.Count 0 Then 'checking if specified
sheet is opened already
For i = 1 To moExcelApp.Worksheets.Count
If moExcelApp.Worksheets(i).Name = Left(ExSheetTitle,
Len(ExSheetTitle) - 4) Then j = 77: Exit For
Next
End If
If j < 77 Then Set moExcelWS =
CreateObject(moExcelApp.Workbooks.Open(XLSheetFull Title)) 'loads (with
error) spreadsheet
If moExcelWS Is Nothing Then Set moExcelWS =
moExcelApp.Workbooks(ExSheetTitle).Worksheets(Left (ExSheetTitle,
Len(ExSheetTitle) - 4))

Your comments appreciated,
Jack