Adding Excel Data Filters Using Automation

<< Click to Display Table of Contents >>

Navigation:  Office365 and PowerApps >

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