VBScript to open a password protected file in order to edit links.

[rdomingue]

I have a working program but want to add a password to the external file I'm using to update my active spreadsheet file. I do not want to be prompted for the password when I initiate the "update" command from within code. I would like to automatically pass the password through scripting. Here is what I have so far: (This works if I don't password protect the file "activityreportdatabase.xlxs.")

Sub GetLinks()
' UpdateLinks Macro


Dim Response As Integer, PASSWORD As String
Response = MsgBox(prompt:="UPDATE NOW?", Buttons:=vbYesNo)
If Response = vbYes Then
ActiveSheet.Unprotect ("456")
ActiveWorkbook.UpdateLink Name:="C:\activityreportdatabase.xlsx", Type:= _
xlExcelLinks
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.Protect ("456")
Else
End If
End Sub

 

[LockeGarmin]

The 'Workbooks.Open' method has a password parameter you can use to open a password protected spreadsheet. So if Book1.xlsx on your desktop is password protected with the word "test" you would use this code to open the workbook:

Workbooks.Open Environ$("UserProfile") & "\Desktop\Book1.xlsx",Password:="test"