Changing Worksheet names in Excel


  • I'm trying to change the sheet names in my new excel workbook.  I know it has 3 sheets as default named "Sheet1", "Sheet2",.....  but I need to change those names and add one more sheet.  I've opened up an existing excel workbook, as well as added a new one.  I need to change the sheet names for the new one.  This is the code I have so far:



    Dim LCV = New Excel._ExcelApplication

    LCV = CreateObject("Excel.Application")

    Dim LCVWkbk As Excel.Workbook


    Dim oBook As Excel.Workbook

    Dim oBooks As Excel.Workbooks

    Dim oSheets As Excel.Sheets

    Dim oSheet1 As Excel.Worksheet

    Dim oSheet2 As Excel.Worksheet

    Dim oSheet3 As Excel.Worksheet

    Dim oSheet4 As Excel.Worksheet


    LCVWkbk = LCV.Workbooks.Open("C:\Documents and Settings\test.xls")

    LCV.Visible = True

    'Opening a new excel workbook

    oBooks = LCV.Workbooks

    oBook = oBooks.Add()

    oSheets = oBook.Worksheets      <-----I'm getting an error here that says COM unhandled

    oSheet1 = oSheets.Add

    oSheet1.Name = "Portfolio"

    Tuesday, July 17, 2007 8:56 PM


  • HELPNewToVB,


    Simply change the worksheet name property, the following example will  change "Sheet3" to "Test", this one uses the excel object model to create a new sheet and you may want to load an existing excel sheet.  Numerous examples available on how to handle this by simple search in the forums.


    Code Snippet

            Dim x As New Microsoft.Office.Interop.Excel.Application

            x.Visible = True


            CType(x.Workbooks(1).Worksheets("Sheet3"), Microsoft.Office.Interop.Excel.Worksheet).Name = "Test"


    Hope that can take you some idea on the issue.

    Friday, July 20, 2007 6:02 AM