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.
If you want to start to use the software as soon as possible, then you can:
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
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.
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.
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