Do you prepare analytics by downloading a report, cleaning it up, summarizing it, and presenting it to your audience mostly using Excel? Quite common in the world of finance and accounting. Say you’re looking to automate some of the steps using the power of VBA. (ChatGPT and other Generative AI tools will be your friend on that journey.)
You KNOW one of the steps will be downloading the report. Once you do, it lives in your Downloads folder. But how do you make sure your automated Excel App picks up the latest version of the downloaded report?
Let’s say your download is named Sales Report.csv. When you download that report a second time, it’ll be named Sales Report (2).csv. If you’re looking to automate your analytics, you’ll want to make sure the latest download is always consistently named Sales Report.csv. To do that, add the following to your Excel App to cleanup your Downloads folder, removing old Sales Report downloads. After you’ve run this step in your Excel App, THEN you should download the report and proceed with importing it into your Excel.
Sub CleanupDownloadsFolder()
On Error Resume Next
Dim ReportName As String: ReportName = "Sales Report*.csv"
Dim ReportPath As String: ReportPath = Environ$("USERPROFILE") & "\Downloads\" & ReportName
Kill ReportPath
End Sub
All you need to do is place the name of your download where it says “Sales Report*.csv” above. You’ll want to make sure it’s in quotes. You’ll want to include a wildcard (*) after the name of the report but before the file extension. That ensures anything named Sales Report.csv with a number suffix is removed.
A couple of comments:
- On Error Resume Next is necessary in case the download you’re looking for doesn’t exist, i.e. your download folder is already cleaned up and you’re ready to go
- Note the ReportPath is dynamic, picking up your Downloads folder
Happy automation!