Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to remove external references from range names
Just an offering to the world...a while back I had to remove all the
external references from all my range names in the active workbook, which is something Name Manager does not do. This code worked for me, but I make no guarantees that it will do anything for anyone else. Bruce Cooley ------------------------------- Sub InternalNames() Dim oName As Name Dim strName As String Dim strOldName As String ' Original Name Dim strExRef As String ' External Reference Dim strNewName As String ' New Name Dim intExRefStart As Integer ' position of [ Dim intExRefEnd As Integer ' position of ] Dim intExRefLength As Integer ' length of External Reference For Each oName In ActiveWorkbook.Names ' OFFSET FORMULAS SOMETIMES HAVE MULTIPLE EXTERNAL REFERENCES...IN THE MIDDLE! ' =OFFSET('*:\*\[*.xls]*'!$A$21,0,'*:\*\[*.xls]*'!$CZ$1,'*'!COUNT,1) ' =OFFSET('*:\*\[*.xls]*'!$F$94,0,0,COUNTIF('*:\*\[*.xls]*'!$G$94:$G$105,"0")) If InStr(1, oName.RefersTo, ":\", vbTextCompare) 0 Or InStr(1, oName.RefersTo, "[", vbTextCompare) 0 Then Do While InStr(1, oName.RefersTo, "[", vbTextCompare) 0 ' Assuming that where there's a [ there' a :\ ' MsgBox "Name: " & oName.Name strName = oName.Name ' MsgBox "RefersTo: " & oName.RefersTo strOldName = oName.RefersTo intExRefStart = InStr(1, oName.RefersTo, ":\", vbTextCompare) - 1 ' Back off to start of path intExRefEnd = InStr(1, oName.RefersTo, "[", vbTextCompare) intExRefLength = intExRefEnd - intExRefStart strExRef = Mid(oName.RefersTo, intExRefStart, intExRefLength) ' MsgBox "Deleting: " & strExRef strNewName = Replace(oName.RefersTo, strExRef, "", vbTextCompare) If InStr(1, oName.RefersTo, "[", vbTextCompare) 0 Then strOldName = oName.RefersTo intExRefStart = InStr(1, oName.RefersTo, "[", vbTextCompare) intExRefEnd = InStr(1, oName.RefersTo, "]", vbTextCompare) + 1 intExRefLength = intExRefEnd - intExRefStart strExRef = Mid(oName.RefersTo, intExRefStart, intExRefLength) ' MsgBox "Deleting: " & strExRef strNewName = Replace(strNewName, strExRef, "", vbTextCompare) ThisWorkbook.Names.Add Name:=strName, RefersTo:=strNewName, Visible:=True ' MsgBox oName.Name & " Now RefersTo: " & oName.RefersTo End If Loop ' MsgBox "Next name" End If Next MsgBox "All External References have been removed. In theory." Set oName = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to remove external references from range names
To remove references here is an excellent addin... Fully debugged and tested...
Look at Bill Manville's Find Links... http://www.oaltd.co.uk/MVP/ -- HTH... Jim Thomlinson "Bruce Cooley" wrote: Just an offering to the world...a while back I had to remove all the external references from all my range names in the active workbook, which is something Name Manager does not do. This code worked for me, but I make no guarantees that it will do anything for anyone else. Bruce Cooley ------------------------------- Sub InternalNames() Dim oName As Name Dim strName As String Dim strOldName As String ' Original Name Dim strExRef As String ' External Reference Dim strNewName As String ' New Name Dim intExRefStart As Integer ' position of [ Dim intExRefEnd As Integer ' position of ] Dim intExRefLength As Integer ' length of External Reference For Each oName In ActiveWorkbook.Names ' OFFSET FORMULAS SOMETIMES HAVE MULTIPLE EXTERNAL REFERENCES...IN THE MIDDLE! ' =OFFSET('*:\*\[*.xls]*'!$A$21,0,'*:\*\[*.xls]*'!$CZ$1,'*'!COUNT,1) ' =OFFSET('*:\*\[*.xls]*'!$F$94,0,0,COUNTIF('*:\*\[*.xls]*'!$G$94:$G$105,"0")) If InStr(1, oName.RefersTo, ":\", vbTextCompare) 0 Or InStr(1, oName.RefersTo, "[", vbTextCompare) 0 Then Do While InStr(1, oName.RefersTo, "[", vbTextCompare) 0 ' Assuming that where there's a [ there' a :\ ' MsgBox "Name: " & oName.Name strName = oName.Name ' MsgBox "RefersTo: " & oName.RefersTo strOldName = oName.RefersTo intExRefStart = InStr(1, oName.RefersTo, ":\", vbTextCompare) - 1 ' Back off to start of path intExRefEnd = InStr(1, oName.RefersTo, "[", vbTextCompare) intExRefLength = intExRefEnd - intExRefStart strExRef = Mid(oName.RefersTo, intExRefStart, intExRefLength) ' MsgBox "Deleting: " & strExRef strNewName = Replace(oName.RefersTo, strExRef, "", vbTextCompare) If InStr(1, oName.RefersTo, "[", vbTextCompare) 0 Then strOldName = oName.RefersTo intExRefStart = InStr(1, oName.RefersTo, "[", vbTextCompare) intExRefEnd = InStr(1, oName.RefersTo, "]", vbTextCompare) + 1 intExRefLength = intExRefEnd - intExRefStart strExRef = Mid(oName.RefersTo, intExRefStart, intExRefLength) ' MsgBox "Deleting: " & strExRef strNewName = Replace(strNewName, strExRef, "", vbTextCompare) ThisWorkbook.Names.Add Name:=strName, RefersTo:=strNewName, Visible:=True ' MsgBox oName.Name & " Now RefersTo: " & oName.RefersTo End If Loop ' MsgBox "Next name" End If Next MsgBox "All External References have been removed. In theory." Set oName = Nothing End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating external references to spreadsheets with changing names | Excel Worksheet Functions | |||
range names to cell references | Excel Worksheet Functions | |||
Range Names convert to Cell References | Excel Discussion (Misc queries) | |||
How to rename references from range names to cell references | Excel Discussion (Misc queries) | |||
Replace range names with cell references? | Excel Worksheet Functions |