VBA code is not working on 64 bit PC.

[ANONYMOUS123456]

I have following VBA code with works for an excel file where column A=PIC NAME , B=URL, C=status.
This code tries to open the image URLs and download them and rename those images as well. But when
I try to run this code on 64 bit PC then the message " The code in this project must be updated for use on
64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute."
is displayed.

I want the following code to run on both 32 bit and 64 bit PCs .
Can anybody make some changes in the following code to achieve the desired outcome?

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

 

[Jaafar Tribak]

Maybe this :

#If Win64 Then
    Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
    Alias "URLDownloadToFileA" _
    (ByRef pCaller As LongPtr, _
     ByVal szURL As String, _
     ByVal szFileName As String, _
     ByVal dwReserve As Long, _
     ByRef lpfnCB As LongPtr) _
As LongPtr
#Else
    Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
    Alias "URLDownloadToFileA" _
    (ByRef pCaller As Long, _
     ByVal szURL As String, _
     ByVal szFileName As String, _
     ByVal dwReserve As Long, _
     ByRef lpfnCB As Long) _
As Long
#End If

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
EndSub

 

[ANONYMOUS123456]

Maybe this :

#If Win64 Then
    Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
    Alias "URLDownloadToFileA" _
    (ByRef pCaller As LongPtr, _
     ByVal szURL As String, _
     ByVal szFileName As String, _
     ByVal dwReserve As Long, _
     ByRef lpfnCB As LongPtr) _
As LongPtr
#Else
    Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
    Alias "URLDownloadToFileA" _
    (ByRef pCaller As Long, _
     ByVal szURL As String, _
     ByVal szFileName As String, _
     ByVal dwReserve As Long, _
     ByRef lpfnCB As Long) _
As Long
#End If

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
EndSub

Well, when I run this code then it says "Compiler error : TYPE MISMATCH" and "URLDownloadToFile" is highlighted.

 

[Jaafar Tribak]

I overlooked the Ret variable ... Try this instead :

#If Win64 Then
    Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
    Alias "URLDownloadToFileA" _
    (ByRef pCaller As LongPtr, _
     ByVal szURL As String, _
     ByVal szFileName As String, _
     ByVal dwReserve As Long, _
     ByRef lpfnCB As LongPtr) _
As LongPtr
Dim Ret As LongPtr
#Else
    Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
    Alias "URLDownloadToFileA" _
    (ByRef pCaller As Long, _
     ByVal szURL As String, _
     ByVal szFileName As String, _
     ByVal dwReserve As Long, _
     ByRef lpfnCB As Long) _
As Long
Dim Ret As Long
#End If

'~~> 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
EndSub

 

[accradhe]

While using above code my XL is crashing at below code, any suggestion why?

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

 

[Jaafar Tribak]

Try this :

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe 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 LongPtr) 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
#End If


Sub Sample()

    Const FolderName As String = "C:\Temp"
    
    Dim ret As Long
    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