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

kohlo

New Member
Joined
Mar 22, 2018
Messages
10
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:

VBA Code:
'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.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
811
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 22, 2018
Messages
10
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:
VBA Code:
Dim Ret As Long

to this:
VBA Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,453
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
The function return type should be Long not LongPtr even in 64 bit.
 

kohlo

New Member
Joined
Mar 22, 2018
Messages
10

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,453
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Yes, change the return type:

Rich (BB code):
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

New Member
Joined
Mar 22, 2018
Messages
10

ADVERTISEMENT

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

VBA Code:
'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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,453
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Crashes as in produces an error, or terminates Excel, or freezes it, or something else?
 

kohlo

New Member
Joined
Mar 22, 2018
Messages
10
It completely terminates Excel, then it restarts itself with autorecovered sheets if I had any open.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,453
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,263
Messages
5,623,704
Members
415,983
Latest member
MusicMan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top