VBA to enter passwords for linked workbooks at opening

Hi,

I frequently lookup to 3 core spreadsheets to check for completion/action dates against task references in my main tracking spreadsheets (we don't have great systems at work, so we are quite reliant on spreadsheets in order to manage tasks, and associated actions)


So when opening, say, my compliance certificate tracker workbook, it asks for the passwords for the 3 Workbooks belonging to my team - which hold the due and order dates for the compliance tasks due on their portfolios.

I have a number of workbooks which always lookg at these 3 workbooks - so it would be really handy if I and my admin didn't have to type the passwords in each time they need to update or check the information (quite frequently).

Is there VBA code that allows you to open the workbook, and at that point of opening the passwords are automatically completed?

I've tried recording a macro but this didn't seem to work. I'm a novice at VBA so simple is good!

Help gratefully received.

Rebecca.

Rebecca.

The Workbooks.Open Method has a Password parameter.

Of course if you put the passwords in there anyone who has access to your macro can see the passwords unless you Protect your VBA Project with a password and hide it's content, all done with the Tools->VBA Properties...->Protection dialog.

HTH

You can put the password string into a DLL. It will not be in your macro then.
Find and download the ResEdit.exe from internet, learn how to create a DLL and how to read it. More, if you have some pictures in worksheets or userforms, you can place them in the DLL, too, and read them during workbook opening procedure or (better) when you need them.

Thanks - the link is helpful. It doesn't matter about the passwords being visible in the code.

However if it try to use the code as follows in 'ThisWorkbook' I am still asked for the passwords for the linked workbooks on opening my tracker:

Sub OpenUp()
Workbooks.Open Filename:="L:\Consultancy\Regional Info\Buildings\Brian w finance.xlsm", Password:="Pass"
End Sub


Unsure if I'm doing the wrong thing, the right thing badly, or the right thing in the wrong place? any advice?

retired007geek said:

Rebecca.

The Workbooks.Open Method has a Password parameter.

Of course if you put the passwords in there anyone who has access to your macro can see the passwords unless you Protect your VBA Project with a password and hide it's content, all done with the Tools->VBA Properties...->Protection dialog.

HTH

Click to expand...

Rebecca,

Are you concerned about who sees what is in the linked workbooks or only who can change them?

If you are only worried about who can change them they can be assigned Modify/Edit Passwords which will only allow people with the passwords to make changes and save.

With this done you won't have any problems opening them to extract linked data from them. Of course, this assumes you don't do any writing to them.

You may find this information useful in controlling the updating of Links.

HTH