Simple Code to Download Files from a Website Using List of Links

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
Two questions:
(1) How to change from selecting the cells with the links to a loop?
(2) how to fix the error?

(1) How to change from selecting the cells with the links to a loop?
I'm trying to download files from a website where I have all the links in the sheet "Download.Web.Files" of the file "Download Web Files via Hyperlinks - (Active).xlsm". The links start in cell A7. Currently I have to select the cells, but I would like to make it to have a Loop from cell A7 to the "LastRow".

(2) how to fix the error?
For now, I am getting the error "Run-time error ‘52’: Bad file name or number” on the following line:

Code:
FileCopy .Address, DestinationFolder & FileName

Also, note that when I manually click on one the of the links, the following pops up which might also be an issue.
"Opening [websitepath] Some files can contain viruses or otherwise be harmful to your computer. It is important to be certain that this file from a trustworthy source. Would you like to this file?"

Code:
Sub Hyperlink_Download()

    'Sets all display alerts to be ignored
        Application.DisplayAlerts = False

    'Dimensioning
        Dim RangeofLinks As Range
        Dim FileName As String, p As Long     
        Dim DestinationFolder As String
        Dim LastRow As Long
        Dim i As Long
    
    'Activate the Workbook and Sheet
        Workbooks("Download Web Files via Hyperlinks - (Active).xlsm").Sheets("Download.Web.Files").Activate   
    
    'Setting Destination Folder (folder where files should be saved)
        'Destination Folder
            DestinationFolder = Range("B2").Value
    
        'Ensuring the destination folder link has "\" at the end of the path, and if not, adds it.
            If Right(DestinationFolder, 1) <> "\" Then DestinationFolder = DestinationFolder & "\"
    
    'Copying the files
            For Each RangeofLinks In Selection
                If RangeofLinks.Hyperlinks.Count > 0 Then
                    With RangeofLinks.Hyperlinks(1)
                        p = InStrRev(.Address, "\")
                        FileName = Mid(.Address, p + 1)
                        FileCopy .Address, DestinationFolder & FileName
                    End With
                End If
            Next            

'Turns all display alerts back on
        Application.DisplayAlerts = True

End Sub
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Hi OilEconomist,

1) To "execute" a hyperlink (like you are clicking on it), the Follow method has to be used.
2) The Address property of a hyperlink in your situation is obviously a web site address and therefore not a legit "drive:\folder\filename" string, causing a Run-time error.

So you have to download the desired file first. Then you can copy that file from your downloads folder to any destination folder.
 

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
Thanks GWteB,

Please bare with me, as I'm still learning the more complex MSExcel functions.

What do you mean by the follow method?

If you click the hyperlink, at times it may download the file and it may just open the file.

Any clues on how to fix the code?
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
What do you mean by the follow method?
The Follow method as in:
VBA Code:
Hyperlinks(1).Follow

As I understand your hyperlinks act different, depending on the web site. There is no way to determine in advance in what way web links will act.
That being said, downloading a file isn't harmfull but opening a file directly from a web address is tricky, it makes your system vulnerable. Probably that's why you're getting a warning.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,966
Messages
5,621,874
Members
415,862
Latest member
nascaline

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top