Solved

Filter criteria for Excel using VBA in Access

Posted on 2009-04-01
12
508 Views
Last Modified: 2013-11-27
Hi Experts,

I'm having a application defined error when I run the following VBA code in Access that tried to do a filter in an Excel file:


Dim myDate as Date

Dim getRow As Integer

myDate = Date     
 

With wb.Worksheets("Sheet1")

        .AutoFilterMode = False 'To turn off the filter in case there is any

        With .Rows(8)

         .AutoFilterMode = True 'To turn on the filter

         .AutoFilter Field:=4, Criteria1:="New"

         .AutoFilter Field:=9, Criterial:=myDate 'This is the line that error happens

         .AutoFilter Field:=29, Criteria1:="=s-*"

        End With

        getRow = .Range("B2").End(xlDown).Row

        .Rows("2:" & getRow).Delete shift:=xlUp

End With

Open in new window

0
Comment
Question by:LizzJ
  • 6
  • 6
12 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24038059
You need to pass myDate as a String, formatted the same way the cells are.
HTH
Rory
0
 

Author Comment

by:LizzJ
ID: 24038116
the cells contain dates.

can you provide the right way of doing such conversion? tried a few, but doens't seem to work
0
 

Author Comment

by:LizzJ
ID: 24038119
btw, the dates are in format: dd/mm/yyyy
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24038246
I would recommend not using just Rows(8) to set the filter, but use the actual range you want filtered - it's much more reliable. Then your original syntax should work if the dates are real dates.
0
 

Author Comment

by:LizzJ
ID: 24046972
Can you provide the codes for that in such a case? Not much idea on how to specify the range...
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24047120
Sure - is there a column that should always have data for each row?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:LizzJ
ID: 24047161
the column will be D
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24047211
Ok - I'll put something together when I'm back in the office, hopefully later this morning?
0
 

Author Comment

by:LizzJ
ID: 24047360
okie. Thanks. Please also let me know how to fix the date error
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24047762
Quick question - your code starts the filter from row 8, which I assume is where the headers are, but then deletes from row 2 downwards - is that correct?
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 24047854
I've just looked at your code more closely - on the date line that errors, you have written:
 CRITERIAL:=
 rather than:
 CRITERIA1:=
 
if you change the L to a 1 that should fix that error. I just ran a quick test using this code, and it worked fine:
 
 

Sub FilterXL()

   Dim myDate As Date

   Dim getRow As Long

   Dim wb As Excel.Workbook, appXL As Excel.Application

   myDate = Date

   Set appXL = CreateObject("Excel.Application")

   appXL.Visible = True

   Set wb = appXL.workbooks.Open("C:\test\filter_test.xls")

   With wb.Worksheets("Sheet1")

      ' get last used row in column D

      getRow = .cells(.Rows.Count, "D").End(xlUp).Row

      .AutoFilterMode = False 'To turn off the filter in case there is any

      With .Range("A8", .cells(getRow, 29))

         ' filter col D

         .AutoFilter Field:=4, Criteria1:="New"

         ' filter col I

         .AutoFilter Field:=9, Criteria1:=myDate 'This is the line that error happens

         ' filter col AC

         .AutoFilter Field:=29, Criteria1:="=s-*"

      End With

      .Range("B2", .cells(getRow, "B")).SpecialCells(xlCellTypeVisible).EntireRow.Delete shift:=xlUp

   End With
 

End Sub

Open in new window

0
 

Author Comment

by:LizzJ
ID: 24047933
Thanks a lot! it works! Couldn't imagine the silly mistake bothered me whole day...

Anyway, I've got another related question regarding looping through the filtered records. But I'll post another question. Maybe you can also help me look through that?
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Hide vba in gp 7 48
Error in SQL Query 36 37
Recommendation vb6 to vb.net or others 14 44
Help with SQl and UNION 3 19
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now