Saturday 6 December 2014

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"
            myURL = WinHttpReq.responseBody
            If WinHttpReq.Status = 200 Then
                Set oStream = CreateObject("ADODB.Stream")
                oStream.Type = 1
                oStream.Write WinHttpReq.responseBody
                oStream.SaveToFile "C:\Files\" + fileName + ext, 1 ' 1 = no overwrite, 2 = overwrite
            End If
        End If
End Sub

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