Download Zip with new file name, auto open afterwards

excelaudio

New Member
Joined
Nov 4, 2015
Messages
8
So I have spent WAY too much time trying to get to this answer. Below is what I need to get at...and yes, I have been to RondeBruin's site and it hasn't helped much from my limited knowledge.

I have Excel 2010, WinZip16.0 (winzip64.exe), Internet Explorer 11

  1. Download .zip file from site (I got this working)
  2. Rename downloaded file to something more simple in the same folder it was downloaded to
  3. If I go to download the file and it already exists, replace it with the file I'm downloading & renaming today
  4. auto extract zip file to the same folder it was downloaded to
  5. auto open .xls file that was just extracted

Below is what I have so far:

Code:
Option Explicit
Private Declare Function URLDownloadToFileA Lib "urlmon" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long
 
Public Sub Step2_Download_Unzip_Daily_KMI()

Dim spath As String, sFileName As String, PrevBusDayFile As String
Dim TodayFile As Variant
Dim DownloadLocale As String
Dim FileMonth As String
Dim FileYear As String
Dim FileDate As String
Dim ZipFilename As Variant

FileMonth = Format(Date, "MM-MMM")
FileYear = Format(Date, "yyyy")

spath = "http://.................." & FileYear & "/" & FileMonth & "/"
sFileName = Format(Date, "yy-mmdd") & "%20DAILY%20KMI%20AG%20Data.zip"
TodayFile = DownloadLocale & sFileName

DownloadLocale = "C:\Users\username\Desktop\Import\ZIP\"

    DownloadFile spath & sFileName, DownloadLocale & sFileName
    
End Sub

Private Function DownloadFile(URL As Variant, sFileName As String) As Boolean
     'Thanks Mentalis:)
    Dim lngRetVal As Long
    lngRetVal = URLDownloadToFileA(0, URL, sFileName, 0, 0)
    If lngRetVal = 0 Then DownloadFile = True

End Function
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Does anyone have any thoughts/help they could offer? So far, no responses....


So I have spent WAY too much time trying to get to this answer. Below is what I need to get at...and yes, I have been to RondeBruin's site and it hasn't helped much from my limited knowledge.

I have Excel 2010, WinZip16.0 (winzip64.exe), Internet Explorer 11

  1. Download .zip file from site (I got this working)
  2. Rename downloaded file to something more simple in the same folder it was downloaded to
  3. If I go to download the file and it already exists, replace it with the file I'm downloading & renaming today
  4. auto extract zip file to the same folder it was downloaded to
  5. auto open .xls file that was just extracted

Below is what I have so far:

Code:
Option Explicit
Private Declare Function URLDownloadToFileA Lib "urlmon" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long
 
Public Sub Step2_Download_Unzip_Daily_KMI()

Dim spath As String, sFileName As String, PrevBusDayFile As String
Dim TodayFile As Variant
Dim DownloadLocale As String
Dim FileMonth As String
Dim FileYear As String
Dim FileDate As String
Dim ZipFilename As Variant

FileMonth = Format(Date, "MM-MMM")
FileYear = Format(Date, "yyyy")

spath = "http://.................." & FileYear & "/" & FileMonth & "/"
sFileName = Format(Date, "yy-mmdd") & "%20DAILY%20KMI%20AG%20Data.zip"
TodayFile = DownloadLocale & sFileName

DownloadLocale = "C:\Users\username\Desktop\Import\ZIP\"

    DownloadFile spath & sFileName, DownloadLocale & sFileName
    
End Sub

Private Function DownloadFile(URL As Variant, sFileName As String) As Boolean
     'Thanks Mentalis:)
    Dim lngRetVal As Long
    lngRetVal = URLDownloadToFileA(0, URL, sFileName, 0, 0)
    If lngRetVal = 0 Then DownloadFile = True

End Function
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top