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:
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.
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 SubI 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.
