How to Create a Download Manager with Excel VBA

With Excel, we can build custom download manager. This tool will allow you to download any type of file from the internet. Instead of cluttered Downloads folder, each file will get moved to their respective folder based on the file extension.

Download Now

If you want to start to use the software as soon as possible, then you can:

Download the Software Now

Otherwise, if you want to DIY, you can read the contents below.

Let’s prepare the GUI

Create a new workbook and add two sheets to it. Name the first sheet as “Downloads” and the second sheet as “Folder Settings”. In the sheet “Downloads”, add two columns. First Column is just to hold the serial number and the second column is to hold the URL of files to be downloaded. Please make sure that you increase the width of the “File URL” column to an extent that the full URL is visible. If you do not like to alter width of cells, you can directly merge cells and create a big field to hold big URLs.

Now we need buttons. Using shapes, add two rounded rectangles on to the same sheet. These two rounded rectangles will now act as buttons for our tool. Name the first button as “Download” and the other button as “Clear”

Go to the sheet “Folder Settings” and create multiple fields to add the folder path for each file type. In this example, I have created fields for 6 file types i.e., Excel, word, pdf, csv, png and torrent.

Let’s make it functional

Copy and paste the script into a new module. The script has two Subs and one function. Attach the Sub “Download_All” to the button “Download” and attach the macro “Clear_All” to the button “Clear”

Let’s test it

Add URL of a file and press the “Download” button. You can now find the file in respective folder as specified in the sheet “Folder Settings”.

How it works?

The script passes the URL to the Function getfilename to retrieve the file name. The file is downloaded from the URL and saved using the name received from the function. The Clear button will help you to clear old URLs and add fresh list of URLs

Modify it

You can add another column “Time to Download” and specify a time value to this Column. If you are using this on a server, you can keep the Excel open and create a macro to scan the “Time to Download” column at regular interval. If the specified time has reached, the macro will download the file.

Script

Sub Download_All()
    Dim lr As Long
    Dim fileurl As String
    Dim r As Long
    lr = Sheets("Downloads").Range("C" & Rows.Count).End(xlUp).Row
    For r = 5 To lr
        fileurl = Sheets("Downloads").Range("C" & r).Value
        If InStr(1, fileurl, ".xlsx") <> 0 Then
            filepath = Sheets("Folder Settings").Range("D2").Value
        End If
        If InStr(1, fileurl, ".docx") <> 0 Then
            filepath = Sheets("Folder Settings").Range("D4").Value
        End If
        If InStr(1, fileurl, ".pdf") <> 0 Then
            filepath = Sheets("Folder Settings").Range("D6").Value
        End If
        If InStr(1, fileurl, ".csv") <> 0 Then
            filepath = Sheets("Folder Settings").Range("D8").Value
        End If
        If InStr(1, fileurl, ".png") <> 0 Then
            filepath = Sheets("Folder Settings").Range("D10").Value
        End If
        If InStr(1, fileurl, ".torrent") <> 0 Then
            filepath = Sheets("Folder Settings").Range("D12").Value
        End If
        Dim Obj1 As Object
        Set Obj1 = CreateObject("Microsoft.XMLHTTP")
        Obj1.Open "GET", fileurl, False
        Obj1.send
        If Obj1.Status = 200 Then
            Set Obj2 = CreateObject("ADODB.Stream")
            Obj2.Open
            Obj2.Type = 1
            Obj2.Write Obj1.responseBody
            Obj2.SaveToFile (filepath & getfilename(fileurl)), 2 ' 1 = no overwrite, 2 = overwrite
            Obj2.Close
        End If
    Next r
End Sub
    
Function getfilename(filepath As String)
    Dim v_string() As String
    v_string = Split(filepath, "/")
    getfilename = v_string(UBound(v_string))
End Function

Sub Clear_All()
    Sheets(“Downloads”).range(“C5:P50”).clearcontents
End Sub

Use this script on Excel to get the desired output. However, sometimes your Excel files might get corrupted. To fix corrupted Excel data, you can use recovery tools such as DataNumen Excel Repair tool to get the desired result.

Author Introduction:

Nick Vipond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair doc damage and outlook recovery software products. For more information visit www.datanumen.com