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 vb123.com.au 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")

xlsSheet.Range("A1").Select

 

'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

'   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

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

'fxP

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

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

'fxP

'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

'fxP

'fxP     eg  dbpath = GetDBPath_FX

'fxP

'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

Else

 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