none
Changing Worksheet names in Excel

    Question

  • 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

Answers

  • 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

            x.Workbooks.Add()

            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