Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Filter criteria for Excel using VBA in Access

Posted on 2009-04-01
12
Medium Priority
?
523 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 

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 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

571 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