Rename Excel Worksheet Tabs

Question

Post reply

Rename Excel Worksheet Tabs

brickpack

SSCrazy

Points: 2702
More actions
May 22, 2009 at 12:45 pm

#209899

Does anyone know a way to rename the worksheet tabs on an excel spreadsheet using either SSRS or SSIS?

Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Adam Wilbur SSCertifiable Points: 7001 More actions · Answer 1

I had this same problem, and rather than renaming a sheet in Excel, I had to implement a bit of a workaround using the Document Map and then the Misc / Label property of the table. The sheets aren't renamed, but the document map allows for navigation. It's a damn shame that Microsoft didn't address this in SSRS 2008.

A.J.
DBA with an attitude

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

I would use any script task that allows to run VBscript.

In VB script I would create an instabce of Excel and use Excel Object model to rename the tab. For that Excel should be installed on the computer where the instance is created.

Also you can use SSIS Excel Destination and create a new worksheet with the name you need, read:

http://msdn.microsoft.com/en-us/library/ms190012(SQL.90).aspx

Excel Destination Editor (Connection Manager Page)

Regards,Yelena Varsha

Lily Dvornichenko SSC Rookie Points: 33 More actions · Answer 3

The VBScript is pretty easy

Dim objExcel, objWorkbook

Set objExcel = CREATEOBJECT("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open (DTSGlobalVariables("gvFileName").Value)

objWorkbook.Sheets(1).Name = "TransactionDetails"

objExcel.Application.DisplayAlerts = False

objExcel.ActiveWorkbook.Save

objExcel.ActiveWorkbook.Close

Set objWorkbook = Nothing

objExcel.Application.DisplayAlerts = True

Hope this helps.

I am currently trying to move to 2008 from 2000 and was hoping that with all the buzz, it was going to be a matter of two clicks to do this, but alas still have to use VBScript.