Jack said:
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(XLSheetFullTitle)) '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
'---------------------------------------------------------------------------------------
' Procedure: 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()
'---------------------------------------------------------------------------------------
' Procedure: 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
