Qfyilyi

I need this for my job. Someone wrote us a macro. This macro contains a module that take links from cells and download the files.
However. Problem is that it must be 2 rows or more that contain links.
This is nonsense for me/us as we often just download one link/file = only 1 row.

So the code must be able to handle a single row and multiple rows.

I can code a bit HTML and PHP, but that's quite long time ago.

I understand that the error "Subscript out of range" (= ext = buf(UBound(buf)) )
is because of the array. Or the way the array is being handled. But that's it.

To be honest I have not enough time to learn VBA to a point to fix this. I also only have access to these files at work...and here at work...I have to work lol.

So, help would be highly appreciated.

Sub DownloadFilefromWeb()

    Dim strSavePath As String

    Dim URL As String, ext As String

    Dim buf, ret As Long

    Dim fi As String

    Dim lrow5 As Long

   Dim path As String



    Call Clear_All_Files_And_SubFolders_In_Folder

    lrow5 = Range("A2").End(xlDown).Row

    Worksheets("Link").Range("G2:G" & lrow5).Formula = "=GetURL(E2)"



    j = 1

    For i = 2 To lrow5

        fi = Worksheets("Link").Range("A" & i).Value

        URL = Worksheets("Link").Range("G" & i).Value

        buf = Split(URL, ".")

        ext = buf(UBound(buf))

        'MsgBox ActiveWorkbook.Path

        strSavePath = ActiveWorkbook.path & "Backup" & fi & "," & j & "." & ext

        ret = URLDownloadToFile(0, URL, strSavePath, 0, 0)

        j = j + 1

       'If ret = 0 Then

       '     MsgBox "Download has been succeed!"

       'Else

       '     MsgBox "Error"

       'End If



    Next i

  MsgBox ("Download Completed")

End Sub

Edit:

Screenshot of the excel sheet layout

Hard to say without knowing/seeing the layout of the sheet.

I'll guess/assume that row 1 of the Links worksheet contains headers, and that the data itself (that you want to loop through) begins from row 2.

Option Explicit



Sub DownloadFilefromWeb()

    Dim strSavePath As String

    Dim URL As String

    Dim ret As Long

    Dim Filename As String

    Dim fileExtension As String



    Call Clear_All_Files_And_SubFolders_In_Folder



    With Worksheets("Link")

        Dim lastRow As Long

        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row



        .Range("G2:G" & lastRow).Formula = "=GetURL(E2)"



        Dim fileCount As Long

        fileCount = 1



        Dim rowIndex As Long

        For rowIndex = 2 To lastRow

            Filename = .Range("A" & rowIndex).Value

            URL = .Range("G" & rowIndex).Value

            fileExtension = VBA.Strings.Mid$(URL, VBA.Strings.InStrRev(URL, ".", -1, vbBinaryCompare))



            strSavePath = .Parent.path & "Backup" & Filename & "," & fileCount & fileExtension

            ret = URLDownloadToFile(0, URL, strSavePath, 0, 0) ' <- Don't seem to do anything with this value. Maybe include a MsgBox alert if it returns a non-zero value.



            fileCount = fileCount + 1

        Next rowIndex

    End With



    MsgBox ("Download Completed")

End Sub

Main difference is that lastRow is assigned working from the sheet's last row upward (previously it was downward from row 2, meaning it could never be only row 2 i.e. one row of data).

lrow5 = Range("A2").End(xlDown).Row

This gets the number of rows from the current workbook and worksheet. Then in the loop:

For i = 2 To lrow5

it starts at the second row. If you want it to start at the first row (and if there is only one row), then change his to:

For i = 1 To lrow5

Worksheets("Link").Range("G2:G" & lrow5).Formula = "=GetURL(E2)"

It seems this sets all of the hyperlinks to the URL of cell E2, i.e. to the same URL.

The GetURL function is a custom function. See http://howtouseexcel.net/how-to-extract-a-url-from-a-hyperlink-on-excel