Could this be better?????

[Chris84]

Hi

I have spent a big part of the day fighting a losing battle, but have a work around. What I need to do is update links in several different files which are password protected to open and modify.

The file will be used by different people so getting them to input the password's is not a option.

I have put some simple code together which opens the protected workbook then reapplies the links for the file and closes the workbook which works but is a lot slower than before the passwords was used.

Is there a smarter way of doing this?

Sub OpenUpdate()
Dim TestFile As String
TestFile = "C:\TestPWord.xlsm"
Workbooks.Open Filename:=TestFile, _
    Password:="Luck", ReadOnly:=True
ThisWorkbook.ChangeLink Name:=TestFile, NewName:= _
    TestFile, Type:=xlExcelLinks
Workbooks("TestPword.xlsm").Close savechanges:=False
End Sub

Any feedback greatly appreciated

 

[Jerry Sullivan]

Hi Chris,

When your code opens the linked workbook, the values in the calling workbook referencing that linked workbook should be updated automatically, so you could remove the line:

ThisWorkbook.ChangeLink Name:=TestFile, NewName:=  TestFile, Type:=xlExcelLinks

Also, your update might run quicker if you turn off calculation and screenupdating during the process.

Sub OpenUpdate()
    Dim TestFile As String
    TestFile = "C:\TestPWord.xlsm"

    On Error GoTo CleanUp
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Workbooks.Open Filename:=TestFile, Password:="Luck", ReadOnly:=True
    ActiveWorkbook.Close savechanges:=False

CleanUp:
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

I had thought the values might not update with Calculation set to Manual, but in a test, they get updated regardless.