• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1386
  • Last Modified:

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
0
newholyman
Asked:
newholyman
  • 3
  • 2
1 Solution
 
mbizupCommented:
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

0
 
newholymanAuthor Commented:
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
0
 
mbizupCommented:
<<If you are using Excel Automation try setting the autofilter mode of your worksheet object >>

Your code is trying to set the autofilter mode of the *application* object (xlObj), which may be the cause of your error.

It should be on the worksheet object (declared as "Sheet" in your code)
0
 
newholymanAuthor Commented:
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
0
 
mbizupCommented:
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.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now