none
Changing the ActiveSheet in an Excel workbook via Visual Basic 2005

    Question

  • I would like to enter data in an Excel workbook that has several worksheets.  So far my code is as follows:

    Dim oXL As New Excel.Application
    Dim oWB As Excel.Workbook
    Dim oSheet As Excel.Worksheet

    oWB = oXL.Workbooks.Open("c:\payroll\payroll.xls")
    oSheet = oWB.ActiveSheet

    I assume the ActiveSheet is the first worksheet in the Excel workbook.  How can you change the ActiveSheet to another worksheet in the workbook?

    Saturday, August 09, 2008 6:08 PM

Answers

  •  You can use either the workbook or the application objects to get tot he worksheet collection (the oXL object will return the worksheets colection of its currently active workbook.  Also, either the Sheets or the Worksheets property will work.  Also, these properties will accept either the index number (1-based, so "0" will cause an exception) or the string name of the worksheet.  So all of the following should work for you (assuming you have two sheets and they are named "Sheet1" and "Sheet2")...

    Dim oXL As New Excel.Application
    Dim oWB As Excel.Workbook
    Dim oSheet As Excel.Worksheet
    oWB = oXL.Workbooks.Open("c:\payroll\payroll.xls")

    'If you know the worksheet name then...
    oSheet = oXL.Sheets("Sheet1")
    oSheet = oXL.Sheets.Item(
    "Sheet1")
    oSheet = oXL.Worksheets(
    "Sheet2")
    oSheet = oXL.Worksheets.Item(
    "Sheet2")
    oSheet = oWB.Sheets(
    "Sheet1")
    oSheet = oWB.Sheets.Item(
    "Sheet1")
    oSheet = oWB.Worksheets(
    "Sheet2")
    oSheet = oWB.Worksheets.Item(
    "Sheet2")

    'Or if you know the sheet's index then...
    oSheet = oXL.Sheets(1)
    oSheet = oXL.Sheets.Item(2)
    oSheet = oXL.Worksheets(1)
    oSheet = oXL.Worksheets.Item(2)
    oSheet = oWB.Sheets(1)
    oSheet = oWB.Sheets.Item(2)
    oSheet = oWB.Worksheets(1)
    oSheet = oWB.Worksheets.Item(2)

    oSheet.Activate()

    Saturday, August 09, 2008 7:11 PM
  • You can simply reference the Worksheets by their index or name using the Workbook.Worksheets property.

    http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.worksheets(VS.80).aspx
    Saturday, August 09, 2008 7:03 PM

All replies

  • You can simply reference the Worksheets by their index or name using the Workbook.Worksheets property.

    http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.worksheets(VS.80).aspx
    Saturday, August 09, 2008 7:03 PM
  •  You can use either the workbook or the application objects to get tot he worksheet collection (the oXL object will return the worksheets colection of its currently active workbook.  Also, either the Sheets or the Worksheets property will work.  Also, these properties will accept either the index number (1-based, so "0" will cause an exception) or the string name of the worksheet.  So all of the following should work for you (assuming you have two sheets and they are named "Sheet1" and "Sheet2")...

    Dim oXL As New Excel.Application
    Dim oWB As Excel.Workbook
    Dim oSheet As Excel.Worksheet
    oWB = oXL.Workbooks.Open("c:\payroll\payroll.xls")

    'If you know the worksheet name then...
    oSheet = oXL.Sheets("Sheet1")
    oSheet = oXL.Sheets.Item(
    "Sheet1")
    oSheet = oXL.Worksheets(
    "Sheet2")
    oSheet = oXL.Worksheets.Item(
    "Sheet2")
    oSheet = oWB.Sheets(
    "Sheet1")
    oSheet = oWB.Sheets.Item(
    "Sheet1")
    oSheet = oWB.Worksheets(
    "Sheet2")
    oSheet = oWB.Worksheets.Item(
    "Sheet2")

    'Or if you know the sheet's index then...
    oSheet = oXL.Sheets(1)
    oSheet = oXL.Sheets.Item(2)
    oSheet = oXL.Worksheets(1)
    oSheet = oXL.Worksheets.Item(2)
    oSheet = oWB.Sheets(1)
    oSheet = oWB.Sheets.Item(2)
    oSheet = oWB.Worksheets(1)
    oSheet = oWB.Worksheets.Item(2)

    oSheet.Activate()

    Saturday, August 09, 2008 7:11 PM