Page Numbers in Excel 1

[Rseven]

Hello ALl,
Can anyone tell me an easy way to rename the tabs in an Excel spreadsheet when a report is output to Excel. I rename the pages in the report pages section (Summary,Detail & RM) but when they get output to Excel it reads "Detail_1", "Summary_2", "RM_3", "RM_4", "RM_5", etc). It doesn't seem to restart the numbering when the page breaks. It would be okay if it read, "Detail_1", "Summary_1", "RM_1", "RM_2", etc.

Any help would be appreciated

Thank You!

 

[drlex]

AFAIK, the only way to do this is within Excel post-generation. Unfortunately I have to do this with the majority of reports produced, which means my macros are 20% Cognos and 80% Excel steps.

Option Explicit
Sub Main()

   Dim x   
   Dim StrReport as string
   Dim strprompt as string
   Dim objExcel as Object
   '
   strReport = "C:\myreport.xls"
   '
   Set objExcel = CreateObject("Excel.Application")
   objExcel.Visible = 1
   objExcel.Application.DisplayAlerts = False
      objExcel.Application.Workbooks.Open StrReport
         For x = 1 to objExcel.Workbooks(1).Sheets.Count
            strprompt = objExcel.Workbooks(1).Sheets(x).Name
            '<manipulate your string as required>
            '...
            objExcel.Workbooks(1).Sheets(x).Name =  strprompt
         Next x
      objExcel.Application.Workbooks(1).SaveAs strReport, Fileformat:=-4143
      objExcel.Application.Workbooks(1).Close   
   objExcel.Application.Quit        
   '
End Sub

soi la, soi carré

 

[Rseven]

Thanks very much, that worked just fine.

 

[drlex]

You're welcome; thanks for the star. Happy Friday!

soi la, soi carré