Go Back   Access World Forums > Microsoft Access Discussion > Macros

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-06-2018, 02:15 AM   #1
Lochwood
Newly Registered User
 
Join Date: Jun 2017
Posts: 53
Thanks: 15
Thanked 2 Times in 2 Posts
Lochwood is on a distinguished road
Export to Excel with filters enabled

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.

Lochwood is offline   Reply With Quote
Old 11-06-2018, 02:40 AM   #2
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,584
Thanks: 308
Thanked 402 Times in 387 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Export to Excel with filters enabled

You could create a macro in Excel to do it.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is online now   Reply With Quote
Old 11-07-2018, 05:06 AM   #3
Trevor G
Newly Registered User
 
Trevor G's Avatar
 
Join Date: Oct 2009
Location: Tamworth, Staffordshire
Posts: 2,329
Thanks: 4
Thanked 227 Times in 218 Posts
Trevor G will become famous soon enough Trevor G will become famous soon enough
Re: Export to Excel with filters enabled

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?

__________________
Trevor
I am on a learning curve of life, I know a little but like to share what I have learnt with others.
I am using Microsoft Office 2003 To 2016
Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.
Trevor G is offline   Reply With Quote
Old 11-07-2018, 05:24 AM   #4
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,847
Thanks: 55
Thanked 2,183 Times in 2,094 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Export to Excel with filters enabled

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

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
MS Access 2010 - Right Click - Text Filters And Export Menu Remains On Screen DaveCollins181 General 17 03-20-2017 08:44 AM
Export to Excel with filters Danick Modules & VBA 11 09-02-2014 10:42 AM
VBA - add Excel formula that filters on bold Rx_ Modules & VBA 0 08-15-2011 02:11 PM
Question Export A Form's Recordset To Excel (only export visible datasheet fields) ghudson General 15 12-08-2010 12:29 PM
Use filters on a subform like in excel. tacieslik Forms 10 10-22-2003 05:19 AM




All times are GMT -8. The time now is 07:45 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World