Adding Excel Data Filters Using Automation

<< Click to Display Table of Contents >>

Navigation:  Office and Office 365 >

Adding Excel Data Filters Using Automation

Here is the Excel Automation code for Adding Filters


Option Compare Database

Option Explicit


'Create an Excel file by exporting from Access

'Open the excel file and add the data filter

'Useful if the Excel file is going to be moved to

'the internet with dropbox or OneDrive


'Add a reference to Microsoft Excel  XX.0 Object Library


'Create a table called zWorld_Demo or alter the code for your own table or query


'By Garry Robinson   November 2016

'Go to for 100's more samples


Private Sub cmdCreateFilter_Click()


Dim FileDir As String

Const FileName = "Exported.xlsx"



'Find the current folder where the Front-End database is located

FileDir = GetDBPath_FX


'First create the Excel file

DoCmd.OutputTo acOutputTable, "zWorld_Demo", "ExcelWorkbook(*.xlsx)", FileDir & FileName, False


Dim xls As Excel.Application

Dim xlsW As Excel.Workbook

Dim xlsSheet As Excel.Worksheet


Set xls = CreateObject("Excel.Application")

xls.Visible = True


xls.Workbooks.Open FileDir & FileName, , , , , , True

Set xlsSheet = xls.Worksheets("zWorld_Demo")



'Choose line 1 to simply add the filters, choose line 2 if you want to apply specific filters

'to your data and leave them in place.



'   xls.Selection.AutoFilter Field:=6, Criteria1:=">2000", Operator:=xlAnd


'Clear up excel references

Set xlsSheet = Nothing

Set xls = Nothing


End Sub


Function GetDBPath_FX(Optional DbPathStr, Optional DbNameStr) As String


'fxP     --------------------------------------------------------


'fxP-->  GetDBPath_FX - Return the directory path of the database


'fxP     DbPathStr "string" is a file path string (this is optional)

'fxP     DbNameStr "string" database name without folder is returned


'fxP     Return the directory path of the database that you are using now

'fxP     If you are using Access 2000, use the FXL9_startup module.



'fxP     eg  dbpath = GetDBPath_FX


'fx      Original This routine was published in SmartAccess newsletter #4

'fxP     --------------------------------------------------------



Dim strPath As String

Dim intLastSlash As Integer


If IsMissing(DbPathStr) Then

 strPath = CurrentDb.Name


 strPath = DbPathStr

End If


For intLastSlash = Len(strPath) To 1 Step -1

 If Mid(strPath, intLastSlash, 1) = "\" Then

   Exit For

 End If

Next intLastSlash


GetDBPath_FX = Left(strPath, intLastSlash)


If Not IsMissing(DbNameStr) Then

 DbNameStr = Mid(strPath, intLastSlash + 1, Len(strPath) - intLastSlash)

 'now remove the file type

 DbNameStr = Left(DbNameStr, InStr(DbNameStr, ".") - 1)

End If


End Function