Sleeper: Updating Links from Files with Passwords

[GThorn228]

I am trying to automatically update 20 worksheets (all have passwords) in VBA. I have tried everything I can imagine.

Any ideas on how to send a password from a linked excel file in VBA?

Thanks,:dunno

[Zack Barresse]

Hi there! Welcome to the board!!

You can unprotect a sheet, or all sheets, in a workbook via VBA code; an example is here (http://www.vbaexpress.com/kb/getarticle.php?kb_id=142). Is this what you are talking about? If not, can you explain more? Do you already have some code? If so, can you post it?

[GThorn228]

Zack,

Here is the code that I am using to open each file and update the links. I can send the password easily when I open each file. However, each of the 20 files are over 2 MB. So it takes a while.

I just need to update the links. Really no need to open the files!


Sub Macro2()
' Macro2 Macro
' Macro recorded 2/9/2004 by Gary Thorn
' Keyboard Shortcut: Ctrl+u
Dim Pathname As String
Pathname = "C:\IMPACT 2005\All\"
file1 = Pathname + "IMPACT Alton 2005.xls"
file2 = Pathname + "IMPACT Barstow 2005.xls"
File3 = Pathname + "IMPACT Brownsville 2005.xls"
File4 = Pathname + "IMPACT Burlington 2005.xls"
File5 = Pathname + "IMPACT Destin 2005.xls"
File6 = Pathname + "IMPACT EmeraldCoast 2005.xls"
File7 = Pathname + "IMPACT FNNM 2005.xls"
File8 = Pathname + "IMPACT Ft Walton Beach 2005.xls"
File9 = Pathname + "IMPACT Gastonia 2005.xls"
File10 = Pathname + "IMPACT Harlingen 2005.xls"
File11 = Pathname + "IMPACT JacksonvilleIL 2005.xls"
File12 = Pathname + "IMPACT JacksonvilleNC 2005.xls"
File13 = Pathname + "IMPACT Kinston 2005.xls"
File14 = Pathname + "IMPACT Lima 2005.xls"
File15 = Pathname + "IMPACT Marysville 2005.xls"
File16 = Pathname + "IMPACT McAllen 2005.xls"
File17 = Pathname + "IMPACT New Bern 2005.xls"
File18 = Pathname + "IMPACT Odessa 2005.xls"
File19 = Pathname + "IMPACT Panama City 2005.xls"
File20 = Pathname + "IMPACT Porterville 2005.xls"
File21 = Pathname + "IMPACT Riograndevalley 2005.xls"
File22 = Pathname + "IMPACT Santa Rosa 2005.xls"
File23 = Pathname + "IMPACT Sedalia 2005.xls"
File24 = Pathname + "IMPACT Seymour 2005.xls"
File25 = Pathname + "IMPACT Shelby 2005.xls"
File26 = Pathname + "IMPACT Victorville 2005.xls"
File27 = Pathname + "IMPACT Weslaco 2005.xls"
File28 = Pathname + "IMPACT Yuma 2005.xls"
File29 = Pathname + "IMPACT Starpub 2005.xls"
File30 = Pathname + "IMPACT ENC 2005.xls"
'This code does NOT work......here is where I need help.
' ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources

' ActiveWorkbook.UpdateLink Name:=file1, Type:=xlExcelLinks, ("top10")

' ActiveWorkbook.UpdateLink Name:=file2, Type:=xlExcelLinks, Password:=("top10")

The code below works fine but opens every file individually and updates the links. How can I not have to open each file but yet update the links.


Workbooks.Open (File3), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT Brownsville 2004.xls").Close
Workbooks.Open (File4), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT Burlington 2004.xls").Close
Workbooks.Open (File5), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT Destin 2004.xls").Close
Workbooks.Open (File6), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT EmeraldCoast 2004.xls").Close
Workbooks.Open (File7), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT FNNM 2004.xls").Close
Workbooks.Open (File8), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT Ft Walton Beach 2004.xls").Close
Workbooks.Open (File9), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT Gastonia 2004.xls").Close
Workbooks.Open (File10), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT Harlingen 2004.xls").Close
Workbooks.Open (File11), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT JacksonvilleIL 2004.xls").Close
Workbooks.Open (File12), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT JacksonvilleNC 2004.xls").Close
Workbooks.Open (File13), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT Kinston 2004.xls").Close
Workbooks.Open (File14), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT Lima 2004.xls").Close
Workbooks.Open (File15), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT Marysville 2004.xls").Close
Workbooks.Open (File16), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT McAllen 2004.xls").Close
Workbooks.Open (File17), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT New Bern 2004.xls").Close
Workbooks.Open (File18), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT Odessa 2004.xls").Close
Workbooks.Open (File19), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT Panama City 2004.xls").Close
Workbooks.Open (File20), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT Porterville 2004.xls").Close
Workbooks.Open (File21), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT Riograndevalley 2004.xls").Close
Workbooks.Open (File22), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT Santa Rosa 2004.xls").Close
Workbooks.Open (File23), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT Sedalia 2004.xls").Close
Workbooks.Open (File24), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT Seymour 2004.xls").Close
Workbooks.Open (File25), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT Shelby 2004.xls").Close
Workbooks.Open (File26), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT Victorville 2004.xls").Close
Workbooks.Open (File27), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT Weslaco 2004.xls").Close
Workbooks.Open (File28), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT Yuma 2004.xls").Close
Workbooks.Open (File29), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT Hickory 2004.xls").Close
Workbooks.Open (File30), UpdateLinks:="3", Password:="hand3"
ActiveWorkbook.Save
Workbooks("IMPACT ENC 2004.xls").Close
End Sub

[Zack Barresse]

.. 'The code below works fine but opens every file individually and updates the links. How can I not have to open each file but yet update the links..

You can't. The file needs to be opened to update links. Kind of a bummer.

As far as your routine goes, you can take out the Save line ..


ActiveWorkbook.Save

.. and add a True to the end of the next line ..


Workbooks("IMPACT ENC 2004.xls").Close True

This stands for SaveChanges syntax. That will shorten your code up by a third. Why do you need to update the links this way? What is it that you are doing exactly?

here ya go