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.
 

kohlo

New Member
Joined
Mar 22, 2018
Messages
10
I'd start by making both of those ByRef arguments ByVal.
Yes, that solved it! I can't thank you enough, you've really saved me here!

For posterity, here's the complete code that is now working for me:

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" _
    (ByVal pCaller As LongPtr, _
    ByVal szURL As String, _
    ByVal szFileName As String, _
    ByVal dwReserve As Long, _
    ByVal 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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Watch MrExcel Video

Forum statistics

Threads
1,128,079
Messages
5,628,539
Members
416,324
Latest member
sam_d

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