Verifying if new data has been added to excel file ?

[Dan Thompson]

Hi,
I have about 1000 excel workbooks I work with on a regular basis each one of
them has data and 1 chart. All of the Workbooks and charts get updated each
month. however some of them do not do to a lack of available data. I am
wondering if there is a macro that can check each workbook to see if new data
has been added since the last time the macro was run and generate a master
list in a separate workbook of all the work books that have new data since
the last time the macro was run and a list of all the workbooks that have not
had new data since the last time the macro was run.

Can anyone help either with a macro or point me to a site that may already
have some kind of macro like this ?

Thanks,
Dan Thompson

 

[Bernie Deitrick]

Dan,

You could check the file date, if the only reason a file is saved is that
data has been added to it. If so, a macro would find them easily. Otherwise,
you would need a different approach.

Let us know,

Bernie
MS Excel MVP

 

[Tim Williams]

Does new data always get appended to existing data (as new rows), or
can new data replace old data?
Are there multiple locations in each file where data could be added ?

Where are the filesd stored - all in one folder ?
How are new files added, and do old files get removed ?

Tim

 

[Dan Thompson]

Ok I will try to explain the process a little clearer.
I collect data once a month and manually enter it into an Access Database
the data that gets entered in the data base is appended to already existing
datasets that are represented by 4 character legend names such as ODS1 ODS2
exc... Once all the data has been collected for that month and entered into
the database it is than exported from Access into Excel Slave files
(Workbooks) the data from the slave files are already linked to Master Excel
files (The ones with my chart in them)
So The data gets exported from the database to slave file and than when I
open my master file it shows the new data appended to the existing data. My
Master files (The ones with the chart) Already have a macro that runs on
Workbook open and it extends the plotted chart lines to reflect any new data
that was not there before. However because not all the data sets I collect
have new data each month some of the charts will have new data and some will
not. It is very difficult to tell just by looking at the ploted chart line if
there has been new data since last month so I have to manually look at the
data to tell if any given chart has new data this month. All of the appended
new data is appended by rows the columns are constant and never change.

And using the file date modified is not accurate because it is possible to
open the file and make changes without any new data having been added.

So I guess I am looking for a macro that will identify if a new row of data
has been added to the sheet since the last time.

Hope this makes my question more clear ?

Dan Thompson

 

[Tim Williams]

Why not just extend your workbook_open macro to flag when the plot
range has been expanded ?

It's hard to know exactly what you mean by "the last time" - the last
time what ? You note that the workbooks might be opened for other
reasons (and so change the modified date), but should this be the time
compared against when the workbook is next opened ?

It might be better to have only one "query" workbook, rather than
creating a separate copy for each: seems like a lot of work to manage
1k workbooks...

Tim