Compile error: Type mismatch with switch from 32-bit to 64-bit office

[kohlo]

I have been using the following VBA code (slightly edited from here) to download and rename product images for my job with great success, until I got a new PC which runs 64-bit Office:

'Use this statement with 32-bit Excel
    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
     
    'Use this statement with 64-bit Excel
    'Private Declare PtrSafe Function URLDownloadToFile _
        Lib "urlmon.dll" 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 Long
     
    '~~> This is where the images will be saved. Change as applicable
    Const FolderName As String = "C:\Temp\"         'How Brad tested the code        *****Had to change this statement
     
    Sub DownloadAndRename()
    'Downloads files from columns I through M, renames them after the SKU in column M, then puts the new name in columns N through R
    Dim LastRow As Long, i As Long, j As Long, k As Long, maxCols As Long
    Dim SKU As String, strPath As String, URL As String
    Dim extension As String, rg As Range, SKUs As Range
    Static n As Long, start As Long
    Dim Pausetime As Double
    Application.ScreenUpdating = False
    maxCols = 10         'Maximum number of URLs in a row to download
    Pausetime = 4 / 86400
    n = 50
     
    With ActiveSheet
        Set rg = .Columns("B")  'First column of files to download          ******Had to change this statement
        Set SKUs = .Columns("A") 'SKUs located in this column               ******Had to change this statement
        LastRow = SKUs.Cells(.Rows.Count, 1).End(xlUp).Row
    End With
     
    If start = 0 Then start = 2
     
    For i = start To start + n '<~~ 2 because row 1 has headers
        SKU = SKUs.Cells(i, 1).Value
        If SKU <> "" Then
            For j = 1 To maxCols
                URL = rg.Cells(i, j).Value
                If URL <> "" Then
                    extension = ""
                    k = InStrRev("." & URL, ".")
                    If k > 1 Then extension = Mid(URL, k - 1)
                    If Len(extension) > 1 And Len(extension) <= 4 Then
                        strPath = FolderName & SKU & "_" & j & extension
                
                        Ret = URLDownloadToFile(0, URL, strPath, 0, 0)
                
                        If Ret = 0 Then
                            rg.Cells(i, maxCols + j).Value = strPath
                        Else
                            rg.Cells(i, maxCols + j).Value = "Download failed"
                        End If
                    End If
                End If
            Next
        End If
        If i >= LastRow Then Exit Sub
    Next i
    start = start + n + 1
    Application.OnTime Pausetime + Now, "DownloadAndRename"
     
     
    End Sub

I commented out the 32-bit stuff at the top and 'activated' the 64-bit part, but when running it I get a Compile error: Type mismatch highlighting "Ret = URLDownloadToFile(0, URL, strPath, 0, 0)"

I (a noob) googled around a bit and thought the fact that the code had Long instead of LongPtr might be the culprit. Replacing them all got me the same compile error though, this time highlighting this: "If k > 1 Then extension = Mid(URL, k - 1)"

Any help with this would be greatly appreciated, I use this snippet all the time. If there's nothing to be done about it I can ask the IT guys for permission to install the 32-bit version again, but I would like to see if I can get this fixed before I hassle them.

 

[6StringJazzer]

Don't change all the declarations to LongPtr, trying changing just Ret to LongPtr. I don't have 64-bit Office so I can't confirm, but in general that will cause Ret to be the same type as the return type of URLDownloadToFile.

 

[kohlo]

Don't change all the declarations to LongPtr, trying changing just Ret to LongPtr. I don't have 64-bit Office so I can't confirm, but in general that will cause Ret to be the same type as the return type of URLDownloadToFile.

Thanks for the reply! Do you mean edit this bit:

Dim Ret As Long

to this:

Dim Ret As LongPtr

?

If yes that succesfully crashes my Excel when I try to run it I tried changing other instances of Ret in the code to LongPtr but that will make the line highlight in red.

 

[RoryA]

The function return type should be Long not LongPtr even in 64 bit.

 

[kohlo]

The function return type should be Long not LongPtr even in 64 bit.

Hi, should I change something in the code from LongPtr to Long, or was that a general comment?

 

[RoryA]

Yes, change the return type:

Private Declare PtrSafe Function URLDownloadToFile _
        Lib "urlmon.dll" Alias "URLDownloadToFileA" _
(ByRef pCaller As LongPtr, _
ByVal szURL As String, _
ByVal szFileName As String, _
ByVal dwReserve As Long, _
ByRef lpfnCB As LongPtr) _
As Long

 

[kohlo]

Sadly, that crashes Excel as well For reference, here's the code I'm working with now:

'Use this statement with 32-bit Excel
    '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
     
    'Use this statement with 64-bit Excel
    Private Declare PtrSafe Function URLDownloadToFile _
        Lib "urlmon.dll" Alias "URLDownloadToFileA" _
    (ByRef pCaller As LongPtr, _
    ByVal szURL As String, _
    ByVal szFileName As String, _
    ByVal dwReserve As Long, _
    ByRef lpfnCB As LongPtr) _
    As Long
        
    Dim Ret As Long
     
    '~~> This is where the images will be saved. Change as applicable
    Const FolderName As String = "C:\Temp\"         'How Brad tested the code        *****Had to change this statement
     
    Sub DownloadAndRename()
    'Downloads files from columns I through M, renames them after the SKU in column M, then puts the new name in columns N through R
    Dim LastRow As Long, i As Long, j As Long, k As Long, maxCols As Long
    Dim SKU As String, strPath As String, URL As String
    Dim extension As String, rg As Range, SKUs As Range
    Static n As Long, start As Long
    Dim Pausetime As Double
    Application.ScreenUpdating = False
    maxCols = 10         'Maximum number of URLs in a row to download
    Pausetime = 4 / 86400
    n = 50
     
    With ActiveSheet
        Set rg = .Columns("B")  'First column of files to download          ******Had to change this statement
        Set SKUs = .Columns("A") 'SKUs located in this column               ******Had to change this statement
        LastRow = SKUs.Cells(.Rows.Count, 1).End(xlUp).Row
    End With
     
    If start = 0 Then start = 2
     
    For i = start To start + n '<~~ 2 because row 1 has headers
        SKU = SKUs.Cells(i, 1).Value
        If SKU <> "" Then
            For j = 1 To maxCols
                URL = rg.Cells(i, j).Value
                If URL <> "" Then
                    extension = ""
                    k = InStrRev("." & URL, ".")
                    If k > 1 Then extension = Mid(URL, k - 1)
                    If Len(extension) > 1 And Len(extension) <= 4 Then
                        strPath = FolderName & SKU & "_" & j & extension
                
                        Ret = URLDownloadToFile(0, URL, strPath, 0, 0)
                
                        If Ret = 0 Then
                            rg.Cells(i, maxCols + j).Value = strPath
                        Else
                            rg.Cells(i, maxCols + j).Value = "Download failed"
                        End If
                    End If
                End If
            Next
        End If
        If i >= LastRow Then Exit Sub
    Next i
    start = start + n + 1
    Application.OnTime Pausetime + Now, "DownloadAndRename"
     
     
    End Sub

 

[RoryA]

Crashes as in produces an error, or terminates Excel, or freezes it, or something else?

 

[kohlo]

It completely terminates Excel, then it restarts itself with autorecovered sheets if I had any open.

 

[RoryA]

I'd start by making both of those ByRef arguments ByVal.

There seems to be contradictory information out there as to whether the first and last arguments actually need to be LongPtr, so I'll have to do some more investigation.