VBA Download/Updater

sassriverrat

Well-known Member
Joined
Oct 4, 2018
Messages
655
With a little help, I've written some code. The intention of this is to perform weekly updates of a downloadable file. This macro, if it works, will be copied over and over (unless someone has a better way, PLEASE!) so that roughly 25 different files can be downloaded individually and/or simultaneously.

the kicker here- I don't want to have a situation where the no files exist- meaning if the download fails, I still have the old file just in case I need it.

thanks for checking for me!

Code:
Private Sub Download()Dim URL As String
Dim tstamp As String
Dim Folder As String
Dim Namer As String
Dim Date0 As String
Dim Date1 As String
Dim LocalFilePath As String
Dim TempFolderOLD As String
Dim TempFileNEW As String
Dim DownloadStatus As Long
With Sheets("Background")
    Namer = .Range("B4")   'Pub name
    URL = .Range("I4")     'URL to download
    Date0 = .Range("E4")   'Week #
    Date1 = .Range("C4")   'Year #
End With
With Sheets("Setup")
    Folder0 = .Range("B5") 'temp file
    Folder1 = .Range("B7") 'permanent file
End With
TempFolderOLD = Environ("Userprofile") & "\" & Folder0
tstamp = Format(Now, "mm-dd-yyyy")
TempFileNEW = TempFolderOLD & tstamp & "\" & Namer & ".pdf"
LocalFilePath = Environ("Userprofile") & "\" & Folder1 & "\" & Namer & ".pdf"
    
'If these criteria are met, let's begin the download tree
    If Date1 <> Sheets("Background").Range("G2") And Date0 <> Sheets("Background").Range("I2") Then
       
'Let's assign everything to the temp folder
    'Begin by clearing any possible undeleted/corrupted files from my "temp" folder
        If Len(Dir(TempFolderOLD)) <> "" Then Kill (TempFolderOLD)
    'Make a new temp folder
        If Len(Dir(TempFolderOLD)) = "" Then MkDir (TempFolderOLD)
    'Attempt download to the temp folder
        DownloadStatus = URLDownloadToFile(0, URL, TempFileNEW, 0, 0)
    'Check for proper download
        If DownloadStatus = 0 Then
        'Delete the old files
            Kill (LocalFilePath)
        'Save temp files to replace old files
            TempFileNEW.SaveAs Filename:=LocalFilePath, FileFormat:=xlpdf
        'Now delete temp files
            Kill (TempFileNEW)
        'Now update excel sheet to show download passed
            MsgBox "File Downloaded. Check in this path: " & LocalFilePath
                With Sheets("Background")
                    .Range("F4") = tstamp
                    .Range("G4") = "SAT"
                    .Range("C4") = Format(Now, "ww", vbWednesday)
                    .Range("E4") = Format(Now, "yy")
                End With
    'If download failed, update excel to show- old files should NOT have been deleted yet but the temp file should be deleted
        Else:
            MsgBox "Download File Process Failed"
            Sheets("Background").Range("G4") = "FAIL"
            Kill (TempFolderOLD)
        End If
    'If the original criteria were met and the download was not necessary, say so
    Else: MsgBox "The most up to date pub has been downloaded"
    End If
End Sub
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

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