Using one file to make changes in another file

[Horatio J. Bilge, Jr.]

How would I go about using code in one excel file to change the contents of a
different file?

I have a workbook for swimmers to keep track of times. It tracks their best
times, and then compares the best time to a table of time standards. The
issue is that the time standards change periodically, and I want to give
users an update file that will update the time standards in the original
workbook, without requiring them to re-enter all of their times.

What I envision is having a sheet in the update file with the new time
standards, and using vba to replace the old standards with the new ones. But
I am open to other methods.

In the target workbook, I have workbook structure protection, vba project
protection, and worksheet protection. Also, the target time standards sheet
is hidden.

Thanks in advance for your help,
~ Horatio

 

[Gary''s Student]

Rather than update values in the user workbooks, have the user workbooks link
to a standards workbook (which you periodically distribute).

1. the standards workbook would have to be distributed in either case
2. you don't need to know where the data appears in the user workbooks
3. you avoid the need for macros

 

[Horatio J. Bilge, Jr.]

Unfortunately, the workbook is already distributed, and quite a few people
are using it. While I can see the benefits of linking to a standards
workbook, at this point, it would mean that all of the users would have to
start from scratch and re-enter all of their times.

I know exactly where the data is in the users' workbooks. The various
protections that I mentioned were put in place by me, and the sheet was
hidden to prevent users from poking around and changing things.

The workbook as it is now already uses quite a few macros, so that isn't
something I am worried about (e.g., Chip Pearson's time quick entry, user
information forms, customized views, force to open maximized).

Although I might eventually have to go your route and link to a standards
workbook, is it even possible to do what I am thinking?

~ Horatio

 

[Gary''s Student]

It is possible. Check back tomorrow for some code samples.

 

[Horatio J. Bilge, Jr.]

Thanks. I look forward to seeing the code.
~ Horatio