将 32 位代码转换为 64 位

Question

我有很多图像需要下载到我的文件夹中并重命名。我已经尝试了下面的宏，但它不适用于 32 位 excel，请帮助我在 64 位上工作。

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

如果需要同时适用于 32 位和 64 位的导入函数，则需要在声明中使用编译器指令。

Option Explicit
#If VBA7 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 Long
    Private Declare PtrSafe Function DeleteUrlCacheEntry Lib "Wininet.dll" _
      Alias "DeleteUrlCacheEntryA" ( _
        ByVal lpszUrlName As String _
      ) As Long
#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
    Private Declare Function DeleteUrlCacheEntry Lib "Wininet.dll" _
      Alias "DeleteUrlCacheEntryA" ( _
        ByVal lpszUrlName As String _
      ) As Long
#End If
Public Const ERROR_SUCCESS As Long = 0
Public Const BINDF_GETNEWESTVERSION As Long = &H10
Public Const INTERNET_FLAG_RELOAD As Long = &H80000000
Public Const folderName As String = "c:temp"
Sub downloadImages()
    Dim i As Long, ret As Long, sWAN As String, sLAN As String
    
    With Worksheets("Sheet1")
        For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
            sLAN = folderName & .Cells(i, 1).Value & ".jpg"
            sWAN = .Cells(i, 2).Value
            ret = URLDownloadToFile(0&, sWAN, sLAN, BINDF_GETNEWESTVERSION, 0&)
            
            If ret = 0 Then
                .Cells(i, 3) = "File successfully downloaded"
            Else
                .Cells(i, 3) = "Unable to download the file"
            End If
        Next i
    End With
    
End Sub

该#If VBA7 And Win64 Then告诉 VBA 如何编译导入的函数。 64 位版本使用 PtrSafe 。以上内容在 32 位和 64 位上都进行了测试。