Export to Excel with filters enabled (1 Viewer)

Lochwood

Registered User.
Local time
Today, 03:15
Joined
Jun 7, 2017
Messages
130
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

Enthusiastic Amateur
Local time
Today, 10:15
Joined
Sep 21, 2011
Messages
14,038
You could create a macro in Excel to do it.?
 

Trevor G

Registered User.
Local time
Today, 10:15
Joined
Oct 1, 2009
Messages
2,341
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

..forever waiting... waiting for jellybean!
Local time
Today, 18:15
Joined
May 7, 2009
Messages
19,169
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:

Code:
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
 

Users who are viewing this thread

Top Bottom