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

[OilEconomist]

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:

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?"

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

 

[GWteB]

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]

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]

What do you mean by the follow method?

The Follow method as in:

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.