Forum Discussion

Thomas_Bennett's avatar
Thomas_Bennett
New Contributor III
2 years ago
Solved

Add a non CV Excel workbook to a book.

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

Books
  • JackLacava's avatar
    JackLacava
    2 years ago

    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.

  • Thomas_Bennett's avatar
    Thomas_Bennett
    New Contributor III
    2 years ago

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

     

    Cheers,

     

    Tom

  • JackLacava's avatar
    JackLacava
    Honored Contributor
    2 years ago
    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:

    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!

    • Thomas_Bennett's avatar
      Thomas_Bennett
      New Contributor III
      2 years ago

      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.  

       

      • JackLacava's avatar
        JackLacava
        Honored Contributor
        2 years ago

        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.