If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Removing external links in Excel
In my group at work, we have a lot of problems with "links" (external
references to other workbooks) showing up in our Excel workbooks. Part of this is our analysts propensity to use old workbooks as "templates" for new projects. To remove the links, I wrote a procedure that cycled through all the worksheets to show me which worksheets contained cells with the string ".xls]" in their formulas (one form of external link). That allowed me to perform a "find and replace" to remove the offending string in the formulas. Since these external links also appear in Excel Name objects, I wrote another procedure that cycles through all Names and allows the user to selectively delete any the Name object that refers to external workbooks. This routine worked okay except for two cases: the intrinsic Names "Print_Area" and "Print_Title" . I was not able to delete or change their "RefersTo" property to remove the external reference. Would any one have experience using VBA to remove the external references in those two intrinsic Excel Name objects? Note: I know there are some utilities that do all of the above via automation, but I cannot use them in my work environment due to LAN restrictions, so just being able to write a VBA procedure that deals with these two intrinsic names is all I really need. Thank you ahead of time for your assistance! --- Tom |
#2
|
|||
|
|||
Removing external links in Excel
Tom Carnahan wrote:
Note: I know there are some utilities that do all of the above via automation, but I cannot use them in my work environment due to LAN restrictions Pity. FINDLINK.XLA from http://www.BMSLtd.ie/mvp would do this for you. I think you will find that PrintArea and PrintTitles are worksheet-level names. Here is a relevant bit of code from FINDLINK: If Right(obj.Name, 13) = "!Print_Titles" Then ' cannot delete this name but can kill it via PageSetup With Sheets(Left(obj.Name, Len(obj.Name) - 13)).PageSetup .PrintTitleColumns = "" .PrintTitleRows = "" End With End If where obj is the Name object. Hope it helps. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
Thread Tools | |
Display Modes | |
|
|