Excel VBA Case Study #3 – File Checker Macro

[IMAGE] Excel VBA File Checker Macro

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

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

#1 – Why did I create the Macro?

» Back to contents

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.

[IMAGE] Folder and File Structure
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?

» Back to contents

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.

[IMAGE] Red is missing, green is present

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

» Back to contents

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.

[IMAGE] File Checker Dashboard
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?

» Back to contents

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).

[IMAGE] Four Code Modules used in the workbook

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

» Back to contents

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.

[IMAGE] Folder structure

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

» Back to contents

[IMAGE] Daniel Lajosbanyai

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!

Victor Chan
Hi I'm Victor. I started Launch Excel to help you use Excel and VBA faster. Thousands of people have learned new skills and increased their effectiveness at work with our help. You can do it too! Also I've personally used Excel lots since 2002. I use it mainly in accounting and finance but I also have a Masters degree in Engineering from Cambridge University. I hope you enjoy the site and tutorials!
Join over 9,000 subscribers

GET BETTER AT EXCEL

Recommended Posts

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Contact Us

Please send us an email and we'll get back to you, asap.

Not readable? Change text.