auto update from password protected files vba?

[tmmellor1981]

Hi all

i know this is possible but i'm a bit of a novice and can't get any of the code's i've copied to work, sure someone will find this pretty easy in here.

I have a workbook containing many vlookups to various other workbooks on a network, 2 of the workbooks i link to have passwords on. I would like the update links button on opening disabled and for excel to update all links on open and save the passwords for the 2 files so it requires no user input, for arguements sake lets say the files are:

s:\dashboard\test.xls - password of "password1"
s:\dashboard\numbers.xls password of "password2"

if this cannot be done automatically on open could it be assigned to a button?

hope i made sense to everyone thanks for you help as always

 

[Domski]

Try:

Sub Open_Stuff()
Workbooks.Open Filename:="s:\dashboard\test.xls", UpdateLinks:=3, Password:="password1"
Workbooks.Open Filename:="s:\dashboard\numbers.xls", UpdateLinks:=3, Password:="password2"
End Sub

Dom

 

[tmmellor1981]

sorry if i sound stupid do i put that in a module ?

 

[Domski]

Yes, that's correct. You can then add a button to one of your toolbars and attach the macro to it.

If you want to have it always available it would be worth creating a Personal Macro Workbook to hold the code.

Dom

 

[tmmellor1981]

Hi again

thanks for you help it seems to work only issue is that both workbooks are in excess of 7 meg each and take forever to open is there any way of updating links without opening the files like that auto update does ?

regards

 

[Domski]

Nope, links within a workbook only update when it is opened.

Dom

 

[tmmellor1981]

sorry for the repeat questioning , is it possible then to have code automatically close the workbooks after links have updated ?

 

[Domski]

Try:

Sub Open_Stuff()
Workbooks.Open Filename:="s:\dashboard\test.xls", UpdateLinks:=3, Password:="password1"
ActiveWorkbook.Close SaveChanges:=True
Workbooks.Open Filename:="s:\dashboard\numbers.xls", UpdateLinks:=3, Password:="password2"
ActiveWorkbook.Close SaveChanges:=True
End Sub

Dom

 

[tmmellor1981]

many thanks for all your help greatly appreciated