How to "Name" the TABS in EXCEL when Exporting from SQL Server Reporting Services..?

Experts:

I have a SQL RS report that I'm exporting into Excel.  The export works and actually is exporting my GROUPS into separate Excel Worksheet Tabs. This is good, however I would like to be able give each TAB a distinct NAME based on the GROUP.

My Group is actually AGENTNAME, and so I would like the AGENT NAME to appear on each TAB when exported to Excel.

How can I accomplish this?

thanks
M
LVL 17
MIKESoftware Solutions ConsultantAsked:
Who is Participating?
 
sillversnakeConnect With a Mentor Commented:
This worked for me in a quick test:

A one-line solution to be applied AFTER each page is exported... ASSUMING you have the agent's name already in a cell of the exported sheet.  In this example, the agent's name would have to already be located in cell A1:

    ActiveSheet.Name = Range("A1")

A longer version is posted below:
Sub RenTab1()
    Range("A1").Select    'A1 is where the agent name currently resides
    Selection.Copy        'copies agent name to clipboard
    Sheets("Sheet1").Select      'selects first sheet in a new workbook
    Sheets("Sheet1").Name = Range("A1")     'renames sheet with A1 data
    Application.CutCopyMode = False    
End Sub

Open in new window

0
 
MIKESoftware Solutions ConsultantAuthor Commented:
I was able to export a MAP SHEET from RS to Excel that contains a simply listing of AGENT NAMES that I can click on and it will take me to their respective Excel TAB....I did this by using some options in the Group Formatting inside my RS report....I think it will work for now.

I do like your idea and so I may use it as well.

Thank you for the input.

M
0
 
Gav-BCommented:
I like that solution too but...
Could it be worked into an SSIS script task (or something..) to enable automation?

The Holy grail would be reports being schedualled and auto emailed to clients with the multiple named tabs, and no input from the developer (me) at all.

- Gav B.
Business Reporting Analyst
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
MIKESoftware Solutions ConsultantAuthor Commented:
I know this is an old question,...but WHERE do I put this CODE in order to have the TEXT NAME in cell A6 appear as the TAB NAME...?

Incidentally,...I have about 90 tables......    : (
0
 
Gav-BCommented:
In Excel hit [Alt] + [F11] to open up a whole new world called VBA...
It should be pasted into a module which you could call, for example, "RenameTabsWithA1Value" to be nice and descriptive :)

I'm not too hot on VBA though and although I can understand what that code is saying I couldn't instantly implement it...
I'm a bit busy with other things right now (custom SSRS Schedules...) but I might give it a go soon and post back.

VBA is a good thing to be able to grasp, but if you want to get deeper into programming don't spend too much time learning it and skip to VB.NET (as I will be doing)
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
no worries...I found the answer...thanks anyway...
0
 
Gav-BCommented:
So have you got it working?
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Yes its working, I now have an open question on if its possible to have SQL SERVER REPORTING SERVICES (VISUAL BASIC) code this as part of the actual EXPORT into EXCEL.

But as far as getting the TABS to be named according to what is in Cell A6 for each worksheet...yes...simple code works....
0
 
Gav-BCommented:
Post a link to that question in here...
0
 
Gav-BCommented:
I believe I have a some better VBA code for this solution, please see Code Snippet.

This allows for error handling the fact that Tabs can only be named with 31 characters.

- Gav B.
Business Reporting Analyst
Sub RenameTabs_v1()
' Rename all worksheet tabs with each worksheet's cell A1 contents.
'   If Cell A1 has no content then the tab will not be renamed.
'   If Cell A1 has more than 31 charaters then the sheet will be renamed with the first 31 characters.
'   If Cell A1 contains any of the following 7 characters Excel will return an error;
' / \ : * ? [ or ]
Dim i As Integer
For i = 1 To Sheets.Count
    If (Len(Worksheets(i).Range("A1").Value) < 32) Then
        If (Len(Worksheets(i).Range("A1").Value) > 0) Then
            Sheets(i).Name = Worksheets(i).Range("A1").Value
        End If
    Else  Sheets(i).Name = Left(Worksheets(i).Range("A1").Value, 31)
    End If
Next
End Sub

Open in new window

0
 
MIKESoftware Solutions ConsultantAuthor Commented:
This has been working fine. But I'll make reference of your comment. Thanks

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_24238190.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.