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

EN

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:\Users\rshuell\Desktop\Downloads\" & "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