playtime007
New Member
- Joined
- Feb 3, 2020
- Messages
- 32
- Office Version
- 2019
- Platform
- Windows
I am having a simple application in excel to download all zip urls in column "C" column "C" has urls and column "Q" has names Every url has 1 zip file and every zip has 1 csv in it. What I want to do is to 1. unzip the CSV files and save them with names as mentioned in column "Q" 2. Kill all the zip 3. CSV file must be overwritten as I will be doing this task on daily basis so when the next day or next time I perform the task it must overwrite the previous file
VBA Code:
Sub Download_All()
Dim lr As Long
Dim fileurl As String, filename 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, ".zip") <> 0 Then
filepath = "C:\MyDownloads\"
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