Archiving specific records to access

[Guest]

Hello,
I have a workbook with 8 spreadsheets for different business units. These
spreadsheets contain lists of customer information. The two key columns in
these spreadsheets are 'START-DATE' and 'ARCHIVE-DATE'. The start-date is
entered by the user and the archive-date is simply that start-date plus 7
days.

I need to automate the task of selecting the rows where the 'ARCHIVE DATE'
column is equal to todays date. These records will be deleted from the
spreadsheet and archived in an access database. I was hoping to set it up so
that when the workbook is opened, this archiving process will be automated.
What would be the best way around this problem?

All suggestions will be gratefully received

 

[Myrna Larson]

Do you have the necessary code to extract the data?

One possible sequence of actions would be

1. use Data/AutoFilter to display only records with the current date in the
Archive column

2. copy these records to another sheet

3. delete them from the main sheet

4. do whatever is necessary to import the copied records into Access

For me, assuming you want to also automate step 4, that's the hardest part.

It may be possible to import records directly from Excel into Access with a
criterion such as the date. I'm not good enough with Access programming to
know. If that is true, you would not need steps 1-3. If that works, then your
2nd task would be to delete the records from Excel (easy).

Once you have the macro written, you would call it from the Workbook_Open
event macro.

If you follow the steps above, you could turn on the macro recorder as you do
it manually to get an idea of what the code looks like. Then you would modify
it to change the filter criterion to the current date. (BTW, what if a date
got skipped? Would you want to archive all records with dates <= current
date?)

 

[Jamie Collins]

It may be possible to import records directly from Excel into Access with a
criterion such as the date.

You can bypass MS Access and put the data directly into the database
e.g.

INSERT INTO MyArchive ([START-DATE], [ARCHIVE-DATE], <other columns
here>)
SELECT [START-DATE], [ARCHIVE-DATE], <other columns here>
FROM [Excel 8.0;HDR=YES;Database=C:\MyBook.xls;].[BusinessUnit1$]
WHERE [ARCHIVE-DATE] = DATE()
UNION ALL
SELECT [START-DATE], [ARCHIVE-DATE], <other columns here>
FROM [Excel 8.0;HDR=YES;Database=C:\MyBook.xls;].[BusinessUnit2$]
WHERE [ARCHIVE-DATE] = DATE()
UNION ALL
SELECT [START-DATE], [ARCHIVE-DATE], <other columns here>
FROM [Excel 8.0;HDR=YES;Database=C:\MyBook.xls;].[BusinessUnit3$]
WHERE [ARCHIVE-DATE] = DATE()
UNION ALL
....
UNION ALL
SELECT [START-DATE], [ARCHIVE-DATE], <other columns here>
FROM [Excel 8.0;HDR=YES;Database=C:\MyBook.xls;].[BusinessUnit8$]
WHERE [ARCHIVE-DATE] = DATE();

Perhaps the criterion required is <= DATE(), though.

Jamie.

--