Excel VBA Case Study #3 – File Checker Macro

Last updated on March 16, 2019 By Victor Chan

Summary: In case study #1 I showed you how I built an Excel macro to save 50 company “investment” worksheets and email each one. After I built that macro I realized I still had another problem, which was to track the files already submitted to me and highlight which ones were missing.


It would take me about 25 minutes to check manually… but I needed to do it much faster. Since I was on a roll, I decided to create another macro to help me.


This became my File Checker macro and it has literally wowed my co-workers. Instead of taking 25 minutes I can now do the same task in 10 seconds!


Read this case study to learn how the Excel VBA code works so you can use it yourself.


Difficulty: Intermediate

Download the Sample Workbook

Download the sample file with VBA code
20190316-File-Checker.zip (3,572 Kb)

#1 – Why did I create the Macro?

As I mentioned in Case Study 1, each month my company receives financial performance reports (i.e., workbooks) from all 50 of our subsidiaries.


The subsidiaries submit three types of reports:

  1. Level 1: High-level report about clients
  2. Level 2: Detailed report about my company’s departments
  3. Sales Report: Monthly sales with diagrams and graphs

Each month, my team uses the macro I showed you in Case Study 1 to create internal reports, save them to the server and email them out.

Here’s a representation of the folder and file structure I work with. Imagine 50 folders on the left hand side (instead of 5) and

you can grasp how many files there are!

Although I had already automated the first part, my team still needed to keep track of which reports we created. We also needed to update the reports when the subsidiaries sent in new figures.


My managers want the reports as soon as possible, so my team needs to process the reports as soon as we receive them. Sometimes we thought we processed the reports so quickly that we were cool…and then the manager called us asking for his reports. He couldn’t find them on the server. We had created them, but they were still sitting on our personal drives rather than on the server. Oops!


After that happened, I decided I needed to create the “Filechecker” macro. This amazing tool tells me which files are on the server…and which ones are missing.

#2 – What does it do?

The macro browses a list of folders, looks for certain files, and checks their date stamps. To build it, I first came up with its rules:

  • I needed to specify month and year (I did this with a drop-down)
  • I had to specify the folders’ paths
  • The paths had to change based on the selected period
  • I had to specify the correct file names (Company Number_Company Short Name_”Report”.pdf)
  • When I found the file, I wanted it to be green on my list
  • If I didn’t find it, I wanted it to be red

After identifying the rules, I wrote code that satisfied all of them.

#3 – How is it better than using Excel without VBA?

How is using Filechecker better than manually looking up PDF files? Considering we have 50 companies in different folders with various years and months, it’s no contest!


Besides, the lag in our file manager sometimes makes our manual process unbearable! Our IT department always says everything is okay, but why do I have to wait five seconds to open a folder? Copying and deleting is even worse!


The macro, on the other hand, provides me results in under 10 seconds…for all 50 companies!


Manually checking one single company’s reports takes half a minute. That means it would take 25 minutes to check for all 50 reports! The macro also provides me flexibility. I can search for only one report type at a time using the separate sheet for each report type. Or, if I want to check all three report types at once, I can do that on the main Dashboard by clicking on the “Check All” button.

This is the Filechecker dashboard showing Level 1, Level 2 and Sales reports for 5 countries.

As you can see in the above screenshot of the Dashboard, the green color shows that the February 2019 sales reports are ready. The Level 1 and 2 reports – both of which are red – still need be created. Note: to simplify this case study I used 5 countries rather than 50.

#4 –How does the code work?

Now it’s time to dig into the code together!

Structure

The workbook has four code modules: one for each report type and one for the “Dashboard” (shown in the above screenshot).

Each report type module contains the same basic code…only the range and column references differ. I will explain those modules shortly.


The Dashboard module contains a “CheckAllReports” macro that calls the three PDF file searcher macros from the report type modules. It also calls a “ClearDashboard” subroutine that clears the dashboard’s data.

Sub CheckAllReports()

MsgBox "Checking all reports. This may take a while. Please be patient!", _
vbOKOnly, "Information"

Call ClearDashboard

Call UpdateFileDateDirL1

Call UpdateFileDateDirL2

Call UpdateFileDateDirL2SalesReport

MsgBox "All reports have been checked."

End Sub


Private Sub ClearDashboard()
    Dim rng As Range
        Set rng = Sheets("Dashboard").Range("D3:I50")
            rng.ClearContents
End Sub

The macro searches for reports in “C:\LaunchExcel\Reports\year\month” (where year and month are variables).


I created the folder structure so that you only have to save the files on your “C:\” drive. Feel free to modify the structure to suit your own needs.


You can, of course, move and delete reports and watch how the dashboard reacts when refreshed.


The tabs in the macro workbook (Level 1, Level 2, and Sales Report) contain the path name in column E. The CONCATENATE function joins the text together. In case you’re not familiar with it, you can read Microsoft’s documentation here.

Declare Variables

Since the three report modules are nearly identical, I’ll just walk you through “Level1”.


As with all VBA programs, I start by declaring variables:

Sub UpdateFileDateDirL1()

Dim path As String
Dim year, month As String
Dim lrow As Long

'Calculate everything, to refresh period
Calculate

'Clear date and land list
Sheets("Level 1").Range("F2:F50").Value = ""
Sheets("Dashboard").Range("D3:D50").Value = ""

I then recalculate the formulas using the “Calculate” command. This automatically writes the paths and file names – both determined by formulas – to the appropriate Excel sheets.

'Set period
year = Sheets("Level 1").Cells(2, 1).Value
month = Sheets("Level 1").Cells(3, 1).Value

'Find last row on report pages
lrow = Sheets("Level 1").Range("B500").End(xlUp).Row

Next, I set the year and month variables equal to the cells on the “Dashboard” where users choose the year and month. I then find the last row on the “Level 1” sheet. Since the list of the companies varies, I don’t want to hard code the last row’s number.


The next snippet is more interesting:

'Collect file date stamps
For i = 2 To lrow
     path = Sheets("Level 1").Cells(i, 5).Value
        On Error Resume Next
            Sheets("Level 1").Cells(i, 6).Value = FileDateTime(path)
        If Err.Number = 53 Then
            Sheets("Level 1").Cells(i, 6).Value = "File not found"
        End If
Next i

Here we use Excel’s “FileDateTime()” function.


This function provides the last modified date of the file specified in parentheses. If the file is not found, it returns a “53” error. I added error handling to the program so that it returns “File not found” instead of displaying the error.


If the program finds a company’s file and its last modified date, it writes the company’s name to the “Dashboard” in green.


If instead of finding a date stamp the program returns “File not found”, it writes the company’s name on the dashboard in red.

'Colour red the missing files on Dashboard
Set rng = Sheets("Level 1").Range("F2:F50")

For Each cell In rng
    If cell.Value = "File not found" Then
            lrow = Sheets("Dashboard").Range("D50").End(xlUp).Row
                Sheets("Dashboard").Cells(lrow + 1, 4).Value = _
                Sheets("Level 1").Cells(cell.Row, 2).Value
                Sheets("Dashboard").Cells(lrow + 1, 4).Font.Color = -16776961
    Else
            lrow = Sheets("Dashboard").Range("D50").End(xlUp).Row
                Sheets("Dashboard").Cells(lrow + 1, 4).Value = _
                Sheets("Level 1").Cells(cell.Row, 2).Value
                Sheets("Dashboard").Cells(lrow + 1, 4).Font.Color = -11489280
        End If
    Next cell


End Sub

And that’s it!


As I mentioned earlier, I only had to write one of the report code modules from scratch. I then copied the module and adjusted it for the different report names. Easy enough!

#5 – Summary of key learning points

This macro works well since we have standardized file names. This allowed us to easily search for them by simply changing the company names, numbers, and month/year.

I always try to understand how key steps hold processes together. This allows me to automate those processes using VBA.


In my case, the naming standard was the key – without it I wouldn’t be able to modify the file names and use “FileDateStamp”. Since this function doesn’t support wildcard characters, I had to refer to the exact file names, which is why the naming standard was critical!


If you look at the code, it is very short and simple. Try to stick to clean solutions – they always work best, and they are easier to debug. Best practices for coding are also easy to find on the internet…especially at LaunchExcel!

#6 – About the Author

Daniel Lajosbanyai – I work as a controller and accountant for a company with a bunch of international subsidiaries. In my daily job I work A LOT with Excel and my tasks are quite repetitive (and sometimes boring!)


To boost my processes and spare some time and energy, I started learning Excel Macros. My first teacher was a university professor, who showed me how to get started. I am really thankful to him, because without this knowledge I might have quit my job years ago.


Now I enjoy writing macros for every task I can automate and feel really happy to have learned this skill! Why should we do repetitive things, when our computers can do them quicker for us? We only need to learn how to give them instructions to follow!

Related Posts:


Connect on YouTube, LinkedIn, Twitter.

Hey, I'm Victor Chan

Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?

Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.

That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.

Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!


Recommended Posts

Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.

Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.

Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!

Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.

Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.

RECOMMENDED READING

Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.

Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!

Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.

JOIN FREE EMAIL NEWSLETTER

Step up your Excel game! Join our free email newsletter and get updates on how to become more awesome at Excel.