Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Password Protected Links
Hi,
I have the following button. It opens a spreadsheet that is password protected. This spreadsheet has links to another password protected spreadsheet. The button enters the password and sets update links to yes but it then asks for a password to update the links I would like it to input the password automatically. Can Anyone help please? Thanks Private Sub Command1_Click() On Error GoTo Err_Command1_Click Dim ExcelApp As Object 'Excel.Application Dim ExcelWkb As Object 'Excel.Workbook Set ExcelApp = CreateObject("Excel.Application") Set ExcelWkb = ExcelApp.Workbooks ExcelWkb.Open "C:\Test.xls", , , , "ABCDE", UpdateLinks:=1 ExcelWkb.Close ExcelApp.Quit Set ExcelWkb = Nothing Set ExcelApp = Nothing Exit_Command1_Click: Exit Sub Err_Command1_Click: MsgBox Err.Description Resume Exit_Command1_Click End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Password Protected Links
Is there a reason that you're starting a second instance of excel instead of
just opening the file in the current workbook? Somebody/something has to provide those passwords. Saved from a previous post. You could use a macro in a helper workbook that opens the 5 workbooks first, then opens the "real" workbook. Then closes those 5 workbooks after the links have been updated. (Yep, the macro has to supply the passwords for the first 5.) Maybe you could have another workbook contains a macro that opens the other xx workbooks. The macro would need to know all the names and passwords for the files. This expects a worksheet named WkbkList that contains the names of the files in A2:Axxx (headers in row 1) and passwords in B2:Bxxx. (The filenames have to include the drive, path and name.) 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 'the workbook with all the links myRealWkbkName = "C:\my documents\excel\book1.xls" With Worksheets("WkbkList") 'headers in row 1 myFileNames = .Range("a2:b" & _ .Cells(.Rows.Count, "A").End(xlUp).Row).Value End With Set myRealWkbk = Workbooks.Open(Filename:=myRealWkbkName, UpdateLinks:=0) For iCtr = LBound(myFileNames, 1) To UBound(myFileNames, 1) Set wkbk = Nothing On Error Resume Next Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr, 1), _ Password:=myFileNames(iCtr, 2), _ ReadOnly:=True) On Error GoTo 0 If wkbk Is Nothing Then MsgBox "Check file: " & myFileNames(iCtr, 1) Else 'the links should have updated when 'this workbook was opened. 'so we can close it here wkbk.Close savechanges:=False End If Next iCtr End Sub If the files are all in the same folder, you don't have to include that in the worksheet cells. You could just tell the program where to look: Change this line: Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr, 1), _ Password:=myFileNames(iCtr, 2), _ ReadOnly:=True) to: Set wkbk = Workbooks.Open(Filename:="C:\myfoldernamehere\" & _ myFileNames(iCtr, 1), _ Password:=myFileNames(iCtr, 2), _ ReadOnly:=True) If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) james wrote: Hi, I have the following button. It opens a spreadsheet that is password protected. This spreadsheet has links to another password protected spreadsheet. The button enters the password and sets update links to yes but it then asks for a password to update the links I would like it to input the password automatically. Can Anyone help please? Thanks Private Sub Command1_Click() On Error GoTo Err_Command1_Click Dim ExcelApp As Object 'Excel.Application Dim ExcelWkb As Object 'Excel.Workbook Set ExcelApp = CreateObject("Excel.Application") Set ExcelWkb = ExcelApp.Workbooks ExcelWkb.Open "C:\Test.xls", , , , "ABCDE", UpdateLinks:=1 ExcelWkb.Close ExcelApp.Quit Set ExcelWkb = Nothing Set ExcelApp = Nothing Exit_Command1_Click: Exit Sub Err_Command1_Click: MsgBox Err.Description Resume Exit_Command1_Click End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
update links to password protected workbooks. | Excel Discussion (Misc queries) | |||
Password Protected Links | Links and Linking in Excel | |||
password protected workbooks with links between them | Excel Discussion (Misc queries) | |||
Links to password protected workbooks | Excel Programming | |||
bypass password when update linking of password protected file | Excel Discussion (Misc queries) |