Rename Worksheet in excel through codes

How to Rename Worksheet in excel through code?
Example Sheet1 to MyWorksheet using a code in VB.Net

 

the actual file name or the excel speadsheet. or rename one of the sheets inside that file?

 

yes rename one of the sheets inside that file. We have only one sheet in the excel file and i want to rename it using code.

 

I don't think its possible unless Excel can handle "Macro Scripting" or you do something shitty like getting the handle of its window and using the mouse to click on it etc.

 

Here are two ways of doing this

OLD VB6 way and still works on .net

Code:

Dim xlApp As Object = CreateObject("Excel.Application")
Dim xlBook As Object = xlApp.Workbooks.Open("c:\Test.xls")
xlBook.Worksheets(1).Name = xlBook.Name 'or any name you want
xlBook.Close(1)
xlApp.Quit()

.net way (still using COM Excel object) i like this one better since you can do more with this

Code:

       Dim exc As New Application
        Dim workbooks As Workbooks = exc.Workbooks
        Dim workbook As Workbook = workbooks.Open("c:\Test.xls")
        Dim sheets As Sheets = workbook.Worksheets
        Dim worksheet As Worksheet = CType(sheets.Item(1), Worksheet)
        worksheet.Name = "WOWThis is Cool"

note for this last one you will need the follwing

add the follwing references to your project


add this import to your class file

Code:

Imports Microsoft.Office.Interop.Excel