Export to Excel with filters enabled

[Lochwood]

I have a macro on a button that exports details in a query to excel. Is it possible for the macro to enable column filters within excel when exported? currently i am having to do this manually.

 

[Gasman]

You could create a macro in Excel to do it.?

 

[Trevor G]

You could look to convert the form button macro to VBA and then you can add the code to apply the filter no problem. Do you look to open the excel workbook when you run the macro?

 

[arnelgp]

you could create a Public Function in a Module, and call it within your macro:

macro:
RunCode fnAutoFilter("the path and filename of excel file")


the function:

Public Function fnAutoFilter(sFile As String)
Dim objExcel As Object
Dim objWB As Object
Dim objSH As Object
If Dir(sFile)<>"" Then
	Set objExcel = CreateObject("Excel.Application")
	Set objWB = objExcel.WorkBooks.Open(sFile, , False)
	With objWB.Sheets(1)
		.UsedRange.Cells.AutoFilter
	End With
	objWB.Close True
	Set ObjWB=Nothing
	objExcel.Close
	Set ObjExcel=Nothing
End If
End Function