Click here to Skip to main content
13,447,710 members (52,020 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


8 bookmarked
Posted 29 Jul 2010

Rename Excel Sheets Generated from SSRS using Document Map

, 29 Jul 2010
Rate this:
Please Sign up or sign in to vote.
Rename Excel sheets generated from SSRS using document map

What Problem Does This Solution Solve?

IN SSRS, you can generate any multisheet Excel report containing document map as the first sheet. The limitation of SSRS is that it does not name sheets according to the document map. The sheet names are always default such as sheet1, sheet2, sheet3... In many of the forums, developers are looking for this solution. The article deals with rather a workaround to this problem.

How Does This Help Someone Else?

In projects involving SSRs reports, there often arises the need to rename Excel sheets according to the document map generated.

Document Map is the first sheet generated from SSRS which contains hyperlinks to different sheets in the Excel. For better readability of individual sheets, Excel sheets are renamed according to the document map. This approach is the simplest one considering it is configurable and easy to maintain.

How Does the Code Actually Work?

Here we have generated the scheduled SSRS report as an Excel file. The rename_excel utility renames all the sheets of this report, as per the names of the sheets in the document map, after which it mails it to the intended recipients. This utility is basically a console application which can also be scheduled after SSRS scheduler. I have tried to make this utility configurable using an XML file.

In the Main() function, after reading the configuration parameters from the config.xml, open the Excel sheet and change the hyperlink in the document map to link it to individual sheets.

Note: Excel sheet name supports less than 31 characters only. Sheet renaming is ignored if there are more than 31 characters. This limitation is taken care of in the code.

Once the sheets are renamed, save the Excel file as new Excel file and send the Excel as attachment to the intended recipients. Configure the SMTP server accordingly to send mails.

Using the Code

The zip file contains a class file RenameExcel.vb and a Configuration XML file Config.xml.

Config file elements:

  • Path: Path where the report is located
  • SaveTo: Path where the newly renamed Excel sheet will be saved
  • FileName: File name which you want to give
  • Row: Excel sheet Row number from where the document map starts
  • Column: Excel sheet column number from where the document map starts
  • ErrorLogfile: Log file which will write all the errors encountered, if any
  • SMTPServer: SMTP server IP which will be used to send mails
  • Subject: Subject of the mail
  • Body: Body of the mail
  • Body1: Extra body message if you need to add
  • MailTo: Recipients of the mail
  • MailFrom: Sender of the mail

Once you have configured according to your requirements, you can run the RenameExcel.exe.

You can also schedule the EXE.

Imports System.Web.Mail
Imports System.IO
Imports System.Xml
Imports Excel
Imports System.Text

Module RenameExcel

#Region "Declaration"
    Dim strErrorLog As String
    Dim strFileName As String = DateTime.Now().ToString("yyyyMMdd") + ".xls"
    Dim strSMTPServer, strSubject, strMailTo, strMailFrom, strBody As String

#End Region

#Region "Main"
    Sub Main()

        Dim xmlConfig As XmlTextReader
        Dim xmlMap As XmlTextReader
        Dim strPath, strSaveTo, strFile, strRow, strCol As String
        Dim strRange As String
        Dim strRangeArray As Array
        Dim xl As New Excel.ApplicationClass
        Dim xlBook As Excel.Workbook


            xmlConfig = New XmlTextReader("Config.xml")

            'Get the Config values from config file config.xml
            While Not xmlConfig.EOF
                If Not xmlConfig.IsStartElement Then
                    Exit While
                End If
                strPath = xmlConfig.ReadElementString("Path")
                strSaveTo = xmlConfig.ReadElementString("SaveTo")
                strFile = xmlConfig.ReadElementString("FileName")
                strRow = xmlConfig.ReadElementString("Row")
                strCol = xmlConfig.ReadElementString("Column")
                strErrorLog = xmlConfig.ReadElementString("ErrorLogFile")
                strSMTPServer = xmlConfig.ReadElementString("SMTPServer")
                strSubject = xmlConfig.ReadElementString("Subject") + _
			" " + DateTime.Now().ToString("MM/dd/yyyy")
                strBody = xmlConfig.ReadElementString("Body") + " " + _
                strBody = strBody + xmlConfig.ReadElementString("Body1")
                strMailTo = xmlConfig.ReadElementString("MailTo")
                strMailFrom = xmlConfig.ReadElementString("MailFrom")
            End While

            Dim oMissing As Object
            oMissing = System.Reflection.Missing.Value

            Dim xlSheet As Excel.Worksheet
            Dim rnRange As Range
            Dim strSheetName As String

            'open the excel sheet from the given path configured in config.xml
            xlBook = xl.Workbooks.Open(strPath + strFile, oMissing, _
		oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, _
		oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing)

            xlSheet = xlBook.Worksheets(1)
            rnRange = xlSheet.UsedRange()
            strRangeArray = rnRange.Value2

            Dim iRow, iColumn As Integer

            iRow = strRow
            iColumn = strCol

            Dim strColumn As String
            strColumn = Convert.ToChar(iColumn + 64)

            'Code to rename the excel sheets and editing the hyperlink
            For iRow = iRow To xlBook.Worksheets.Count
                strSheetName = strRangeArray(iRow, iColumn)
                If strSheetName.Length > 31 Then
                    strSheetName = strSheetName.Substring(0, 31)
                End If
                xlBook.Sheets(iRow).Name = strSheetName.Split("/").GetValue(0)
                xlSheet.Range(strColumn + _
		iRow.ToString()).Hyperlinks(1).SubAddress = "'" & _
		xlBook.Sheets(iRow).Name & "'!A1"


            strFileName = strSaveTo + strFile + "_" + strFileName

            'Check if the daily report is already created 
            If FileExists(strFileName) Then
                Dim fiFile As New FileInfo(strFileName)
            End If

            'save the new daily report generated at the location 
            'mentioned in the config file config.xml

            'Send mail with attachment to users configured in the config.xml
            SendMailOneAttachment(strMailFrom, strMailTo, _
		strSubject, strBody, strFileName, "", "", strSMTPServer)

        Catch ex As Exception
        End Try
    End Sub

#End Region

#Region "SendMailOneAttachment: Send the mail to users configured in the 
 onfig.xml along with attachment "

    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' Send the mail to users configured in the config.xml
    ''' also send the daily report generated as new attachment 
    ''' </summary>
    ''' <param name="From"></param>
    ''' <param name="sendTo"></param>
    ''' <param name="Subject"></param>
    ''' <param name="Body"></param>
    ''' <param name="AttachmentFile"></param>
    ''' <param name="CC"></param>
    ''' <param name="BCC"></param>
    ''' <param name="SMTPServer"></param>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    ''' 	[harshit]	7/13/2010	Created
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Sub SendMailOneAttachment(ByVal From As String, _
      ByVal sendTo As String, ByVal Subject As String, _
      ByVal Body As String, _
      Optional ByVal AttachmentFile As String = "", _
      Optional ByVal CC As String = "", _
      Optional ByVal BCC As String = "", _
      Optional ByVal SMTPServer As String = "")

        Dim myMessage As MailMessage
        Dim oAttach As System.Web.Mail.MailAttachment
            myMessage = New MailMessage

            With myMessage
                .To = sendTo
                .From = From
                .Subject = Subject
                .Body = Body
                .BodyFormat = MailFormat.Text
                'CAN USER MAILFORMAT.HTML if you prefer

                If CC <> "" Then .Cc = CC
                If BCC <> "" Then .Bcc = ""

                If FileExists(AttachmentFile) Then _
                 .Attachments.Add(New System.Web.Mail.MailAttachment(strFileName))

            End With

            If SMTPServer <> "" Then _
               SmtpMail.SmtpServer = strSMTPServer

        Catch ex As Exception
        End Try

    End Sub
#End Region
<?xml version="1.0" encoding="utf-8" ?> 
	<Subject>Report for</Subject>
	<Body>Attached is the Report for</Body> 
	<Body1>Please respond to this email if you have any questions.
Thank you


  • 30th July, 2010: Initial post


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

India India
No Biography provided

You may also be interested in...


Comments and Discussions

GeneralMy vote of 1 Pin
peymanshams7-Dec-10 9:48
memberpeymanshams7-Dec-10 9:48 
GeneralArticle Problems Pin
Gil Fink27-Jul-10 20:10
memberGil Fink27-Jul-10 20:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01-2016 | 2.8.180318.3 | Last Updated 30 Jul 2010
Article Copyright 2010 by harshitp
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid