How to Name Sheets in Exported Excel Workbooks with SSRS 2008 R2

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Stan Kulp » How to Name Sheets in Exported Excel...

13 posts, Page 1 of 2

1 2 »»

How to Name Sheets in Exported Excel Workbooks with SSRS 2008 R2

Rate Topic

Display Mode

Topic Options

Author

Message

Stan Kulp-439977

Posted Monday, August 22, 2011 12:01 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 10:00 AM
Points: 78, Visits: 597

Comments posted to this topic are about the item How to Name Sheets in Exported Excel Workbooks with SSRS 2008 R2

Post #1163112

skanker

Posted Monday, August 22, 2011 2:05 AM

Say Hey Kid

Group: General Forum Members
Last Login: Today @ 1:57 AM
Points: 693, Visits: 1,022

Thanks for this article. It will come in useful as this was a report feature that we were asked for when we developed a particular report using 2005. We have just upgraded to 2008 r2 so I can revisit the report and win a few brownie points. Wink

I have been struggling to find things in the 2008 - although there is more to change in properties I am finding that it takes a little longer to find where everything is set.

Does anyone know if there is a way of automaticlaly naming a report when a user exports to pdf or excel?

Tim

Post #1163142

mannaggia

Posted Monday, August 22, 2011 7:40 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 4:07 AM
Points: 25, Visits: 227

tim.kay (8/22/2011)

Does anyone know if there is a way of automaticlaly naming a report when a user exports to pdf or excel?

No - that's what we really would like.

I logged a suggestion at connect.microsoft.com, but they closed it with a "By Design" status.

https://connect.microsoft.com/SQLServer/feedback/details/679029/ssrs-should-vary-the-export-filename-or-allow-it-to-be-set-at-runtime

We run into a problem because if a user exports a report to Excel, leaves Excel open, then changes a parameter in the report and exports again, it exports the report with the same name each time (Report.xls).

It reports an error: "A document with the name 'Report(1).xls' is already open. You cannot open two documents with the same name, even if the documents are in different folders. To open the second document, either close the document that's currently open, or rename one of the documents."

With Excel 2010, this often locks up Excel and it has to be killed with Task Manager. It has an OK button in the error message, but you can't click it to dismiss it.

I guess Microsoft is saying they designed it to lock up like that...

Post #1163285

skanker

Posted Monday, August 22, 2011 7:52 AM

Say Hey Kid

Group: General Forum Members
Last Login: Today @ 1:57 AM
Points: 693, Visits: 1,022

Thanks for the reply mannaggia.

I had a feeling that this might be the answer but thought best to ask in case anything has changed.

I was looking for a easier way for some of our report users to save a bit of time as they need to save a particular report to be sent to different organisations. The reports are sent securely using a set referencing format for each organisation. We are not allowed (at this point in time) to allow the external organisation to access the reports direct, though this will hopefully change in future once powers that be allow it to happen. The joys of bureaucracy......

Tim

Post #1163292

MaricopaJoe

Posted Monday, August 22, 2011 11:11 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:40 PM
Points: 49, Visits: 232

I've been using this, but found that SSRS limits the sheet name to very small name. Reality is that most users want a date and the measure name or some sort of combination, but the most SSRS will support is 'Sheet Name 1".
Odd that excel supports a longer name, even programmatically via vba.

C'mon Microsoft..finish it. Just like the rest of the Dundas product, we will have to wait for SQL 19 to see it fully implemented.

JOE FEIGELMAN | Database Developer | Insight | www.Insight.com
t. 480.409.6666 c. 623.428.9421 e. Joe.Feigelman@insight.com

Post #1163499

HABK24

Posted Wednesday, November 30, 2011 10:28 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 7:53 AM
Points: 4, Visits: 18

I'm using SQL 2008 (not R2) ... is there any known method of naming each w/s in an exported Excel file in similar fashion with what R2 permits?

Bob

Post #1214122

Stan Kulp-439977

Posted Wednesday, November 30, 2011 10:54 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 10:00 AM
Points: 78, Visits: 597

No. R2 is when Microsoft added the ability to name sheets.

Here is a macro you can use to do it after the Excel file has been generated, but it is hardly a good solution.

http://stackoverflow.com/questions/736918/how-to-get-named-excel-sheets-while-exporting-from-ssrs

Put the tab name on the page header or group TableRow1 in your report so that it will appear in the "A1" position on each Excel sheet. Then run this macro in your Excel workbook.

Sub SelectSheet()
For i = 1 To ThisWorkbook.Sheets.Count
mysheet = "Sheet" & i
On Error GoTo 10
Sheets(mysheet).Select
Set Target = Range("A1")
If Target = "" Then Exit Sub
On Error GoTo Badname
ActiveSheet.Name = Left(Target, 31)
GoTo 10
Badname:
MsgBox "Please revise the entry in A1." & Chr(13) _
& "It appears to contain one or more " & Chr(13) _
& "illegal characters." & Chr(13)
Range("A1").Activate
10
Next i
End Sub

Post #1214139

HABK24

Posted Wednesday, November 30, 2011 11:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 7:53 AM
Points: 4, Visits: 18

You are correct ... for my current report, this is not a viable solution .

Nonetheless, thank you.

Bob

Post #1214165

Lisa Slater Nicholls

Posted Friday, March 30, 2012 9:44 AM

Old Hand

Group: General Forum Members
Last Login: Saturday, May 18, 2013 1:35 PM
Points: 301, Visits: 473

Actually there is such a method -- and I've shown it here -- http://spacefold.com/lisa/post/2007/10/03/Changing-the-Sheet-names-in-SQL-Server-RS-Excel-QnD-XSLT.aspx -- and there are lots of followup posts on my blog because people seem very interested in this technique.

But you may not like it.

Basically the idea is to use XML versus Excel as your output format and use XSLT to transform the XML -> SSML (Excel's 2003 XML dialect). It's quite easy, and you can save the XSLT information with your report definition so it is applied whenever XML is exported, it's not a separate option the user has to pick. The whole configuration and dev process is spelled out as a walkthrough in that post.

Of course, in later posts, I've let people know that in R2 this is no longer necessary *IF* the only thing you care about changing in Excel is the sheet names -- but it's still a great technique to know, because there's *lots* more you might want to alter in the default Excel presentation that SSRS provides.

>L<

Post #1275863

Stan Kulp-439977

Posted Friday, March 30, 2012 9:57 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 10:00 AM
Points: 78, Visits: 597

Thanks. A good one to know.

Post #1275876

« Prev Topic | Next Topic »

13 posts, Page 1 of 2

1 2 »»

Permissions