Hi all,

I'm using the code below to open a work book and move to a particular
worksheet in the work book.

The problem arises in the Else Clause of the IF Then statement. I'm not sure
how to set Excel to a particular sheet in the workbook, even though I have
both the name of the sheet and the number of the sheet.

The work books can have either 1 or 2 worksheets in them. If it is 1 sheet,
there is no problem. If there the workbook has 2 sheets I want to set the 2nd
sheet as the active sheet.

Any help is appreciated.

Thanks

Craig


Public Function OpenWorkbook(strFilename) As String
' Comments : Open an Excel workbook and returns the FSR Type.
' Parameters: DocumentName - Path and file name to open
' Returns : FSR Type (Old or New)
'
Dim strFSRType As String
Dim xlActiveSheet As Excel.Worksheet

On Error GoTo PROC_ERR

Set m_Excel = CreateObject("EXCEL.APPLICATION")
Set m_Workbook = m_Excel.Workbooks.Open(strFilename)
Set xlActiveSheet = m_Workbook.ActiveSheet


If xlActiveSheet.Name = "main" Then
'ActiveWorkbook.Sheets.Count
OpenWorkbook = "Old"
Else
OpenWorkbook = "New"
xlActiveSheet = m_Workbook.Sheets("FSR - 45043 Report Sheet")
End If

PROC_EXIT:
Exit Function

PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description
Resume PROC_EXIT

End Function

Am Mon, 9 Oct 2006 16:06:01 -0700 schrieb Craigbob:

For obect variables you need the Set statement:

Set xlActiveSheet = ...

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
-- www.VBOffice.net --


Hi all,

I'm using the code below to open a work book and move to a particular
worksheet in the work book.

The problem arises in the Else Clause of the IF Then statement. I'm not
sure
how to set Excel to a particular sheet in the workbook, even though I have
both the name of the sheet and the number of the sheet.

The work books can have either 1 or 2 worksheets in them. If it is 1
sheet,
there is no problem. If there the workbook has 2 sheets I want to set the
2nd
sheet as the active sheet.

Any help is appreciated.

Thanks

Craig


Public Function OpenWorkbook(strFilename) As String
' Comments : Open an Excel workbook and returns the FSR Type.
' Parameters: DocumentName - Path and file name to open
' Returns : FSR Type (Old or New)
'
Dim strFSRType As String
Dim xlActiveSheet As Excel.Worksheet

On Error GoTo PROC_ERR

Set m_Excel = CreateObject("EXCEL.APPLICATION")
Set m_Workbook = m_Excel.Workbooks.Open(strFilename)
Set xlActiveSheet = m_Workbook.ActiveSheet


If xlActiveSheet.Name = "main" Then
'ActiveWorkbook.Sheets.Count
OpenWorkbook = "Old"
Else
OpenWorkbook = "New"
xlActiveSheet = m_Workbook.Sheets("FSR - 45043 Report Sheet")
End If

PROC_EXIT:
Exit Function

PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description
Resume PROC_EXIT

End Function