PDA

View Full Version : auto update link from password protected file



ajm
01-09-2007, 05:20 PM
I have a workbook which has a single link to a password protected file. everything i have tried to get the link to update automatically on open, or via calling a macro on open get stuck. here's what i have been using:

Sub OpenNow()
' Open password protected files
Application.Workbooks.Open _
"G:\TransLink\Contracts\Bus contracts\All operators\Payment Process\Key Factors.xls", False, False, , "payments", "Chelsea"

ActiveWorkbook.Close

End Sub
i tried a few different things. the sticking point seems to be the password to modify. without it, the routine runs fine but then stops at the password to modify box. if that password is included in the routine, it gets stopped at the password to open, even though that password is included in the routine.

I am perplexed. If i run the macro from Tools=>Macros menu, it works perfectly, even with the WriteResPassword included.

anybody able to help?:banghead:

XLGibbs
01-09-2007, 05:24 PM
I have dozens of reports where I work that link to my master files which are all password protected and recommended read only. I have not once encountered a situation where the automatic refresh of external links failed to get the data.

Occassionally, if the file is closed and the linked cell has a volatile formula, it returns an #N/A until I open the source file (whether it be read only or otherwise)...

is this the issue you are having with the update?

ajm
01-09-2007, 05:41 PM
hey gibbs. funny how loads of mrexcel people show up here also. no. N/A is not what's happening. when the code doesn't work, it merely goes to the box that says "this workbook contains links to other data sources". then selecting "update" it then goes to the password box and stops.

that happens using Workbook_open. also when calling the macro from a module using "application.run...." inside the workbook open event.

XLGibbs
01-09-2007, 06:19 PM
hey gibbs. funny how loads of mrexcel people show up here also.
[quote]

This is my primary residence. There are alot of the same guys all over..JMT boards and other excel boards. A rather large community of a lot of guys that all know eachother by board handles.

But we here at VBAX think it is just the guys at Mr.Excel knowing greatness when they see it, so they come here too. (plus you can attach files here)

[quote]
no. N/A is not what's happening. when the code doesn't work, it merely goes to the box that says "this workbook contains links to other data sources". then selecting "update" it then goes to the password box and stops.

that happens using Workbook_open. also when calling the macro from a module using "application.run...." inside the workbook open event.

I think there is an option in Tools>Options that you can change that sets this to automatic. As I said, I have never had an issue with a password protected file and a linked formula/cell to the file, so I am unsure why that would happen.

using Application.Run and reference modules in the other workbooks WOULD require that workbook to be open..so that is different than updating an external formula reference and would indeed require passwords.

ajm
01-09-2007, 07:13 PM
using Application.Run and reference modules in the other workbooks WOULD require that workbook to be open..so that is different than updating an external formula reference and would indeed require passwords.

sorry. a little confusing here. the workbook open event AND the modules are all in the one workbook. am trying to open a second workbook in order to update the links in the original. It is the second workbook that is password protected.

does that make any difference??