open excel from Access

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.

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.


--
Regards,
Tom Ogilvy


"dar" <[email protected]> wrote in message
news:[email protected]...
>
> 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.

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" wrote:

> 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.
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "dar" <[email protected]> wrote in message
> news:[email protected]...
> >
> > 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.
>
>
>

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.

--
Regards,
Tom Ogilvy



"dar" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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.
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "dar" <[email protected]> wrote in message
> > news:[email protected]...
> > >
> > > 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.
> >
> >
> >

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" wrote:

> 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.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "dar" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> > > 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.
> > >
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "dar" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >
> > > > 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.
> > >
> > >
> > >
>
>
>

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

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

--
Regards,
Tom Ogilvy

"dar" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "dar" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" wrote:
> > >
> > > > 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.
> > > >
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "dar" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > >
> > > > > 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.
> > > >
> > > >
> > > >
> >
> >
> >

Thank you, that worked.


"Tom Ogilvy" wrote:

> lastrow = sh.Cells(Rows.Count,4).End(xlUp)
> should be
>
> lastrow = sh.Cells(Rows.Count,4).End(xlUp).Row
>
> --
> Regards,
> Tom Ogilvy
>
> "dar" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> > > 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.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > > "dar" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" wrote:
> > > >
> > > > > 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.
> > > > >
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > >
> > > > > "dar" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > >
> > > > > > 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.
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>