XLSM resource on SSRS site (portal)
hello, For specifics reports too complex to build with SSRS, we have decided to build them using excel file (with macro) directly connected to the database. To deploy them to user, we have decided to upload them on SSRS (as resource). When I use an excel 2000 file, it's ok, I click on it, Excel is automatically open and I can execute macro inside my excel file. Nevertheless, excel2000 is limited (in number of columns and row) regarding data manipulated. For some reports, I've decided to save them in XLSM. In this case, Excel is also automatically open but when I try to launch macro, I have the message "a document with the name 'resource.aspx' is already open....". I don't know why and I don't understand why it's blocked. Moreover on excel 2000, I have just to activate "macro" but with the same file in excel 2007 with macro (xlsm) format, I have to activate "macro" and "links"...??? I've looked for workaround (SSRS / IE) but I don't find any reason why macro couldn't be activated and executed If someone can help me how I could execute macro on XLSM excel in that condition ? Stephane
April 10th, 2012 7:05am

Hi Stephane, In Reporting Services, a report cannot be exported to XLSM format directly from Business Intelligence Development Studio or Report Manager. If we want to render a report in XLSM format, we need to use the OfficeWriter Designer Save option to set the file format for the Excel report in the RDL. The file format options for Save to Disk are: Office 2003 exports to XLSOffice 2007 exports to XLSXOffice 2007 with macros exports to XLSM Note: Office 2007 with macros (XLSM, DOCM) is only available in OfficeWriter 4.1 and above. For the details information, please see: Saving and Deploying Your Excel ReportHow to export SSRS reports to Office 2007 Hopefully this helps. Regards, Mike Yin
Free Windows Admin Tool Kit Click here and download it now
April 15th, 2012 10:44pm

Hi Mike, Thank for your answer. In fact, I don't want to export an RDL report to XLSM. I have developed specific reports directly in excel using macros (XSLM file) with direct connection to the database (without using SSRS). The idea is to upload these excel files on the SSRS site (upload as resource in fact) to have only one place with all reports (RDL and excel) for the end-user. In that case, the user click on the excel file directly on SSRS site, it opens excel with the excel file inside. the issue comes when I try to activate macro in the excel file. I don't understand why I couldn't activate macro and I have the message "a document with the name 'resource.aspx' is already open...." What I don't understand is that if I do the same thing with an excel 2000 file, I don't have the issue and I could activate macro (but limited to 65000 lines). Regards Stephane
April 16th, 2012 3:41pm

I found the issue. it was due to the fact I used shapes (rectangles) on which I assigned macro (instead of using button). When assigning a macro on a shape, Excel record not only the name of the macro but also the name of the file on which the macro is located. When I click on the shape to launch the macro, it seems that, when this file is open by SSRS, Excel try to re-open the file. This issue doesn't appear if you use excel 2000 file or excel 2007 outside SSRS.
Free Windows Admin Tool Kit Click here and download it now
April 21st, 2012 2:22am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics