Rename Excel Sheet VB.NET and C#.

Posted by VIJI Monday, April 19, 2010

The following sections will show you how to rename an Excel worksheet through VB.NET and C#.

To rename an excel sheet we need to open the excel file, rename the sheets, sava and close it.

For open an Excel 2007 workbook ,we have to add the Microsoft Excel 12.0 Object Library (For Excel 2007, but it may vary depends on the Excel version you are using) in you project.



From the following pictures to show how to add Excel reference library in your project.


Excel 12.0 interop assembly Reference


Select Microsoft Excel 12.0 Object Library under COM Tab and click OK button

Now we can start coding to open and rename Excel Sheets.

We have to reference the Microsoft.Office.Interop.Excel, which we added now.


VB.NET Code

Imports Excel = Microsoft.Office.Interop.Excel


Private Sub RenameExcelSheetsName(ByVal sFileName As String)



Dim app As Excel.Application = New Excel.ApplicationClass()

Dim excelWorkbook As Excel.Workbook


Dim excelWorkSheet As Excel.Worksheet


Try

excelWorkbook = app.Workbooks.Open(sFileName)

If excelWorkbook.Sheets.Count  > 0 Then


excelWorkSheet = excelWorkbook.Sheets(1)


excelWorkSheet.Name = "General" 'Rename the sheet


End If

excelWorkbook.Save() 'Save the excel


excelWorkbook.Close() 'Close the excel

Catch ex As Exception


     lblStatus.Content = "Export Excel Failed: " & ex.Message


Finally

'Clean up objects
app.Quit()
app = Nothing
GC.Collect()


GC.WaitForPendingFinalizers()


End Try
End Sub

In the code , Imports Excel = Microsoft.Office.Interop.Excel - we assign the excel reference to a variable Excel.



It's so simple !!!!

C#  Code
using Excel = Microsoft.Office.Interop.Excel;

private void RenameExcelSheetsName(string sFileName)



{

Excel.Application app = new Excel.ApplicationClass();

Excel.Workbook excelWorkbook;


Excel.Worksheet excelWorkSheet ;

try {




excelWorkbook = app.Workbooks.Open(sFileName);




if (excelWorkbook.Sheets.Count > 0) {


excelWorkSheet = excelWorkbook.Sheets(1);


//Rename the sheet


excelWorkSheet.Name = "General";


}

//Save the excel
excelWorkbook.Save();


//Close the excel
excelWorkbook.Close();


}


catch (Exception ex) {
               lblStatus.Content = "Export Excel Failed: " + ex.Message;
}


finally {
//Clean up objects


app.Quit();


app = null;


GC.Collect();


GC.WaitForPendingFinalizers();


}
}

2 Responses to Rename Excel Sheet VB.NET and C#.

  1. Anonymous Says:
  2. I used the above code but its giving runtime error in server.any ideas

     
  3. JIGAR TALATI Says:
  4. You are my savior. Easiest thing to code took a while to figure out. Thanks much.

     

Post a Comment