Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Protected Links
I have created a workbook to summarize data from other workbooks. In order
to accomplish this task, I linked data using formulas. I am trying to create a macro that updates the links. Here's the catch, all of the other workbooks that are linked have open passwords. So when I update the links I have to key in multiple passwords. I am trying to get around this by simply using vba to open up all the linked workbooks and then close them. However, when I do this, the links do not update...any help on this would be appreciated. End game, is I do not want to have to key any multiple passwords everytime I need to update links...I would rather have code to that for me. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Protected Links
I have found a way to open all the workbooks through vba code; however, I
cannot get the links to update. If I use vba code to the open the files, the links will not update in the summary workbook, but if I open up the files through the excel toolbar or windows explorer the links update fine. I am at a loss on this one and need help. I have attached the vba code below (grabbed from another post) and have tried to add an application.calculate etc. to the code but still not working. HELP!! Option Explicit Sub Macro3() Dim myFileNames As Variant Dim myPasswords As Variant Dim iCtr As Long Dim myRealWkbk As Workbook Dim myRealWkbkName As String Dim wkbk As Workbook myRealWkbkName = "\\Goant6\Account\Jeff_H\LRO\FY08\FY08 LRO Labor Consolidation.xls" myFileNames = Array("\\goant5\company\GO\FINANCE\LRO\LRO_LABR_TE MPLATES\FY08\LRO_LABR_Executive Office Group.xls", _ "\\goant5\company\GO\FINANCE\LRO\LRO_LABR_TEMPLATE S\FY08\LRO_LABR_Finance Group.xls", _ "\\goant5\company\GO\FINANCE\LRO\LRO_LABR_TEMPLATE S\FY08\LRO_LABR_Human Resources Group.xls", _ "\\goant5\company\GO\FINANCE\LRO\LRO_LABR_TEMPLATE S\FY08\LRO_LABR_Linehaul.xls", _ "\\goant5\company\GO\FINANCE\LRO\LRO_LABR_TEMPLATE S\FY08\LRO_LABR_Maintc Group.xls", _ "\\goant5\company\GO\FINANCE\LRO\LRO_LABR_TEMPLATE S\FY08\LRO_LABR_Mexico Group.xls", _ "\\goant5\company\GO\FINANCE\LRO\LRO_LABR_TEMPLATE S\FY08\LRO_LABR_Operations Support Group.xls", _ "\\goant5\company\GO\FINANCE\LRO\LRO_LABR_TEMPLATE S\FY08\LRO_LABR_Transportation Dept.xls", _ "\\goant5\company\GO\FINANCE\LRO\LRO_LABR_TEMPLATE S\FY08\LRO_LABR_Truckload Services.xls", _ "\\goant5\company\GO\FINANCE\LRO\LRO_LABR_TEMPLATE S\FY08\Ops SC LRO_FY08.xls") myPasswords = Array("EXE9311", _ "finance", _ "HRS2892", _ "TRN7563", _ "MNT5510", _ "MEX2275", _ "OPS7862", _ "DIS7563", _ "CAP4466", _ "CCO7287") If UBound(myFileNames) < UBound(myPasswords) Then MsgBox "check names & passwords--qty mismatch!" Exit Sub End If Set myRealWkbk = Workbooks.Open(Filename:=myRealWkbkName, UpdateLinks:=0) For iCtr = LBound(myFileNames) To UBound(myFileNames) Set wkbk = Nothing On Error Resume Next Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr), _ Password:=myPasswords(iCtr)) On Error GoTo 0 Application.Calculate If wkbk Is Nothing Then MsgBox "Check file: " & myFileNames(iCtr) Exit Sub End If wkbk.Close savechanges:=False Next iCtr End Sub "hurlbut777" wrote: I have created a workbook to summarize data from other workbooks. In order to accomplish this task, I linked data using formulas. I am trying to create a macro that updates the links. Here's the catch, all of the other workbooks that are linked have open passwords. So when I update the links I have to key in multiple passwords. I am trying to get around this by simply using vba to open up all the linked workbooks and then close them. However, when I do this, the links do not update...any help on this would be appreciated. End game, is I do not want to have to key any multiple passwords everytime I need to update links...I would rather have code to that for me. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Protected Links
(Saved from a previous post.)
Option Explicit Sub testme() Dim myFileNames As Variant Dim myPasswords As Variant Dim iCtr As Long Dim myRealWkbk As Workbook Dim myRealWkbkName As String Dim wkbk As Workbook myRealWkbkName = "C:\my documents\excel\book1.xls" myFileNames = Array("C:\my documents\excel\book11.xls", _ "C:\my documents\excel\book21.xls", _ "C:\my other folder\book11.xls") myPasswords = Array("pwd1", _ "pwd2", _ "pwd3") If UBound(myFileNames) < UBound(myPasswords) Then MsgBox "check names & passwords--qty mismatch!" Exit Sub End If Set myRealWkbk = Workbooks.Open(Filename:=myRealWkbkName, UpdateLinks:=0) For iCtr = LBound(myFileNames) To UBound(myFileNames) Set wkbk = Nothing On Error Resume Next Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr), _ Password:=myPasswords(iCtr)) On Error GoTo 0 If wkbk Is Nothing Then MsgBox "Check file: " & myFileNames(iCtr) Exit Sub End If wkbk.Close savechanges:=False Next iCtr End Sub (I got bored after 3 workbooks. You may want to test it with a couple to get it going.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm hurlbut777 wrote: I have created a workbook to summarize data from other workbooks. In order to accomplish this task, I linked data using formulas. I am trying to create a macro that updates the links. Here's the catch, all of the other workbooks that are linked have open passwords. So when I update the links I have to key in multiple passwords. I am trying to get around this by simply using vba to open up all the linked workbooks and then close them. However, when I do this, the links do not update...any help on this would be appreciated. End game, is I do not want to have to key any multiple passwords everytime I need to update links...I would rather have code to that for me. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Protected Links
Dave,
Thanks for your relply. Unfortunately, I already tried that code. For some reason when I use vba code to open the files, the links will not update. If I open up the file via non-code methods, the links update. I'm perplexed on this one...guess I will just enter in 50 passwords everytime I want to update...at this point that would have taken me less time than coming up with an "efficient" method to automate. Thanks again. "Dave Peterson" wrote: (Saved from a previous post.) Option Explicit Sub testme() Dim myFileNames As Variant Dim myPasswords As Variant Dim iCtr As Long Dim myRealWkbk As Workbook Dim myRealWkbkName As String Dim wkbk As Workbook myRealWkbkName = "C:\my documents\excel\book1.xls" myFileNames = Array("C:\my documents\excel\book11.xls", _ "C:\my documents\excel\book21.xls", _ "C:\my other folder\book11.xls") myPasswords = Array("pwd1", _ "pwd2", _ "pwd3") If UBound(myFileNames) < UBound(myPasswords) Then MsgBox "check names & passwords--qty mismatch!" Exit Sub End If Set myRealWkbk = Workbooks.Open(Filename:=myRealWkbkName, UpdateLinks:=0) For iCtr = LBound(myFileNames) To UBound(myFileNames) Set wkbk = Nothing On Error Resume Next Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr), _ Password:=myPasswords(iCtr)) On Error GoTo 0 If wkbk Is Nothing Then MsgBox "Check file: " & myFileNames(iCtr) Exit Sub End If wkbk.Close savechanges:=False Next iCtr End Sub (I got bored after 3 workbooks. You may want to test it with a couple to get it going.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm hurlbut777 wrote: I have created a workbook to summarize data from other workbooks. In order to accomplish this task, I linked data using formulas. I am trying to create a macro that updates the links. Here's the catch, all of the other workbooks that are linked have open passwords. So when I update the links I have to key in multiple passwords. I am trying to get around this by simply using vba to open up all the linked workbooks and then close them. However, when I do this, the links do not update...any help on this would be appreciated. End game, is I do not want to have to key any multiple passwords everytime I need to update links...I would rather have code to that for me. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Protected Links
Do you have calculation set to manual when you run that macro?
Maybe adding an "application.calculate" line to the code will help. If you recalc with the "sending" workbook open, then the links that point at that file should update. End If application.calculate 'added wkbk.Close savechanges:=False hurlbut777 wrote: Dave, Thanks for your relply. Unfortunately, I already tried that code. For some reason when I use vba code to open the files, the links will not update. If I open up the file via non-code methods, the links update. I'm perplexed on this one...guess I will just enter in 50 passwords everytime I want to update...at this point that would have taken me less time than coming up with an "efficient" method to automate. Thanks again. "Dave Peterson" wrote: (Saved from a previous post.) Option Explicit Sub testme() Dim myFileNames As Variant Dim myPasswords As Variant Dim iCtr As Long Dim myRealWkbk As Workbook Dim myRealWkbkName As String Dim wkbk As Workbook myRealWkbkName = "C:\my documents\excel\book1.xls" myFileNames = Array("C:\my documents\excel\book11.xls", _ "C:\my documents\excel\book21.xls", _ "C:\my other folder\book11.xls") myPasswords = Array("pwd1", _ "pwd2", _ "pwd3") If UBound(myFileNames) < UBound(myPasswords) Then MsgBox "check names & passwords--qty mismatch!" Exit Sub End If Set myRealWkbk = Workbooks.Open(Filename:=myRealWkbkName, UpdateLinks:=0) For iCtr = LBound(myFileNames) To UBound(myFileNames) Set wkbk = Nothing On Error Resume Next Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr), _ Password:=myPasswords(iCtr)) On Error GoTo 0 If wkbk Is Nothing Then MsgBox "Check file: " & myFileNames(iCtr) Exit Sub End If wkbk.Close savechanges:=False Next iCtr End Sub (I got bored after 3 workbooks. You may want to test it with a couple to get it going.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm hurlbut777 wrote: I have created a workbook to summarize data from other workbooks. In order to accomplish this task, I linked data using formulas. I am trying to create a macro that updates the links. Here's the catch, all of the other workbooks that are linked have open passwords. So when I update the links I have to key in multiple passwords. I am trying to get around this by simply using vba to open up all the linked workbooks and then close them. However, when I do this, the links do not update...any help on this would be appreciated. End game, is I do not want to have to key any multiple passwords everytime I need to update links...I would rather have code to that for me. -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Protected Links
Dave,
I had also tried that as well. I have chalked it up to one of those random excel problems. Thanks again for all the help. "Dave Peterson" wrote: Do you have calculation set to manual when you run that macro? Maybe adding an "application.calculate" line to the code will help. If you recalc with the "sending" workbook open, then the links that point at that file should update. End If application.calculate 'added wkbk.Close savechanges:=False hurlbut777 wrote: Dave, Thanks for your relply. Unfortunately, I already tried that code. For some reason when I use vba code to open the files, the links will not update. If I open up the file via non-code methods, the links update. I'm perplexed on this one...guess I will just enter in 50 passwords everytime I want to update...at this point that would have taken me less time than coming up with an "efficient" method to automate. Thanks again. "Dave Peterson" wrote: (Saved from a previous post.) Option Explicit Sub testme() Dim myFileNames As Variant Dim myPasswords As Variant Dim iCtr As Long Dim myRealWkbk As Workbook Dim myRealWkbkName As String Dim wkbk As Workbook myRealWkbkName = "C:\my documents\excel\book1.xls" myFileNames = Array("C:\my documents\excel\book11.xls", _ "C:\my documents\excel\book21.xls", _ "C:\my other folder\book11.xls") myPasswords = Array("pwd1", _ "pwd2", _ "pwd3") If UBound(myFileNames) < UBound(myPasswords) Then MsgBox "check names & passwords--qty mismatch!" Exit Sub End If Set myRealWkbk = Workbooks.Open(Filename:=myRealWkbkName, UpdateLinks:=0) For iCtr = LBound(myFileNames) To UBound(myFileNames) Set wkbk = Nothing On Error Resume Next Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr), _ Password:=myPasswords(iCtr)) On Error GoTo 0 If wkbk Is Nothing Then MsgBox "Check file: " & myFileNames(iCtr) Exit Sub End If wkbk.Close savechanges:=False Next iCtr End Sub (I got bored after 3 workbooks. You may want to test it with a couple to get it going.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm hurlbut777 wrote: I have created a workbook to summarize data from other workbooks. In order to accomplish this task, I linked data using formulas. I am trying to create a macro that updates the links. Here's the catch, all of the other workbooks that are linked have open passwords. So when I update the links I have to key in multiple passwords. I am trying to get around this by simply using vba to open up all the linked workbooks and then close them. However, when I do this, the links do not update...any help on this would be appreciated. End game, is I do not want to have to key any multiple passwords everytime I need to update links...I would rather have code to that for me. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
update links to password protected workbooks. | Excel Discussion (Misc queries) | |||
Update Protected links | Excel Programming | |||
Update links box gives Continue or Edit Links dialog | Excel Discussion (Misc queries) | |||
Suppress Update Links dialog AND don't update links | Excel Programming | |||
Edit Links: Changing links on a protected worksheet | Excel Discussion (Misc queries) |