Book publications - Publishing to Excel - worksheet names

[former_member570917]

Hi guys,

I am working in the EPM plug-in in AO. SP number 2.6.100.78647.

I'm attempting to use Book Publications to publish to a Single Excel Workbook. I'm wondering if there's any way to change how the EPM Plug-In decides to name each worksheet, because currently it automatically uses a standard rule (that I'll explain below) which means that lots of sheets in the same Book would have the same worksheet name and so, as a result, they don't get added to the report... (the book publisher just says another sheet already has that name and so doesn't add it to the workbook).

To be fully clear, the book publisher gives each Excel sheet a name of the format:

"MAs_2_Total Adjusted (for manag"

Where:

• “MAs” was the original template’s worksheet name
• “2” is the section name in the book pub template
• “Total Adjusted (for manag” is a cutoff of a description of an AuditTrail ID (specifically "Total Adjusted (for management accounts)"; this section varies by AuditTrail and by BusinessUnit dimension in this example and it can't even get to the BU part as it's too long

So it has cutoff at the max Excel tab name length. This causes an issue as I might have many many BUs against the same AuditTrail, and after the first one it just says “Sheet name already exists, sheet not added” (or something to that extent) and doesn’t include those sheets!! Which is obviously bad news. So I’ve got the following issues:

• I would want to get rid of “MAs”, but obviously the original template needs a sheet name…
• You can’t have a blank section header, and can’t use the same section header twice in a book, so have just sequentially called them “1”,”2”,”3, etc – but this looks a bit rubbish
• It’s showing the description of both dimensions that are varying; would save me a lot of hassle if I could show just ID as theoretically this would work!! This would be the biggest win if anyone knew how
• You don’t seem to get an option ANYWHERE that actually lets you change how it names the sheet

Has anyone come across this issue before?

Thanks

Joe

[former_member186338 · Accepted solution]

Yes, it's not possible to change BPC logic on name creation.

[former_member186338]

It's not a huge job to replace Book Publication with vba code...

[former_member570917]

Yeah this was my backup plan.

Would prefer to keep it with native BPC functionality, just for maintainability reasons going forwards. But if no one replies with info on book publications (I can just imagine the answer is that what I want to do isn't possible using the out-of-box functionality), then I'll have to go down the VBA route.