asked on

MS access vba turn of filter in Excel worksheet

From Ms access using VBA turn off filter in MS Excel sheet. Some time user leave the filter turn on in Excel sheet. This cause the import to fail.
Ms Acess 2010
Excel 2010

I need to turn the filter off in the Excel sheet using VBA from Ms Access.

Thank you

mbizup

How are you currently importing your data? If you are using Excel Automation try setting the autofilter mode of your worksheet object:

xlWS.AutoFilterMode = False

if you are not currently using automation, it is done similar to this:

Dim xlApp as Excel.Application
Dim xlWB  Excel.Workbook
Dim xlWS as Excel.Worksheet

Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open("C:\Yourfilename.xls")
Set xlSH = xlWB.Sheets("YourWorkSheet")

if xlWS.AutoFilterMode = True then xlWS.AutoFilterMode = False

 ' etc

' clear up objects

xlwb,close
xlapp.quit
set xlwb = nothing
set xlapp = nothing
setxlws = nothing

Open in new window

newholyman

ASKER

received error message object does not support this object or method

Dim xlObj As Object, Sheet As Object
Dim xlFile As String
xlFile = ("file")
Rem xlFile = wrk_copy
Set xlObj = CreateObject("Excel.Application")
xlObj.Workbooks.Open xlFile
xlObj.Visible = True
Set Sheet = xlObj.ActiveWorkbook.Worksheets("sheetname")
If xlObj.AutoFilterMode = True Then xlObj.AutoFilterMode = False
xlObj.ActiveWorkbook.Save
xlObj.ActiveWorkbook.Close

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

newholyman

ASKER

Here the final vba that worked;
Dim xlObj As Object, Sheet As Object
Dim xlFile As String
xlFile = ("file")
Rem xlFile = wrk_copy
Set xlObj = CreateObject("Excel.Application")
xlObj.Workbooks.Open xlFile
xlObj.Visible = True
Set Sheet = xlObj.ActiveWorkbook.Worksheets("sheetname")
If Sheet.AutoFilterMode = True Then Sheet.AutoFilterMode = False
xlObj.ActiveWorkbook.Save

Thank you

mbizup

If the suggestion I posted did the trick (If Sheet.AutoFilterMode = True Then Sheet.AutoFilterMode = False), why did you award a B grade?

EE's grading standards are here:

http://support.experts-exchange.com/customer/portal/articles/481419-what-grade-should-i-award-?b_id=44

A B grade means the solution given lacked some information or required you to do a good amount of extra work to resolve the problem. When closing the question, the asker should explain why a B grade was awarded.