使用存在导出下拉菜单的VBA将IE报告导出到Excel

Question

我正在使用 vba 从我们的报表服务器中提取特定于日期的报告。我能够通过在 URL 中输入日期参数来绕过日期选择器。下一步是将报表导出到 excel。它有一个下拉菜单来选择文件类型。如何获取代码以从下拉列表中选择？我已经尝试了下面代码的许多变体。

    Sub Report()
    Dim ie As Object
    Application.ScreenUpdating = False
    Set ie = CreateObject("Internetexplorer.Application")
    ie.Visible = True
    ie.navigate "http://reportserver.comp.local/ReportServer/Pages/ReportViewer.aspx?%2fOperations%2fAR%2fCash+Receipts&StartDate=2017-01-07&EndDate=2017-01-13"
    ie.document.getelementbyid("ReportViewerControl_ctl05_ctl04_ctl00_ButtonImgDown").Click
    ie.document.getelementbyid("ReportViewerControl_ctl05_ctl04_ctl00_Menu").Click
    ie.document.getelementbyid("ReportViewerControl_ctl05_ctl04_ctl00_Button").Value = "Excel"

Answer 1

您需要

浏览下拉选项并更改您所追求的特定选择的selectedIndex。请尝试以下操作：

Option Explicit
Sub Report()
    Dim ie As Object, oSelection As Object, i As Long
    Application.ScreenUpdating = False
    Set ie = CreateObject("Internetexplorer.Application")
    If ie Is Nothing Then Exit Sub
    With ie
        .Visible = True
        .navigate "http://reportserver.comp.local/ReportServer/Pages/ReportViewer.aspx?%2fOperations%2fAR%2fCash+Receipts&StartDate=2017-01-07&EndDate=2017-01-13"
        ' I am guessing this is the right id check page source code "<select id='...'"
        Set oSelection = .document.getelementbyid("ReportViewerControl_ctl05_ctl04_ctl00_Menu")
        If Not oSelection Is Nothing Then
            For i = 0 To oSelection.all.Length - 1
                If InStr(1, oSelection.all(i), "Excel", vbTextCompare) > 0 Then
                    oSelection.selectedIndex = i
                    Exit For
                End If
            Next
            Set oSelection = Nothing
        End If
        .Quit
    End With
    Set ie = Nothing
    Application.ScreenUpdating = True
End Sub