Filter criteria for Excel using VBA in Access

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

LizzJAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rory ArchibaldConnect With a Mentor Commented:
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
 
Rory ArchibaldCommented:
You need to pass myDate as a String, formatted the same way the cells are.
HTH
Rory
0
 
LizzJAuthor Commented:
the cells contain dates.

can you provide the right way of doing such conversion? tried a few, but doens't seem to work
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
LizzJAuthor Commented:
btw, the dates are in format: dd/mm/yyyy
0
 
Rory ArchibaldCommented:
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
 
LizzJAuthor Commented:
Can you provide the codes for that in such a case? Not much idea on how to specify the range...
0
 
Rory ArchibaldCommented:
Sure - is there a column that should always have data for each row?
0
 
LizzJAuthor Commented:
the column will be D
0
 
Rory ArchibaldCommented:
Ok - I'll put something together when I'm back in the office, hopefully later this morning?
0
 
LizzJAuthor Commented:
okie. Thanks. Please also let me know how to fix the date error
0
 
Rory ArchibaldCommented:
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
 
LizzJAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.