Apr 25 2020 09:48 AM
Hi
Sorry to have to ask such a basic question, but I simply can't work out how to do this.
I have a column of URLs that link to images, all I want to do is to download all of these images and save them to 1 specific folder. I've looked for a piece of code I can add but nothing I've found seems to work.
Apr 25 2020 12:27 PM - edited Apr 25 2020 12:29 PM
Solution@drnick66 hello there!
I wouldn't qualify the question as "basic". It's a little advanced with VBA I'd say. Below is an example of how you can download a list of files via their URL. It will save the name with the same name it has on the web. Note there are no checks to see if the file exists and the destination folder is hard-coded (change to desired).
Option Explicit
#If VBA7 Then
Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As LongPtr, _ ByVal szURL As String, _ ByVal szFileName As String, _ ByVal dwReserved As Long, _ ByVal lpfnCB As LongPtr) As LongPtr
#Else
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
#End If
Public Sub DownloadImages()
Dim Downloaded As Boolean
Dim Count As Long
Dim Index As Long
Dim FileName As String
Dim NameParts As Variant
Dim Values As Variant
Const DownloadPath As String = "C:\Users\Zack\Desktop\"
Values = ThisWorkbook.Worksheets("Sheet1").Range("A2:A6").Value
For Index = LBound(Values, 1) To UBound(Values, 1)
NameParts = Split(Values(Index, 1), "/")
FileName = NameParts(UBound(NameParts))
Downloaded = URLDownloadToFile(0, Values(Index, 1), DownloadPath & FileName, 0, 0) = 0
If Downloaded Then Count = Count + 1
Next Index
MsgBox Count & " files out of " & UBound(Values, 1) - LBound(Values, 1) + 1 & " downloaded."
End Sub
HTH
Apr 25 2020 12:50 PM
OMG. thank you so much @Zack Barresse. I had this code, but it wouldn't work because of the 64-bit thing. You are an absolute star.
thanks again
Nick
Apr 25 2020 02:13 PM
Sep 06 2021 02:27 AM - edited Sep 06 2021 02:28 AM
Hello, @Zack Barresse
I was searchin about the same thing and I found this topic. Instead of opening a new one, I decided to ask you here why I cannot compile and run the code you gave here. Can you please help me? Thanks in advance. I've attached the file with url files, which I want to extract/download. PS: my OS is W10 x64
Sep 11 2021 03:49 PM
@bardukissnetiquette would have you start a new thread and then just put a link in here. However, after looking at your file, there's only a few tweaks you need to get it working for you. The structure is a little different and it only needs about 3 adjustments.
Those changes make it work just fine for me using the below code. I did save your file as XLSB instead of CSV to run the code. You don't have to, but you can't save the file in CSV and retain the code in it. Hope this helps.
Option Explicit
#If VBA7 Then
Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As LongPtr, _
ByVal szURL As String, _
ByVal szFileName As String, _
ByVal dwReserved As Long, _
ByVal lpfnCB As LongPtr) As LongPtr
#Else
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
#End If
Public Sub DownloadImages_bardukiss()
Dim Downloaded As Boolean
Dim Count As Long
Dim Index As Long
Dim FileName As String
Dim NameParts As Variant
Dim Values As Variant
'// Change this path...
Const DownloadPath As String = "C:\Users\Zack\Desktop\"
'// Ensure this sheet name and range is correct. It's status right now.
Values = ThisWorkbook.Worksheets("Test3 2-10").Range("A2:A10").Value
For Index = LBound(Values, 1) To UBound(Values, 1)
NameParts = Split(Values(Index, 1), ";")
FileName = NameParts(LBound(NameParts))
Downloaded = URLDownloadToFile(0, NameParts(3), DownloadPath & FileName & ".jpg", 0, 0) = 0
If Downloaded Then Count = Count + 1
Next Index
MsgBox Count & " files out of " & UBound(Values, 1) - LBound(Values, 1) + 1 & " downloaded."
End Sub
You'll notice I had to manually append the ".jpg" to the file name in the API download, with the code portion:
DownloadPath & FileName & ".jpg"
This is because I am assuming the FileName variable should be using the PartNumber as the file name, which comes from the first column of your data (i.e. "A9V15463").
There is basically no error handling in this entire piece of code and it can certainly be improved upon, but it is the basics. Things I would think about if it were mine (I don't know your requisites):
Take care. :)
Apr 01 2022 09:53 AM - edited Apr 01 2022 11:44 AM
@Zack Barresse I am unable to get this to run. I edited slightly to make the download path dynamic based on the user but I keep getting "Compile Error: Constant expression required". I have the Option Explicit in there just as you do. Any advice?? Thank you.
Option Explicit
#If VBA7 Then
Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As LongPtr, _
ByVal szURL As String, _
ByVal szFileName As String, _
ByVal dwReserved As Long, _
ByVal lpfnCB As LongPtr) As LongPtr
#Else
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
#End If
Public Sub DownloadImages()
Dim Downloaded As Boolean
Dim Count As Long
Dim Index As Long
Dim FileName As String
Dim NameParts As Variant
Dim Values As Variant
'// Change this path...
Const DownloadPath As String = ("C:\Users\" & Environ("username") & "\Documents\RETS\")
'// Ensure this sheet name and range is correct. It's status right now.
Values = ThisWorkbook.Worksheets("Img URLs").Range("A2").Value
For Index = LBound(Values, 1) To UBound(Values, 1)
NameParts = Split(Values(Index, 1), ";")
FileName = NameParts(LBound(NameParts))
Downloaded = URLDownloadToFile(0, NameParts(3), DownloadPath & FileName, 0, 0) = 0
If Downloaded Then Count = Count + 1
Next Index
MsgBox Count & " files out of " & UBound(Values, 1) - LBound(Values, 1) + 1 & " downloaded."
End Sub
Apr 10 2022 11:43 AM
@NickIzGr8FulYou can't use a function call (Environ) in a constant like you are trying. Just declare it as a variable instead, like this.
Dim DownloadPath As String
DownloadPath = ("C:\Users\" & Environ("username") & "\Documents\RETS\")
Sep 27 2022 05:22 PM
Hi @Zack Barresse
Over here asking for help. I have a file I need to work on. Ive been searching about this topic and this answer pop up, which I find super helpful. Been trying to use the code on macro but it shows there is an error. Im pretty much new with macros but kinda get it.
I need to download the images that are on an excel file The URLs are in column B, I also need to assign them the name in column A. I'm trying to use this macro but it doesn't work. I have a mac with ios 12.6 software and using excel version 16,65 in Spanish (don't know if that would change the macro).
Jul 12 2023 07:57 PM - edited Jul 12 2023 07:58 PM
Thank you Zack so much for this code. Worked first time through.
Richard
Apr 25 2020 12:27 PM - edited Apr 25 2020 12:29 PM
Solution@drnick66 hello there!
I wouldn't qualify the question as "basic". It's a little advanced with VBA I'd say. Below is an example of how you can download a list of files via their URL. It will save the name with the same name it has on the web. Note there are no checks to see if the file exists and the destination folder is hard-coded (change to desired).
Option Explicit
#If VBA7 Then
Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As LongPtr, _ ByVal szURL As String, _ ByVal szFileName As String, _ ByVal dwReserved As Long, _ ByVal lpfnCB As LongPtr) As LongPtr
#Else
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
#End If
Public Sub DownloadImages()
Dim Downloaded As Boolean
Dim Count As Long
Dim Index As Long
Dim FileName As String
Dim NameParts As Variant
Dim Values As Variant
Const DownloadPath As String = "C:\Users\Zack\Desktop\"
Values = ThisWorkbook.Worksheets("Sheet1").Range("A2:A6").Value
For Index = LBound(Values, 1) To UBound(Values, 1)
NameParts = Split(Values(Index, 1), "/")
FileName = NameParts(UBound(NameParts))
Downloaded = URLDownloadToFile(0, Values(Index, 1), DownloadPath & FileName, 0, 0) = 0
If Downloaded Then Count = Count + 1
Next Index
MsgBox Count & " files out of " & UBound(Values, 1) - LBound(Values, 1) + 1 & " downloaded."
End Sub
HTH