Mirroring an Excel Workbook

I have an excel sheet that is used to import scheduling data. As it is in a secure location with sensitive data, only a few people have access to the sheet.  I would like to mirror that entire sheet to a read only copy in a different, unsecured, share location.  I need this to happen automatically whenever a change is made to the original sheet without relying on the user to remember to save the copy to the other location. How can I do this?  I searched on the forums, but the previous concepts seemed to be significantly more complex than I needed or referenced code and macros that were not included in the question.  Thanks in advance for any help!
josslmpdAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
I suspect the whole point of it being in a Secure location is so that it cannot be seen by anybody. Why would you then want a copy in an insecure location that can be seen by anybody?

If there are other people that need to see it, they will have to get access to the Secure location.
0
josslmpdAuthor Commented:
It can be seen by people, but we do not want them to be able to make changes to the file, hence the desire to automatically save a read only copy.  There is other information in the original location that is sensitive, but the actual schedule is not.
0
Rob HensonFinance AnalystCommented:
Put the file in the non-secure location but password protect for modify permission.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Roy CoxGroup Finance ManagerCommented:
You could actually have code to refresh the copy workbook on opening. Of course, that workbook would need permission to access the secure drive. Rob's suggestion would be simpler to implement.
0
josslmpdAuthor Commented:
What would that code look like? I understand that the modify password would be easier, but that doesn't prevent someone from deleting the file as a whole, which something we have experienced in the past.
0
Rob HensonFinance AnalystCommented:
With Roy's comment, it might be easier / more efficient if the secure location file has a macro to save a copy to the other location each time it is saved.
0
josslmpdAuthor Commented:
Yes.  This is what I would like.  How would I program that marco? I can't find the actual script I would need.
0
[ fanpages ]IT Services ConsultantCommented:
Hi,

If you wish to make a copy of a workbook when it is saved, simpy add the following code to the 'ThisWorkbook' code module (where "H:\Unsecure_Location_Filename.xls" refers to the [network] folder where the [unsecured] copy of the workbook will be stored):

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

  ThisWorkbook.SaveCopyAs "H:\Unsecure_Location_Filename.xls"
  
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
josslmpdAuthor Commented:
I have tried this a few times to no avail.  I added the code to the ThisWorkbook code module, but then when I try to open the macro screen it isn't displayed.  When I attempt to run it, I get an argument error.  Perhaps it cannot be done on my network.
0
Rob HensonFinance AnalystCommented:
It won't show in the list of macros because it is in the ThisWorkbook module. You can only run it by triggering the Save event, ie by saving the workbook. It wilk then run and save a copy.
0
josslmpdAuthor Commented:
Thanks for the help.  I never got it to work, but I anticipate that is due to my network permissions.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.