Execute Excel Functions in Access

As a database developer, I sometimes help people convert their data from Excel into Access. Although both applications can store data in a table format, they are built for different purposes. Excel is a little bit easier to learn so a lot of people tend to use it first. Eventually, when the data gets bigger or more complicated for Excel, they find it is time to migrate their application into Access. Access is a lot better in handling relational data than Excel. However, Excel users sometimes find it frustrating when they realize some of the functions they typically use in Excel are not available in Access.

A good example of this is the NETWORKDAYS() function in Excel. This function returns the number of working days between two dates. This is a common requirement whether the data is in Excel or Access. Unfortunately, Access does not have an equivalent function for the same purpose. Instead, Access users are forced to create custom functions to do the same thing.

If you have not tried it before, creating a custom function in Access to mimic the functionality of the NETWORKDAYS() Excel function is not easy. There are a number of Access NETWORKDAYS() code examples available online. As it turns out, creating a duplicate Excel function in Access is not always necessary. We can execute some Excel functions through automation. The following code example shows how we can execute the Excel NETWOKDAYS() function from within Access.

Public Function NetWorkdays(StartDate As Date, EndDate As Date) As Long
'8/3/2017
'thedbguy@gmail.com
'Uses Excel's NetWorkdays() function

Dim xl As Object

Set xl = CreateObject("Excel.Application")

NetWorkdays = xl.WorksheetFunction.NETWORKDAYS(Format(StartDate, "yyyy-mm-dd"), _
    Format(EndDate, "yyyy-mm-dd"))

Set xl = Nothing

End Function

What the above code does is instantiate an Excel object and then use the WorksheetFunction method to execute the Excel NETWORKDAYS() function.

Users can use this technique to execute some built-in Excel functions if they can't find an equivalent Access function to do the same thing. I hope you find it useful.