Possibly the following solution could be useful for others who is using SQL server Reporting Services as main Reporting Tool and Microsoft Access as front end for SQL server or even MDB.
I do not think it is the most efficient way to do that, so if you have any suggestions , that would be appreciated.
Anyway, say you have several reports in SQL server Reporting Services and you want to be able to export several of them and consolidate in one Excel file. I was doing it from Access vba.
The first approach could be to get the data directly from SQL and using vba export to Excel cell by cell and do the formatting after that
However, the beauty of Reporting Services that you can have quite complex report which you can export to Excel preserving all formatting, grouping and etc.
On of the interesting function of the SQL Reporting Services is that a client can request a report by its URL address.
I was trying to do it directly from access but could work it out , so I embedded report link inside excel file which i use as a template for my report.
which exports report into new excel file and opens it
last step is to access this file and copy formatted data from it and paste to my excel file.
As result, i have one excel file with well formatted several sheets one per each report.
I do not think it is the most efficient way to do that, so if you have any suggestions , that would be appreciated.
Anyway, say you have several reports in SQL server Reporting Services and you want to be able to export several of them and consolidate in one Excel file. I was doing it from Access vba.
The first approach could be to get the data directly from SQL and using vba export to Excel cell by cell and do the formatting after that
However, the beauty of Reporting Services that you can have quite complex report which you can export to Excel preserving all formatting, grouping and etc.
On of the interesting function of the SQL Reporting Services is that a client can request a report by its URL address.
I was trying to do it directly from access but could work it out , so I embedded report link inside excel file which i use as a template for my report.
Code:
myHyperLink = "http://Yourservername/Reportserver?/Reports/MyReport&Parameter01=" + CStr(Value01) + "&Parameter02=" + CStr(Value02) & "&rs:Command=Render&rs:Format=Excel"
xlApp.Workbooks(1).Sheets(1).Hyperlinks.Add
xlApp.Workbooks(1).Sheets(1).Range("B2"), myHyperLink
'next step is to get the report:
xlApp.Workbooks(1).Sheets(1).Range("B2").Hyperlinks(1).Followlast step is to access this file and copy formatted data from it and paste to my excel file.
As result, i have one excel file with well formatted several sheets one per each report.
