open excel from Access

[dar]

I have Access 2000, with Excel on a Shared Drive.
I need to open a specific wkbk "ClinicTECList.xls" on the N: drive
I need to be able to loop through the 9 wksh and go to Column D and delete any
row (s) that have a Date earlier than today.
Most importantly, I NEED HELP in the coding, down to the basics.

Help would be appreciated.
Other forum sent me here. If code is supplied, please indicate where I
place it and
how I activate the code.

 

[Tom Ogilvy]

You have Access 2000
You have Excel 2000

From which application do you want to run code that does what you describe?
If you can do it from Excel it will be much simpler.

 

[dar]

I will do it from either. If Excel is simpler, I'm all for that.
Thank you in advance for any help you provide.

 

[Tom Ogilvy]

Run this from Excel. (see caution at the bottom).

Open Excel ( a new workbook should be created)
do Alt+F11 to get to the VBE
in the menu do Insert => Module
In the resulting module paste the below code.
(modify the code to reflect the path to the workbook if necessary)
do Alt+F11 to get back
Save the file

go to the tools menu and do

Macro=>Macros
ProcessWorkbook should be highlighted (if not highlight it), then click run.

This will open the ClinicTECList workbook and delete the appropriate rows
on all sheets in the workbook.

Sub ProcessWorkbook()
Dim bk As Workbook, sh As Worksheet
Dim rng As Range, lastrow As Long
Set bk = Workbooks.Open("N:\ClinicTECList.xls")
For Each sh In bk.Worksheets
lastrow = sh.Cells(Rows.Count, 4).End(xlUp)
For i = lastrow To 1 Step -1
Set rng = sh.Cells(i, 4)
If Not IsEmpty(rng) Then
If IsDate(rng) Then
If rng < Date Then
rng.EntireRow.Delete
End If
End If
End If
Next
Next
End Sub


Since you are deleting data, make a copy of your workbook before testing the
macro.

 

[dar]

When I try to run the Macro, it gives me an error message of
"Type mismatch" and it highlights the row
lastrow = sh.Cells(Rows.Count,4).End(xlUp)
when I put the cursur over Count it read 65536 and when I put if over xlUp
it reads -4162. Over lastrow it reads lastrow = 0

 

[Tom Ogilvy]

lastrow = sh.Cells(Rows.Count,4).End(xlUp)
should be

lastrow = sh.Cells(Rows.Count,4).End(xlUp).Row

 

[dar]

Thank you, that worked.

lastrow = sh.Cells(Rows.Count,4).End(xlUp)
should be

lastrow = sh.Cells(Rows.Count,4).End(xlUp).Row