S
ssGuru
Thanks for the fun stuff. We really enjoy the group and your code
examples.
I have an Excel 2003 file with a Master table worksheet with named
fields, let's call it "Master". It is the dynamic result of many
report files.
I receive periodic reports based on an Excel report template I created
that has a worksheet with most of the same named fields as the Master,
let's call it "Report". The "Master" records have a couple of extra
special purpose fields that the "Report" records do not.
The "Report" template also has a configuration worksheet with a couple
of fields unique to this report. "Partner" which contains the name of
the person reporting and "ReportDate" which is the date this "Report"
template was sent. The "Partner" and the "ReportDate" values need to
be added/edited to NEW records when they are added/edited to the
"Master".
I need to append/update an Excel "Master" table by evaluating the
records in other Excel "Report" files (weekly reports) containing a
template worksheet with a similar structure and mostly the same named
fields/columns structure.
I use two common fields/columns as a unique recordIDs to use as a
unique comparison ID between the records in the "Master" and "Report"
files. A field in both "Master" and "Report" worksheets named
"PipeDate" (short date format) and a field in both "Master" and
"Report" worksheets named "Prospect" (text format, which for example
contains a company name.) Programmatically concatenating these two
fields together should give me enough of a unique recordID for my
comparison purposes.
When the "Report: template is received it will contain;
1. some new records (records NOT currently in the "Master" table),
2. some unchanged records (records in the "Master" table and the
"Report" file that match in all common fields)
3. some old records where one or more fields have been updated in the
"Report".
I wish to append the NEW records, those where the "PipeDate&Prospect"
unique ID in a "Report" record do NOT match any record in the "Master"
table. For each NEW record appended to the "Master" I need to add the
"Partner" template value from to the "Partner" field in each NEW
appended record and add the "ReportDate" value to a field named
"LastUpDate" (short date) which is only in the record once it is
appended to the "Master" table. (Later I may try and write code that
will update this field to NOW in the "Report" template IF the user
changes any field at any time. Then I can just capture this date as
part of the update evaluation process)
I wish to inspect all other "Report" template records and compare
field by field to existing records in the "Master". If no difference
are observed in ANY common fields then no change to the "LastUpDate"
date value in the "Master".
If inspection of the template record reveals ANY different data value
in any common field for a record already in the "Master" table then I
want to update the "Master" table with the latest/best data from the
report record and change the value in the "Master" table field
"LastUpDate" to the "ReportDate" in the "Report" template to track
when this record was last updated.
Don't know if others have similar needs but I have needed this skill
for a couple of different projects. Easier to do using Access but my
client wishes to keep this all in Excel.
Appreciate any help, suggestions, guidance, cosmic thoughts, etc.....
Dennis
examples.
I have an Excel 2003 file with a Master table worksheet with named
fields, let's call it "Master". It is the dynamic result of many
report files.
I receive periodic reports based on an Excel report template I created
that has a worksheet with most of the same named fields as the Master,
let's call it "Report". The "Master" records have a couple of extra
special purpose fields that the "Report" records do not.
The "Report" template also has a configuration worksheet with a couple
of fields unique to this report. "Partner" which contains the name of
the person reporting and "ReportDate" which is the date this "Report"
template was sent. The "Partner" and the "ReportDate" values need to
be added/edited to NEW records when they are added/edited to the
"Master".
I need to append/update an Excel "Master" table by evaluating the
records in other Excel "Report" files (weekly reports) containing a
template worksheet with a similar structure and mostly the same named
fields/columns structure.
I use two common fields/columns as a unique recordIDs to use as a
unique comparison ID between the records in the "Master" and "Report"
files. A field in both "Master" and "Report" worksheets named
"PipeDate" (short date format) and a field in both "Master" and
"Report" worksheets named "Prospect" (text format, which for example
contains a company name.) Programmatically concatenating these two
fields together should give me enough of a unique recordID for my
comparison purposes.
When the "Report: template is received it will contain;
1. some new records (records NOT currently in the "Master" table),
2. some unchanged records (records in the "Master" table and the
"Report" file that match in all common fields)
3. some old records where one or more fields have been updated in the
"Report".
I wish to append the NEW records, those where the "PipeDate&Prospect"
unique ID in a "Report" record do NOT match any record in the "Master"
table. For each NEW record appended to the "Master" I need to add the
"Partner" template value from to the "Partner" field in each NEW
appended record and add the "ReportDate" value to a field named
"LastUpDate" (short date) which is only in the record once it is
appended to the "Master" table. (Later I may try and write code that
will update this field to NOW in the "Report" template IF the user
changes any field at any time. Then I can just capture this date as
part of the update evaluation process)
I wish to inspect all other "Report" template records and compare
field by field to existing records in the "Master". If no difference
are observed in ANY common fields then no change to the "LastUpDate"
date value in the "Master".
If inspection of the template record reveals ANY different data value
in any common field for a record already in the "Master" table then I
want to update the "Master" table with the latest/best data from the
report record and change the value in the "Master" table field
"LastUpDate" to the "ReportDate" in the "Report" template to track
when this record was last updated.
Don't know if others have similar needs but I have needed this skill
for a couple of different projects. Easier to do using Access but my
client wishes to keep this all in Excel.
Appreciate any help, suggestions, guidance, cosmic thoughts, etc.....
Dennis