delete table entry by date 
Author Message
 delete table entry by date

I have a table in a document that has the date in the first column of a
table. I would like to delete the table row when the date is 12 months on
from the date in the table. Preferably when the 12 months has passed a
message box would open when the doc was opened (Auto open macro?) requesting
confirmation of row to be deleted. I have a working knowledge of userforms
but am a bit stumped about how to do this . I would appreciate any
suggestions please

Thanks, in anticipation,
Andrew.



Fri, 31 Oct 2003 03:10:07 GMT  
 delete table entry by date


Quote:
> I have a table in a document that has the date in the first column of a
> table. I would like to delete the table row when the date is 12 months on
> from the date in the table. Preferably when the 12 months has passed a
> message box would open when the doc was opened (Auto open macro?) requesting
> confirmation of row to be deleted. I have a working knowledge of userforms
> but am a bit stumped about how to do this . I would appreciate any
> suggestions please

Hi Andrew,

assuming the table is the first or only one and the dates are the only text in
the cells of the first column this code might do the job (Word97 SR2).

Uwe Marx

'*****
Sub PromptForDeleteOldRecords()
Dim oTable As Table
Dim oRow As Row
Dim oCell As Cell
Dim Rng As Range
Dim txtDate As Date

Dim CompareDate As Date

Dim txt$

Set oTable = ActiveDocument.Tables(1)

For Each oRow In oTable.Rows
    Set oCell = oRow.Cells(1)
    Set Rng = oCell.Range
    Rng.End = Rng.End - 1
    txt$ = Rng.text
    On Error Resume Next
    If txt$ = "" Then GoTo EndNext
    txtDate = CDate(txt$)
    CompareDate = DateAdd("m", 12, txtDate)
    If CompareDate < Date Then
        If MsgBox(txtDate & " - Date is at least 1 year old" _
        & vbCr & "Delete this row?", _
            vbYesNo) = vbNo Then GoTo EndNext
        oRow.Delete
    Else
    End If
EndNext:
Next
MsgBox "No or no more records older than 1 year"
End Sub
'*****



Fri, 31 Oct 2003 04:20:00 GMT  
 delete table entry by date
Thanks Uwe, it works perfectly in Word 97 SR2 and Word 2000. I appreciate
you taking the time to help me out. I will have a good look at the code and
I am sure I will learn a lot from it.

Regards, Andrew


Quote:


> > I have a table in a document that has the date in the first column of a
> > table. I would like to delete the table row when the date is 12 months
on
> > from the date in the table. Preferably when the 12 months has passed a
> > message box would open when the doc was opened (Auto open macro?)
requesting
> > confirmation of row to be deleted. I have a working knowledge of
userforms
> > but am a bit stumped about how to do this . I would appreciate any
> > suggestions please

> Hi Andrew,

> assuming the table is the first or only one and the dates are the only
text in
> the cells of the first column this code might do the job (Word97 SR2).

> Uwe Marx

> '*****
> Sub PromptForDeleteOldRecords()
> Dim oTable As Table
> Dim oRow As Row
> Dim oCell As Cell
> Dim Rng As Range
> Dim txtDate As Date

> Dim CompareDate As Date

> Dim txt$

> Set oTable = ActiveDocument.Tables(1)

> For Each oRow In oTable.Rows
>     Set oCell = oRow.Cells(1)
>     Set Rng = oCell.Range
>     Rng.End = Rng.End - 1
>     txt$ = Rng.text
>     On Error Resume Next
>     If txt$ = "" Then GoTo EndNext
>     txtDate = CDate(txt$)
>     CompareDate = DateAdd("m", 12, txtDate)
>     If CompareDate < Date Then
>         If MsgBox(txtDate & " - Date is at least 1 year old" _
>         & vbCr & "Delete this row?", _
>             vbYesNo) = vbNo Then GoTo EndNext
>         oRow.Delete
>     Else
>     End If
> EndNext:
> Next
> MsgBox "No or no more records older than 1 year"
> End Sub
> '*****



Sat, 01 Nov 2003 02:55:28 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Deleting entry from table using ODBC - please help

2. Delete all the entries of a table

3. Delete all the entries of a table

4. Delete all the entries of a table

5. Counting Tables/Delete by Create Date

6. Exporting - Produces dates for NULL Date entries

7. need help please:joined tables,add new entries based on one table columns

8. need help please:joined tables,add new entries based on one table columns

9. ms-access - delete old dates and change dates

10. delete a date in a bounded date field

11. delete a date in a bounded date field

12. delete a date in a bounded date field

 

 
Powered by phpBB® Forum Software