Log In
Register
Home
April 7, 2020
Offline
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 |
| example.com/ Documents/GetTempLinkFileName=150015.jpg | ABC | C:\Users\Desktop\ MyDownloads\ABC |
| example.com/Documents/ GetTempLink?FileName=149928.jpg | XYZ | C:\Users\Desktop\ MyDownloads\XYZ |
| example.com/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
See Answer
October 5, 2010
Offline
Hi Rajsha,
Please supply your workbook with code.
Regards
Phil
April 7, 2020
Offline
Hi Philip Treacy, Please find the attached file for your reference. I have placed code as is.. Thanks
October 5, 2010
Offline
Hi Rajsha,
The attached workbook does what you ask.
You only need to specify the save folder once in cell C2 - change this to suit your PC.
I've added some real image URL's that you can test with.
Regards
Phil
Answers Post
April 7, 2020
Offline
Hi Sir, Thanks for your valuable time in this.. The code which you sent i already tried and this works for only image files.. But, i need to pull any format that is any .extension files. I'm trying to solve this with selenium.. i will catch you with the updated code. Sorry! Thanks a lot.
October 5, 2010
Offline
Hi Rajsha,
the code will work with any file type. Just specify the URL for the file in Col A.
Are you trying to say that you want to get any/all files that are linked to from a specific web page?
Using the code you supplied you need to know in advance the URL for the file/image/doc etc. So if you want to get any/all files from a page, you'll need some different code.
Phil
April 7, 2020
Offline
Hi Sir,
Please check the file attached.. This worked fine for me. I have added additional helper column.. to save files.
Thanks For you help
Rajsha
October 5, 2010
Offline
Hi Rajsha,
This 'kind-of' works but as you haven't supplied the workbook where you are running it, I don't know if it's actually doing what you want.
This line is not needed as you never use fname
fname = Split(Link.Address, "/")(UBound(Split(Link.Address, "/")))
The file you save has no extension because you've removed this line from the code
ext = Split(Link.Address, ".")(UBound(Split(Link.Address, ".")))
The folder structure on Windows is such that you should have a username after C:\Users so
Pth = "C:\Users\Desktop\MyDownloads\" & Link.Parent.Offset(0, 3).Value
should be something like
Pth = "C:\Users\USERNAME\Desktop\MyDownloads\" & Link.Parent.Offset(0, 3).Value
I'm not sure what Link.Parent.Offset(0,3).Value is supposed to be picking up.
You are creating a folder for every link you save because this
MkDir (Pth)
is inside your FOR loop.
If you need me to look at this further please supply the entire workbook and clearly state what you are trying to achieve. State the desired results.
The code you've just supplied does work, but I find it hard to believe it's the ideal piece of code you actually want.
regards
Phil
April 7, 2020
Offline
Hi Sir,
I tried with your code that worked fine when i removed .Jpg at the "URLDownload..." line, since my url is already having .extensions it worked fine. By seeing your code i though it works for only jpg.. It's my misunderstanding...My apologies..
Can you suggest me to work this macro till the end of A column, where in the For loop i = 2 to only 7.. This should be changed to until the column A url ends.
Thanks
Rajsha
October 5, 2010
Offline
No worries.
The code works through all hyperlinks in the sheet and if they are all listed in Column A then it already does what you are asking.
The code I provided in Module 1 of the file in Post 4 above does all of this already.
Phil
1 Guest(s)
