Excel VBA - Exporting and saving SSRS report

Question

Looking for help with pulling an SSRS report from an internal company site using VBA. I've read several posts on VBA automation and have been managed to successfully make all the selections and run the report, now I just need the data.

Specifically, I need help to select the export to excel option.

My current VBA code:

Sub macro_07()
Dim appIE As InternetExplorerMedium
'Set appIE = Nothing
Dim objElement As Object
Dim objCollection As Object
Dim objCollection2 As Object
Dim objCollection3 As Object
Dim objCollection4 As Object
Dim objCollection5 As Object
Dim objCollection6 As Object
Dim objCollection7 As Object
Dim objCollection8 As Object

Set appIE = New InternetExplorerMedium
sURL = ThisWorkbook.Sheets("Control").Range("IALinks").Cells(1, 1).Value
With appIE
    .Navigate sURL
    .Visible = True
End With

Do While appIE.Busy Or appIE.readyState <> 4
    DoEvents
Loop

Set objCollection = appIE.document.getElementById("ctl31_ctl04_ctl07_txtValue")
objCollection.Value = Range("YearSlct").Value
Set objCollection2 = appIE.document.getElementById("ctl31_ctl04_ctl03_ddValue")
objCollection2.selectedIndex = 4
objCollection2.FireEvent "onchange"
Application.Wait (Now + TimeValue("0:00:002"))
Set objCollection3 = appIE.document.getElementById("ctl31_ctl04_ctl05")
objCollection3.Click
Set objCollection4 = appIE.document.getElementById("ctl31_ctl04_ctl05_divDropDown_ctl01")
objCollection4.Focus
objCollection4.Checked = "checked"
Set objCollection5 = appIE.document.getElementById("ctl31_ctl04_ctl05_divDropDown_ctl03")
objCollection5.Focus
objCollection5.Checked = "checked"
objCollection5.FireEvent "onchange"

Set objCollection6 = appIE.document.getElementById("ctl31_ctl04_ctl00")
objCollection6.Click
Application.Wait (Now + TimeValue("0:00:002"))

Set objCollection7 = appIE.document.getElementById("ctl31_ctl06_ctl04_ctl00_Button")
objCollection7.Click


  Set appIE = Nothing
End Sub

This gets me to the point of selecting the export button but I can't figure out how to select the excel option.

Export Menu:

Here is the HTML code from the site:

<table style="display:inline;" cellspacing="0" cellpadding="0">
                        <tbody><tr>
                            <td height="28"><div class="HoverButton" id="ctl31_ctl06_ctl04_ctl00">
                                <table title="Export" id="ctl31_ctl06_ctl04_ctl00_Button" border="0">
                                    <tbody><tr>
                                        <td><a id="ctl31_ctl06_ctl04_ctl00_ButtonLink" style="cursor: pointer;" href="javascript:void(0)"><img id="ctl31_ctl06_ctl04_ctl00_ButtonImg" style="border-style:None;height:16px;width:16px;border-width:0px;" src="/Reports/Reserved.ReportViewerWebControl.axd?OpType=Resource&amp;Version=10.50.1600.1&amp;Name=Microsoft.Reporting.WebForms.Icons.Export.gif"><img id="ctl31_ctl06_ctl04_ctl00_ButtonImgDown" style="border-style:None;height:6px;width:7px;border-width:0px;margin-bottom:5px;margin-left:5px;" src="/Reports/Reserved.ReportViewerWebControl.axd?OpType=Resource&amp;Version=10.50.1600.1&amp;Name=Microsoft.Reporting.WebForms.Icons.ArrowDown.gif"></a></td>
                                    </tr>
                                </tbody></table>
                            </div><div class="MenuBarBkGnd" id="ctl31_ctl06_ctl04_ctl00_Menu" style="padding: 1px; display: block; visibility: visible; position: absolute;">
                                <div class="HoverButton">
                                    <a class="ActiveLink" style="padding: 3px 8px 3px 32px; text-decoration: none; display: block; white-space: nowrap;" onclick="$find('ctl31').exportReport('XML');" href="javascript:void(0)">XML file with report data</a>
                                </div><div class="DisabledButton">
                                    <a class="ActiveLink" style="padding: 3px 8px 3px 32px; text-decoration: none; display: block; white-space: nowrap;" onclick="$find('ctl31').exportReport('CSV');" href="javascript:void(0)">CSV (comma delimited)</a>
                                </div><div class="DisabledButton">
                                    <a class="ActiveLink" style="padding: 3px 8px 3px 32px; text-decoration: none; display: block; white-space: nowrap;" onclick="$find('ctl31').exportReport('PDF');" href="javascript:void(0)">PDF</a>
                                </div><div class="DisabledButton">
                                    <a class="ActiveLink" style="padding: 3px 8px 3px 32px; text-decoration: none; display: block; white-space: nowrap;" onclick="$find('ctl31').exportReport('MHTML');" href="javascript:void(0)">MHTML (web archive)</a>
                                </div><div class="DisabledButton">
                                    <a class="ActiveLink" style="padding: 3px 8px 3px 32px; text-decoration: none; display: block; white-space: nowrap;" onclick="$find('ctl31').exportReport('EXCEL');" href="javascript:void(0)">Excel</a>
                                </div><div class="DisabledButton">
                                    <a class="ActiveLink" style="padding: 3px 8px 3px 32px; text-decoration: none; display: block; white-space: nowrap;" onclick="$find('ctl31').exportReport('IMAGE');" href="javascript:void(0)">TIFF file</a>
                                </div><div class="DisabledButton">
                                    <a class="ActiveLink" style="padding: 3px 8px 3px 32px; text-decoration: none; display: block; white-space: nowrap;" onclick="$find('ctl31').exportReport('WORD');" href="javascript:void(0)">Word</a>
                                </div>
                            <div style="left: 0px; top: 0px; width: 26px; height: 149px; filter: none; position: absolute; z-index: -1; opacity: 0.05; background-color: black;"></div></div></td>
                        </tr>
                    </tbody></table>

Since you're new to Stack Overflow a tip: ask only ONE question per question, otherwise it could be closed as "too broad". You may want to take a moment to read how to effectively ask questions on the site in the [help]. You can use the [edit] link to modfiy the question. — Cindy Meister, Jun 13 '18 at 11:55
Build up a collection of `ActiveLink` classes, then iterate to find the one with Excel as the innertext and click it. — Ryan Wildry, Jun 13 '18 at 17:02
Thanks Ryan. I wasn't sure how to do this but found the answer in a prior question you answered. — Martin Smith, Jun 13 '18 at 18:43

score 0 · Answer 1 · answered Jun 13 '18 at 14:49

0

Set up a subscription to the report and Report Server will do the export to excel to a fileshare for you.

Alternately you might want to investigate the database query behind the report and (re)create the report into Excel.

https://learn.microsoft.com/en-us/sql/reporting-services/subscriptions/subscriptions-and-delivery-reporting-services?view=sql-server-2017

answered Jun 13 '18 at 14:49

JonTout

618
6
14

Thanks for your input. Unfortunately, I only have access to pull reports via the browser which is why I'm trying to use IE Automation. I'll check with the IT team on these options but am looking for something I can accomplish with my current access. – Martin Smith Jun 13 '18 at 15:48
The other "easy" option (if Excel is allowed to import data from a web page) is to open Excel, new workbook, Click on data tab, Select "From Web" and paste in Report URL and follow the wizard Try recording a macro with the above steps once you've got a handle on how to do them. – JonTout Jun 13 '18 at 16:53
Thanks. That is where I started first. I am getting some traction on subscription solution you suggested. – Martin Smith Jun 13 '18 at 18:44

score 0 · Answer 2 · answered Jun 14 '18 at 23:30

Thanks all for the notes. I was able to find a solution following Ryan's suggestion and checking out an answer he provided on a prior post (Get all innertext VBA)

From there it was a simple loop:

    For Each Element In Elements
        If Element.innerText = "Excel" Then Element.Click
    Next

Excel VBA - Exporting and saving SSRS report

2 Answers2