Just a thought. The file I'm trying to fix is a template (xlt), not xls.
Does this make any difference?
Also, I've opened the problem file without macros running. Does this matter?

Ian

"Ian" wrote in message
...
Hi Chip

I've had a chance to try your posted code on another machine with the
erroneous reference. I get a run-time error 1004 on the line

Set VBProj = WB.VBProject

The code is in a new workbook and the reference to Microsoft Visual Basic
For Applications Extensibility 5.3 Library is set in this book. The
project in the problem workbook is not locked.

I tried to output the value of WB with Debug.Print, but nothing was shown.
Is this normal?

Stepping through the code, after the line Set WB = Workbooks(WBName) I
hovered the cursor over WB.VBProject and saw =<Method 'VBProject' of
object '_Workbook' failed.

Am I missing something obvious?

Ian

"Ian" wrote in message
...
Thanks, Chip. As usual, a very informative response.

It is certainly possible to use code to modify other code or other
attributes of the VB Project of a workbook. See
http://www.cpearson.com/Excel/vbe.aspx for details and examples.

A very useful article, though the warning about anti-virus raises
concerns. It may be necessary for the end users to temporarily disable
the AV on their laptops. I'll have to look into that.

You could distribute a workbook to the end users that would fix any
troublesome workbooks the user may have. Put the following code in a
code module of a workbook, link it to a button on Sheet1, and send this
workbook out to the users. They should have this workbook and the
workbook with the bad reference open at the same time

I think I'll programmatically open the problem workbook and automatically
remove the reference. I need to make it as easy as possible as some of
the users are not particularly PC literate. When I distribute the fix, it
will be with a batch file to copy the workbook to a specific location and
open the workbook. The code will run on opening.

This workbook requires a reference to the "Microsoft Visual Basic For
Applications Extensibility 5.3 Library" (in VBA, Tools menu, Reference,
check this entry in the list).

Is this a standard library on all PCs (or at least, all PCs runningMS
Office)? I don't want to end up with any more problems with missing
references.

As an aside, can you think how the problem reference came to be set? I
certainly haven't done it manually, particularly in view of the fact
that, once I remove the reference on my own laptop, it disappears from
the list of available references.

Ian