VBA Download Files

With nothing but an excel spreadsheet which contained a file url and the name it had to be changed to i needed to download 78 files and rename them. not something i'm going to do manually and did i mention Powershell was not an option, well I ended up falling back on the age old macro.

Sub DownloadFiles()

    Dim rng As Range

    Set rng = Application.Selection

    

    Dim ir As Integer

    

    For ir = 1 To rng.Rows.Count

        Dim ext As String

        Dim fileName As String

        Dim myURL As String

        

        'Extract url of file

        myURL = rng.Rows(ir).Hyperlinks(1).Address

        

        'Get file name from s column of corresponding row to selection

        fileName = Range("S" & rng.Rows(ir).Row)

        

        'Get file extension from en

        ext = Right(myURL, Len(myURL) - InStrRev(myURL, ".") + 1)

        'check to make sure file needs to be migrated

        If (Range("T" & rng.Rows(ir).Row) = "Yes") Then

       

       

            Debug.Print fileName & ext

            Dim WinHttpReq As Object

            Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")

            WinHttpReq.Open "GET", myURL, False, "User", "Password"

            WinHttpReq.send

    

            myURL = WinHttpReq.responseBody

            If WinHttpReq.Status = 200 Then

                Set oStream = CreateObject("ADODB.Stream")

                oStream.Open

                oStream.Type = 1

                oStream.Write WinHttpReq.responseBody

                oStream.SaveToFile "C:\Files\" + fileName + ext, 1 ' 1 = no overwrite, 2 = overwrite

                oStream.Close

            End If

        End If

    Next

End Sub

and that's it, never thought i'd be writing with the Basic syntax, but here i am.