Suppressing Update Links Warning

[Guest]

I recently launched a ResetPasswords macro for our Office Manager that allows
her to reset the passwords of all files in a given folder and its subfolders
to a new value. The program opens the file, sets the new password, saves,
then closes the file.

After thoroughly testing it on a variety of my own folders, it was a bit
embarassing that when we ran it for real, the program halted several times to
ask whether she wanted to update old links that existed. Can this warning be
suppressed?

Thank you.
Sprinks

 

[Guest]

When you open the file, you can choose an option to update the links or not.
I don't have excel loaded here right now so can't check for you. If you
don't get a response before morning, I'll post it then.

 

[Dave Peterson]

Look at workbooks.open in VBA's help. You can suppress the links by specifying
a parm in that statement.

set wkbk = workbooks.open(filename:=...., updatelinks:=0)

From xl2003's help:

UpdateLinks Optional Variant. Specifies the way links in the file are updated.
If this argument is omitted, the user is prompted to specify how links will be
updated. Otherwise, this argument is one of the values listed in the following
table.

Value Meaning
0 Doesn't update any references
1 Updates external references but not remote references
2 Updates remote references but not external references
3 Updates both remote and external references


======
ps. You may want to stop any workbook_open event from firing:

application.enableevents = false
set wkbk = workbooks.open( ....)
'do your stuff
wkbk.close savechanges:=true
application.enableevents = true

Just in case there's a workbook_Open event that runs and irritates, er, prompts
the user for something.

 

[Guest]

Thanks, Barb and Dave.

Dave,

I had been doing the save and close in two steps:
With wb
.password = strnew
.save
.close
End wb

Do you know if

.close savechanges:=true

would be faster?

Sprinks

 

[Susan]

sprinks - i was interested in this, too, and tried to
implement the previous suggestions with no luck.
when i searched the newsgroup, i found this old
post from tom that made it work for me......
(i'm not sure if this is exactly what you need....)
susan
xxxxxxxxxxxxxxxxxxxxxxxx
From: Tom Ogilvy - view profile
Date: Mon, Aug 26 2002 12:08 pm
Email: "Tom Ogilvy" <[email protected]>
Groups: microsoft.public.excel.programming

The way to stop the prompt is to go to tools=>Options=>Edit Tab and
uncheck
Ask to Update Automatic Links - this is only if you want them updated
without prompt.

The other way is to open the workbook from another workbook using code.

Then in the Workbooks.Open command, there is an option setting called
UpdateLinks which can be set for the behavior you desire.

In any event, for a given workbook, the prompt is issued prior to any
code
running if the option is selected for Excel to Ask to Update Automatic
Links.

from help for the updatelinks option of the workbooks.open method:
UpdateLinks Optional Variant. Specifies the way links in the file are

updated. If this argument is omitted, the user is prompted to specify
how
links will be updated. Otherwise, this argument is one of the values
listed
in the following table.

Value Meaning
0 Doesn't update any references
1 Updates external references but not remote references
2 Updates remote references but not external references
3 Updates both remote and external references

Regards,
Tom Ogilvy

 

[Dave Peterson]

It might be milliseconds quicker. I'm sure saving the file to the drive is
overwhelmingly slower than the differnce caused by two lines.

But for what little it's worth, I like the multiple lines, too.