?
Solved

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

Posted on 2009-02-11
11
Medium Priority
?
589 Views
Last Modified: 2013-11-05
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
0
Comment
Question by:MIKE
  • 5
  • 5
11 Comments
 
LVL 2

Accepted Solution

by:
sillversnake earned 2000 total points
ID: 23618073
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
 
LVL 17

Author Comment

by:MIKE
ID: 23622837
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
 

Expert Comment

by:Gav-B
ID: 23735174
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 17

Author Comment

by:MIKE
ID: 23909481
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
 

Expert Comment

by:Gav-B
ID: 23909938
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
 
LVL 17

Author Comment

by:MIKE
ID: 23909953
no worries...I found the answer...thanks anyway...
0
 

Expert Comment

by:Gav-B
ID: 23910502
So have you got it working?
0
 
LVL 17

Author Comment

by:MIKE
ID: 23910838
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
 

Expert Comment

by:Gav-B
ID: 23917050
Post a link to that question in here...
0
 

Expert Comment

by:Gav-B
ID: 23957982
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
 
LVL 17

Author Comment

by:MIKE
ID: 23958217
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question