I am getting following error.
Compile error: The code in this project must be updated for use on 64-bit systems.
VBA CODE
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\"
It works fine in Excel 2010.
Thanks.
EDIT
Error I get is Ret Variable Not defined
. Here's the rest of the code.
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 & ".mp3"
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
You must be running this on a 64Bit version of Office whereas previously you were using a 32Bit version.
To convert 32Bit calls to 64Bit you generally have to add PtrSafe
to the function and convert some of the data types from Long
to LongPtr
(which is merely a larger datatype (see: http://msdn.microsoft.com/en-us/library/office/gg251378.aspx)
So the converted function would be:
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
Edit: Note if you want to be able to use this on both 64Bit and 32Bit versions of Office you need to use a preprocessor If statement so Office knows which function to use. Ie:
#If Win64 Then
Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon".......
#Else
Private Declare Function URLDownloadToFile Lib "urlmon".......
#End If