VBA Code to Update Links

[stephen.smith]

Hey Guys I am trying to update links from other workbooks by using VBA rather than updating through the standard Edit Links-> Update all values. Does anyone know how to do this with VBA. I know this is probably very basic but I cant seem to get it to work.
Thanks Again for anyone that has a look and helps me out.
Stephen

 

[stephen.smith]

Sorry I should also mention that some of the workbooks that I am trying to update with this VBA are password protected. Can I put the password in the code so that it doesnt prompt me to enter the password.
Thanks
Stephen

 

[stephen.smith]

Hi guys if anyone can help with this I would appreciate it. Still having no luck with it.

 

[xenou]

I don't see any option to provide a password in the updatelink method - it doesn't appear to be possible.

 

[stephen.smith]

What about performing an open and close formula and putting the password in with it. Is this possible??

 

[xenou]

Sub Foo()
    Workbooks.Open Filename:="C:File1.xls"
    Workbooks.Open Filename:="C:\File2.xls", Password:="1234"
    Workbooks.Close "File1.xls"
    Workbooks.Close "File2.xls"
End Sub

Personally I'd just remove the passwords. Use a secure folder which is better protection anyway.

 

[stephen.smith]

Hi thanks for that I have adjusted it slightly to add in a save for the workbooks that open however I am getting a message when the workbooks open saying "The workbook contains one or more links that cannot be updated. I then have to choose "Continue" or "edit links". How can I bypass this or what is going wrong? I cant take the passwords off the individual files as they are all sensitive documents for different employees and are accessed individually. Thanks for the help so far.
Stephen

 

[xenou]

I'm not sure this is what you want to hear but what I would do is fix the bad links ...

 

[taurean]

Suppress the application alerts by:

Sub Alerts()
Application.DisplayAlerts = False
'Place your code here!
Application.DisplayAlerts = True
End Sub

Edit: And what Xenou has said as if the whole thing is messed up then chances are that you won't even notice!