Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verifying if new data has been added to excel file ?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verifying if new data has been added to excel file ?
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 "Dan Thompson" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verifying if new data has been added to excel file ?
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 On Dec 21, 10:50*am, Dan Thompson wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verifying if new data has been added to excel file ?
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" wrote: 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 On Dec 21, 10:50 am, Dan Thompson wrote: 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Verifying if new data has been added to excel file ?
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 On Dec 22, 12:13*pm, Dan Thompson wrote: 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" wrote: 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 On Dec 21, 10:50 am, Dan Thompson wrote: 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 .- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Verifying uniqueness of records in data | Excel Discussion (Misc queries) | |||
Verifying that data exist | Excel Programming | |||
verifying data type | Excel Programming | |||
Verifying all data in each row is filled in | Excel Programming | |||
Way to prevent VLOOKUP from verifying remote file exists? | Excel Programming |