Skip to main content
T
Thomas_Bennett
Newcomer
October 16, 2023
Solved

Add a non CV Excel workbook to a book.

  • October 16, 2023
  • 2 replies
  • 0 views

Hi all. So I have a book set up that creates an excel workbook based on a loop of entities. It works absolutely fine, however, the book is 300 worksheets or so. Therefore its pretty handy to have an Index page. Is there anyway to splice a worksheet not generated from the CV into this export? 

Also, books seem to forego the handy auto freeze pane on headers that excel exports do on execution. Does anyone have a BR that will do this?

Thanks in advance!

 

Tom

Best answer by JackLacava

I'm just going by the documentation (not a book expert).

"Output Name: Type a name for the file when creating a Zip book. If the .pdf extension is included for a Word or Excel file, the file will be converted into a PDF. This property is optional and does not apply to PDF books."

That would indicate to me that your output name must contain the file extension.

However, if your book is an excel one, there is this addition:

NOTE: Report and File items are not supported by Excel books and are ignored if added.

... which sadly is your case, I believe.

2 replies

J
OneStream Employee
JackLacavaOneStream Employee
OneStream Employee
October 18, 2023
Thomas_Bennett wrote:

Therefore its pretty handy to have an Index page. Is there anyway to splice a worksheet not generated from the CV into this export? 

Not midway through the loop, but you can add a generic file before or after the loop:

JackLacava_0-1697619134183.png

If it needs to reference the same member expansion, you could automatically generate it somewhere else (like another book, or a business rule triggered by Data Management or Dashboard) in a known location, so that this book will just pick it up.

Also, books seem to forego the handy auto freeze pane on headers that excel exports do on execution.

I think this would do for an excellent IdeaStream post. It's a feature quite a few people have asked for, over the years, but always seems to drop by the wayside. Addressing it with a BR is not possible using Cube View Extenders, you would have to use a post-processing event handler or DM job - and even then, the rule would have to use hairy DevExpress functionality that is better left to developers. So feel free to post it there!

T
Thomas_BennettAuthor
Newcomer
October 18, 2023

Thanks Jack! Shame the Freeze pane option isnt already in OS. You could assume book functionality is a lower priority than system reports like CVs and Dashboards but they are so handy as accountants loooove Excel! I guess for now its trusty VBA!

 

As for the index page. Where am I going wrong? I have no index page in my report nor as I assumed incorrectly did it generate separately.  

Thomas_Bennett_0-1697620581892.png

 

J
OneStream Employee
JackLacavaOneStream EmployeeAnswer
OneStream Employee
October 18, 2023

I'm just going by the documentation (not a book expert).

"Output Name: Type a name for the file when creating a Zip book. If the .pdf extension is included for a Word or Excel file, the file will be converted into a PDF. This property is optional and does not apply to PDF books."

That would indicate to me that your output name must contain the file extension.

However, if your book is an excel one, there is this addition:

NOTE: Report and File items are not supported by Excel books and are ignored if added.

... which sadly is your case, I believe.

    T
    Thomas_BennettAuthor
    Newcomer
    October 19, 2023

    Shame about that, Not the end of the world though. I can add the finishing touches and the page with VBA.

     

    Cheers,

     

    Tom

      Terms & ConditionsAccessibility statement