excel - 通过excel中的宏从URL下载和重命名文件

Question

我需要下载 Dropbox 图像链接列表，并想用新名称重命名文件。

我想要实现的是，如果我将新文件名放在一个单元格中，并将链接放在另一个单元格中，它应该下载文件，重命名并保存在文件路径中提到的文件路径以保存。如果下载图像时出现任何错误，状态将在状态单元格中更新。

例如:



我将在 MS Office Professional plus 2016 64 位 Win 10 上使用此宏。

我正在尝试下面的宏，但它没有保存图像:

Option Explicit

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

Dim Ret As Long

'~~> This is where the images will be saved. Change as applicable
Const FolderName As String = "C:\Temp\"

Sub Sample()
    Dim ws As Worksheet
    Dim LastRow As Long, i As Long
    Dim strPath As String

    '~~> Name of the sheet which has the list
    Set ws = Sheets("Sheet1")

    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To LastRow '<~~ 2 because row 1 has headers
        strPath = FolderName & ws.Range("A" & i).Value & ".jpg"

        Ret = URLDownloadToFile(0, ws.Range("B" & i).Value, strPath, 0, 0)

        If Ret = 0 Then
            ws.Range("C" & i).Value = "File successfully downloaded"
        Else
            ws.Range("C" & i).Value = "Unable to download the file"
        End If
    Next i
End Sub

任何帮助将不胜感激。

Answer 1

可能是 64 位函数声明的问题。
尝试

#If Win64 Then
    Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
   (ByVal pCaller As LongPtr, _
    ByVal szURL As String, _
    ByVal szFileName As String, _
    ByVal dwReserved As Long, _
    ByVal lpfnCB As LongPtr) As LongPtr
#Else
    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
#End If

更新 :如果下载失败，函数返回错误码。但是，如果文件可以下载但不能在本地写入(例如文件名无效、没有写入权限...)，则该函数返回 0。