时间戳

首页 > 解决方案 > 如何通过excel VBA在IE11中浏览PDF时单击下载按钮

excel - 如何通过excel VBA在IE11中浏览PDF时单击下载按钮

问题描述

Sub Drop_Down() Dim objIE As Object, ele As Object, opt As Object Set objIE = CreateObject("InternetExplorer.Application")

objIE.Visible = True
objIE.navigate "https://isgs-oas.isgs.illinois.edu/reports/rwservlet?oil_permit_activity"

Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

Set ele = objIE.document.getElementsByTagName("INPUT")

For Each opt In ele
    If opt.getAttribute("name") = "p_YEAR" Then
        opt.Focus
        opt.Value = "2018"
        Exit For
    End If
Next opt

Set ele = objIE.document.getElementsByTagName("select")

For Each opt In ele
    If opt.getAttribute("name") = "p_MONTH" Then
        opt.Focus
        opt.Value = "January"
        Exit For
    End If
Next opt
objIE.document.forms(0).submit
Do While objIE.Busy: DoEvents: Loop

'请在这里帮忙'想立即下载PDF文件

结束子

标签： excelvbaautomationie-automation

解决方案

我通过循环数月和数年并将它们转换为字符串来构建 URL，然后将其连接到 URL 的基础。我猜你想从哪一年开始，你会在“For year =”声明中看到。

PDF 列表

Sub DownloadFile()
Dim WinHttpReq As Object
Dim oStream As Object
Dim myURL As String
Dim LocalFilePath As String
Dim month As String
Dim year As Integer
Dim monthNo As Integer

For year = 2010 To 2018
    For monthNo = 1 To 12
        month = MonthName(monthNo)
            myURL = "https://isgs-oas.isgs.illinois.edu/reports/rwservlet?hidden_run_parameters=oil_permit_activity&p_MONTH=" & month & "&p_YEAR=" & CStr(year)
            LocalFilePath = Environ("USERPROFILE") & "\Desktop\rwservlet\oil_permit_activity_" & month & "_" & CStr(year) & ".pdf"

                Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
                WinHttpReq.Open "GET", myURL, False, "", ""  '("username", "password")
                WinHttpReq.send

                If WinHttpReq.Status = 200 Then
                    Set oStream = CreateObject("ADODB.Stream")
                    oStream.Open
                    oStream.Type = 1
                    oStream.Write WinHttpReq.responseBody
                    oStream.SaveToFile LocalFilePath, 2 ' 1 = no overwrite, 2 = overwrite
                    oStream.Close
                End If
    Next monthNo
Next year
End Sub

上面的代码对我有用，但你必须确保文件夹“rwservlet”存在于你的桌面上，否则它会抛出一个错误（我不擅长错误处理，但我们都在学习）。否则，您可以更改 LocalFilePath 字符串。

推荐阅读