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?
 
[ 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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rob HensonFinance AnalystCommented:
Put the file in the non-secure location but password protect for modify permission.
0
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.