Vba code on looping

[Satish Raju]

Hi Team, I need a small help from you to enhance the below code.
The code which i found is mentioned below... where this code downloads the url documents and SaveAs to folder.


Sub DownloadFile()
pth = [C2]
For Each link In ActiveSheet.Hyperlinks
ext = Split(link.Address, ".")(UBound(Split(link.Address, ".")))
Filename = pth & "" & link.Range.Offset(, 1) & "." & ext
URLDownloadToFile 0, link.Address, Filename, 0, 0
Next link
Call Shell("explorer.exe" & " " & [C2], vbNormalFocus)
End Sub

Here i need to loop the path as per the SaveName. (means every line/url doc should be saved in the path which i created in C column)

Ex:

The file 150015.jpg should be named as ABC and this file should be saved to folder C:\Users\Desktop\ABC
The file 149928.jpg should be named as XYZ and this file should be saved to folder C:\Users\Desktop\XYZ
The file 150015.jpg should be named as 123 and this file should be saved to folder C:\Users\Desktop\123




Path
SaveName
Path


https://example.com/Documents/GetTempLinkFileName=150015.jpg (https://example.com/Documents/GetTempLinkFileName=1500159287542.jpg)
ABC
C:\Users\Desktop\MyDownloads\ABC


https:// (https://api.servicechannel.com/Documents/GetTempLink?FileName=1499289252979.jpg)example.com (https://example.com/Documents/GetTempLinkFileName=1500159287542.jpg)/Documents/GetTempLink?FileName=149928.jpg
XYZ
C:\Users\Desktop\MyDownloads\XYZ


https:// (https://api.servicechannel.com/Documents/GetTempLink?FileName=1500159287542.jpg)example.com (https://example.com/Documents/GetTempLinkFileName=1500159287542.jpg)/Documents/GetTempLink?FileName=150015.jpg
123
C:\Users\Desktop\MyDownloads\123



Below is the code i tried but i got stuck some where. could you please help me in this.


Sub DownloadFile()
Dim L As Long, Lr1 As Long, Lr As Long
Lr1 = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For L = 2 To L + 1
pth = ThisWorkbook.Sheets("Sheet1").Range("C" & L) '[C2]
For Each link In ActiveSheet.Hyperlinks
ext = Split(link.Address, ".")(UBound(Split(link.Address, ".")))
Filename = pth & "" & link.Range.Offset(, 1) & "." & ext
URLDownloadToFile 0, link.Address, Filename, 0, 0
Next link
Next
Call Shell("explorer.exe" & " " & [C2], vbNormalFocus)
End Sub


Any help please.. Thanks