If MsgBox("Does the following " & LCase(Obj_Type) & " from " & Src_Sht & " need updated?" & Chr(10) & Chr(10) & temp_1, vbYesNo, _ 'Check if it has an external reference in the input string Public Function Ext_Text_Update(Orig_Str As String, Obj_Type As String, Src_Sht As String) As String 'Generalized function to elicite and capture updates to formulas/paths/etc. S.Hyperlink.SubAddress = Ext_Text_Update(S.Hyperlink.SubAddress, "shape hyperlink path", WS.Name) S.Hyperlink.Address = Ext_Text_Update(S.Hyperlink.Address, "shape hyperlink path", WS.Name) S.Name = Ext_Text_Update(S.Name, "shape name", WS.Name) Public Sub Sht_Shapes_Check(WS As Worksheet) 'Routine to check all shapes on the given sheet for external links HLink.SubAddress = Ext_Text_Update(hLink.SubAddress, "hyperlink", WS.Name) HLink.Address = Ext_Text_Update(hLink.Address, "hyperlink", WS.Name) Public Sub Sht_Hyperlinks_Check(WS As Worksheet) 'Routine to check all hyperlinks on the given sheet for external links TC.Formula = Ext_Text_Update(TC.Formula, "cell formula", WS.Name)Įnd If 'else no formulas with external links found 'Capture the first address (if we haven't already) to avoid infinite loopingĮnd If 'else we already have the first cell and no action needed Find(what:="*.xl*", LookIn:=xlFormulas)ĭo While (Not Trgt_Cells Is Nothing) And First_Address Trgt_Cells.Address 'Make sure the Target cells is empty, and that the first_address value is empty Public Sub Sht_Formula_Check(WS As Worksheet) 'Routine to check all formulas on the given sheet for external links Temp_2 = Ext_Text_Update(temp_1, "named range", WS.Name)Įnd If 'else there's no external reference to clean up/delete, move to next range "External Link in Named Range") = vbYes Then Should this range be deleted?", vbYesNo, _ The range is defined as '" & temp_1 & "'.
If MsgBox("External link found in Named Range '" & NR.Name & "'.
'Check if the name's definition is an external link 'Make sure the temporary strings start empty Public Sub Named_Range_Ext_Link_Chk(NR As Name, WS As Worksheet, WB As Workbook) If InStr(1, Chk_Str, ".xl", vbTextCompare) > 0 Then Public Function Contains_File_Link(Chk_Str As String) As Boolean 'Check the provided Named Range definition for an external file reference Named_Range_Ext_Link_Chk NR, ActiveSheet, WB 'Iterate through named ranges on the workbook I've got the below so far and it runs & I haven't found any links to remove, but the prompt is still popping up when I open the workbook in question. Break links isn't actually breaking the link(s), so I've been working to create a macro to search for external references and remove them. I've got a workbook that is saying there are external links in the workbook.
0 kommentar(er)
