Excel VBA Code to pull SSRS data as a dataset?

Question

I can’t seem to find a solution for my problem no matter how much I Google. (Please excuse my verbosity in the following explanation) My process is that I have to go to the SSRS web page, run my report (with parameters), and finally export as an excel file. Once all that is done I can execute my couple thousand lines of VBA code to import the raw data I just downloaded and apply my business logic. Now I’m able to make My pretty charts and graphs. For lots of reasons I won’t enumerate here, I need to further automate this. What I would like to do is change my code so it will just pull the data directly into my Excel spreadsheet without having to have first saved it as a file. The Googling I have done so far suggests I would be pulling it into a dataset, then work with that dataset object to consume my data. Does anyone have VBA code that demonstrates how to connect to the SSRS server, call my specific report (with parameters) and, and return the results as ( I’m guessing as a dataset),and read that into my spreadsheet?

Answer 1

I would run into this problem all the time. It would be great to have code to pull directly from a data source into a dataset, but sometimes the simplest way is best. The basic thing I may try is to create an IE object and then navigate through the pages of the website you would find and run the report. Then, depending on the website, you may be able to scrape the data from the web page and create your own dataset, or maybe it would make you save a file - which you could do then open it and grab the data (through vba automation).

My point is, I would bypass going directly to the data source and use a Webbrowser object in VBA to navigate to the data I want. You can also set the visibility of the browser object so that users don't see the browser open and move around. Once the data is captured close the web site and set the browser object to nothing.

Of course it may or may not work depending on security and how the site is built. But it may be worth a try looking into how you can automate the process of grabbing your data through a web browser. - ralph