locked
SSRS to macro enabled excel workbook RRS feed

  • Question

  • Hi Gang,

    Is it possible to export a SSRS report to a macro enabled workbook. I would hate for the end user copy, paste and execue the macro every time the excel export needs to be saved.

    Thanks and regards,

    Monday, November 2, 2015 6:53 AM

Answers

  • Sorry Durbslaw but when you export your SSRS report it will be exported as a plain excel without any macros as the SSRS engine is designed for reporting and the excel export is just an additional tool with basic functionalities. 

    what is the macro used for? try to implement its functionality in the report itself so that it is exported in the desired way and no additional activities by the users are needed.

    Please provide more details about the macro.


    --------------------------------------------------------

    Surender Singh Bhadauria

    My Blog

     

    • Marked as answer by Durbslaw Friday, September 23, 2016 5:02 AM
    Monday, November 2, 2015 6:59 AM

All replies

  • Sorry Durbslaw but when you export your SSRS report it will be exported as a plain excel without any macros as the SSRS engine is designed for reporting and the excel export is just an additional tool with basic functionalities. 

    what is the macro used for? try to implement its functionality in the report itself so that it is exported in the desired way and no additional activities by the users are needed.

    Please provide more details about the macro.


    --------------------------------------------------------

    Surender Singh Bhadauria

    My Blog

     

    • Marked as answer by Durbslaw Friday, September 23, 2016 5:02 AM
    Monday, November 2, 2015 6:59 AM
  • WHats the functionality you're trying to implement through a macro? By default SSRS cannot embed macros to Excel. It just renders the data in plain Excel format

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, November 2, 2015 7:09 AM
  • Hi Thanks for the swift response, in a nutshell the reports are exported as text wraps and merged cells, I  manually unmerge and autofit, this creates empty rows in excel, I then use a Macro is to delete a row if the entire row is empty. 

    • Edited by Durbslaw Monday, November 2, 2015 7:21 AM Grammatical
    Monday, November 2, 2015 7:19 AM
  • Hi Thanks for the swift response, in a nutshell the reports are exported as text wraps and merged cells, I  manually unmerge and autofit, this creates empty rows in excel, I then use a Macro is to delete a row if the entire row is empty.
    • Edited by Durbslaw Monday, November 2, 2015 7:22 AM Grammatical
    Monday, November 2, 2015 7:19 AM
  • Hi Durbslaw,

    To avoid this you can Change the reports design and make sure that the rows are of same height this will avoid having unnecessary blank rows 

    currently there is no way of avoiding the merge issue if you are going to export as a single worksheet.

    a work around can be by implementing page breaks and pushing the next set of data to another sheet 


    --------------------------------------------------------

    Surender Singh Bhadauria

    My Blog

     

    Monday, November 2, 2015 11:14 AM
  • Hi Thanks for the swift response, in a nutshell the reports are exported as text wraps and merged cells, I  manually unmerge and autofit, this creates empty rows in excel, I then use a Macro is to delete a row if the entire row is empty.
    why not change the designer to give enough width to the cells and set autogrow to true. In that way you dont need to do the resizing yourselves

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, November 2, 2015 11:16 AM
  • Nope tried this, does not work!
    Monday, November 2, 2015 1:52 PM
  • Which version are you on. Because since SQL Server 2008 R2 this problem is nearly gone and in 2012 it is completely gone.

    While developing the report check the width of each column of your tablix or matrix and then on the basis of that set the width of the labels. along with that the distance between a label and the tablix should be a multiple of the row height of the tablix. the same is applicable even for the labels. this is what i had done to solve the problem when i was using 2005 and 2008. 

    if the heights, widths and distances are not set properly then empty rows and merged columns will appear.


    --------------------------------------------------------

    Surender Singh Bhadauria

    My Blog

     

    Tuesday, November 3, 2015 5:40 AM
  • Hi Surrender,

    I am using SQL2012 and the reports in SSRS render without an issue, the data is multi rowed and contained neatly/precisely  in each cell (no extra row or column space). However in Excel its screwy.

    Wednesday, November 4, 2015 4:46 AM