从网站下载 VBA 文件 - 弹出窗口

我正在尝试自动从网站下载文件。当我手动进行下载时,我所要做的就是单击“保存”图标(软盘),然后会弹出另一个窗口一秒钟并开始下载(同时弹出的窗口消失)。

我通常做的(当我自动下载时)是找到文件的 URL,然后我使用 URLDownloadToFile 函数。但在这种情况下,我无法在 html 中找到 url。我尝试在对象上使用 .clickFireEvent 但没有任何效果。

所以我开始认为(基于此站点中的类似问题)当我按下“保存”图标时脚本会生成 URL。不幸的是,我不熟悉 javascript 或它是如何工作的。现在,我正在尝试使用浏览器的开发人员工具控制台来确定单击对象时会发生什么。顺便说一句:这个对象是一个 <img> 对象。

我在网上搜索答案,我认为如果我想下载文件,我将不得不自己调用 javascript,例如 execScript 。但是,当我单击图标时,如何找出调用了哪个脚本?更重要的是,我是否能够在不完全了解网页的工作原理的情况下理解您的答案? :)

P.S.:我知道如果我能给你网站地址会容易得多,但它需要登录才能看到我在说的东西......

stack overflow VBA download file from website - popup window
原文答案

答案:

作者头像

假设您正在尝试下载图像 -
看看 How do I base64 encode a string efficiently using Excel VBA? 你会发现一个 Decode 方法。在 url 的 base64 部分使用它,即 &quot;data:image/png;base64,&quot; 之间的文本。将其另存为二进制文件,您就有了图像。

作者头像

这有帮助吗?

Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
  "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal _
    szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Sub DownloadFilefromWeb()
    Dim strSavePath As String
    Dim URL As String, ext As String
    Dim buf, ret As Long
    URL = Worksheets("Sheet1").Range("A2").Value
    buf = Split(URL, ".")
    ext = buf(UBound(buf))
    strSavePath = "C:UsersrshuellDesktopDownloads" & "DownloadedFile." & ext
    ret = URLDownloadToFile(0, URL, strSavePath, 0, 0)
    If ret = 0 Then
        MsgBox "Download has been succeed!"
    Else
        MsgBox "Error"
    End If
End Sub

或者....

Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
  "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal _
    szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Private Sub pMain()
  Dim sURL As String
  Dim sDestination As String
  Dim bSuccess As Boolean
  Dim lRow As Long
  Dim ws As Excel.Worksheet
  Dim strSavePath As String
  Dim URL As String, ext As String
  Dim buf, ret As Long

  'Change to suit
  Set ws = ThisWorkbook.Worksheets("Sheet1")

  With ws
    For lRow = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
      sURL = .Cells(lRow, "A")
      sDestination = .Cells(lRow, "B")

      buf = Split(sURL, ".")
      ext = buf(UBound(buf))

        pos = InStrRev(sURL, "/", -1)
        file = Mid(sURL, pos + 1, 99)
        strSavePath = sDestination & file
        ret = URLDownloadToFile(0, sURL, strSavePath, 0, 0)
            If ret = 0 Then
                .Cells(lRow, "C") = "File download successfully!"
            Else
                .Cells(lRow, "C") = "Couldn't download the file!"
            End If

      DoEvents
    Next lRow
  End With
End Sub

enter image description here