Post reply

Rename Excel Worksheet Tabs

  • 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

  • May 22, 2009 at 3:13 pm

    #999220

    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

  • May 22, 2009 at 3:42 pm

    #999232

    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

  • May 13, 2010 at 2:57 pm

    #1167205

    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.

  • May 13, 2010 at 3:06 pm

    #1167207

    You can use xp_cmdshell to rename file:

    exec xp_cmdshell 'ren \\pamfitdwstg\d$\test.xls test_new.xls'

    Minh Vu

  • February 14, 2011 at 11:48 am

    #1286652

    What if your tab names need to be dynamic?

  • February 14, 2011 at 11:56 am

    #1286656

    If the tab names have to be dynamic in the Lili's script before the line

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

    define a variable that will hold current sheet name and use this variable instead of "TransactionDetails"

    Yelena

    Regards,Yelena Varsha

  • September 23, 2013 at 5:09 pm

    #1652516

    I am not sure what import I must setup. However I am getting errors "DTSGlobalVariables" not declared.

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

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