Download files from hyperlinks and save each under new mentioned folder

[Samadhan Gaikwad]

Hi,

I am in search of vba macro with which I can download files from hyperlinks listed in B column and save each in respective new folder (listed in column A).


Any help will be appreciated.
Thanks,
Samadhan

 

[PCosta87]

Hi,

I am in search of vba macro with which I can download files from hyperlinks listed in B column and save each in respective new folder (listed in column A).


Any help will be appreciated.
Thanks,
Samadhan

Hi,

Try this code and let me know if it works for you (edit: pelase refer to attachment):

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 ParentFolderName As String = "C:\Users\a3rgcw\Downloads\"

Sub Sample()
    Dim ws As Worksheet
    Dim LastRow As Long, i As Long
    Dim Folderpath, strPath As String

    Set ws = Sheets("Sheet1")

    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

    For i = 1 To LastRow
  
        Folderpath = ParentFolderName & ws.Range("A" & i).Value & "\"
  
        If Len(Dir(Folderpath, vbDirectory)) = 0 Then
            MkDir Folderpath
        End If
  
        strPath = Folderpath & "File" & i & ".jpg"
        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

Hope this helps

 

[Samadhan Gaikwad]

Hi,

Thank you for providing such a nice code.
It is working fine as expected.
But files that I am downloading are .7z or .zip files.
Macro is creating folders to dump these files in respective folder but all are almost empty or in some cases there is File (unwanted or may be log file) created.
Note that there is no direct path to file in hyperlink. I have added in uploaded .xlsm file.

 

[PCosta87]

Hi,

Thank you for providing such a nice code.
It is working fine as expected.
But files that I am downloading are .7z or .zip files.
Macro is creating folders to dump these files in respective folder but all are almost empty or in some cases there is File (unwanted or may be log file) created.
Note that there is no direct path to file in hyperlink. I have added in uploaded .xlsm file.

Hi,

It must be have something to do with the links... does manually pasting them in the browser open anything? The ones in the sample throw an error.

Also, you have "*.zip" and there should be no *...
Use only ".zip"

 

[Samadhan Gaikwad]

Yeah...issue resolved after removing '*'.
Actually if go for manual download, user has to click button of that file and java script does download data. I had to automate that thing.
Recorded manual script to create hyperlink from source code and at next your script is helping me ahead!
Thank you so much....!!!!

 

[PCosta87]

Yeah...issue resolved after removing '*'.
Actually if go for manual download, user has to click button of that file and java script does download data. I had to automate that thing.
Recorded manual script to create hyperlink from source code and at next your script is helping me ahead!
Thank you so much....!!!!

Don't mention it, just glad I could help

 

[kiruthika]

hi,
I have using the above code for downloading excel file from url but after downloading i have received error while opening the excel. Can anyone help me out for this? why i am receiving this error? thank you

 

[jbakhaus]

Does anyone know if this will work for a Mac? I too have a long list of hyperlinks and would like to save individual pdfs.

thanks

 

[Marc L]

Can't work on Mac 'cause it uses a Windows feature …​

​

 

[jbakhaus]

@Marc L are there many Mac users in this forum?

 

[Marc L]

Maybe but few …​

​

 

[Gunasekaran]

Hi

In Excel Hyperlinks there is an Excel .xls, .xlsx, PDF, Msg is there. All documents should be download and save as in the same folder. it seems files not generating in below Code... Kindly assist me

>>> use code - tags <<<

Option Explicit

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 ParentFolderName As String = "\\gssux002\GBS_CHE_GL_TMS\ICV\"
Sub Download_Report()
    Dim ws As Worksheet
    Dim LastRow As Long, i As Long
    Dim Folderpath, strPath As String

    Set ws = Sheets("ICV Report")

    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To LastRow
     
        Folderpath = ParentFolderName & ws.Range("A" & i).Value & "\"
     
        If Len(Dir(Folderpath, vbDirectory)) = 0 Then
            MkDir Folderpath
        End If
     
        strPath = Folderpath & ws.Range("A" & i).Value & i & ".*"
        Ret = URLDownloadToFile(0, ws.Range("H" & i).Value, strPath, 0, 0)

        If Ret = 0 Then
            ws.Range("I" & i).Value = "File successfully downloaded"
        Else
            ws.Range("I" & i).Value = "Unable to download the file"
        End If
 
    Next i

MsgBox "Completed This process !!!", vbInformation

End Sub

 

[vletm]

Gunasekaran
As a new member,
You should reread and refresh Your memory from Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

There can find
How to get the Best Results at Chandoo.org
which You would read very carefully.