• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Download files from hyperlinks and save each under new mentioned folder

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
 

Attachments

PCosta87

Well-Known Member
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):
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 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
 

Attachments

Last edited:
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.
 

Attachments

PCosta87

Well-Known Member
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"
 
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

Well-Known Member
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

New Member
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
 

Attachments

jbakhaus

New Member
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
 

Gunasekaran

New Member
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...:rolleyes::rolleyes::rolleyes: Kindly assist me

>>> use code - tags <<<
Code:
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
 
Last edited by a moderator:

vletm

Excel Ninja
Gunasekaran
As a new member,
You should reread and refresh Your memory from Forum Rules
There can find
How to get the Best Results at Chandoo.org
which You would read very carefully.
 
Top