VBA Assistance Required

[Alan Graham]

Afternoon all,

I'm hoping someone can help me.

Spent many an hour Googling for an answer and cant seem to get anywhere.

I plan on creating an Overtime sheet for my manager's convenience where she can access everyone's overtime hours for the month. I want each employee to have their own password protected Excel file which will link into the Managers Excel file.

I have an Excel file named (at present) Master.xlsx. (Managers File)

This Master file has several worksheets within which contain links to several password protected Slave Excel files. (Employee's Files)

What code is required to allow the manager to open the Master file, and update it with the Slave data without her having to enter everyone's password?

Personally I would like it (if possible) to open the Master file, VBA code to open / close the Slaves with password so that the Master can update everyone's overtime.

I hope this makes sense??

 

[dianacris]

I've never done anything like this but here's my two cents ....

The first problem I can think of is whether the slave files' passwords can be modified by the employees. If so, it would be difficult for any code to pass the correct password if the passwords could change.

I believe that when you open the master file you'll want to be sure updating links is manual rather than automatic because your code needs to pass the passwords to each linked file during the "refresh links" process.

Assuming you can keep a list of filenames and passwords which your VBA code will be able to read from, you will need a loop that goes through that list of files and passwords, and opens each file individually. You could keep this information on a particular worksheet in the file and just hide that sheet if it would be distracting.

Sub Open_Stuff()
Workbooks.Open Filename:="c:\test1.xls", UpdateLinks:=3, Password:="password1"
ActiveWorkbook.Close SaveChanges:=True
End Sub
End Sub

Hope this points you in the right direction.