PDA

View Full Version : [SOLVED] List cells containing references to files, i.e. containing formulas with broken links



vanhunk
08-06-2013, 08:39 AM
List cells containing references to unavailable files, i.e. containing formulas with broken links

Good day,

I have searched through many posts relating to links but could not any that actually worked for what I am trying to achieve. Some claimed to be able to but did not.

What I am trying to achieve is that whenever I have the situation that broken links exist in a workbook,
the type that would give you an error like illustrated in the attached word file
e.g. in the "Edit Links" dialogue box: "Link4.xls Worksheet A Error:Source not found",
I want to be able to run a macro that would list all the cells that contain references to the "source that is not found",
in the case of the example it would be all cells that contain formulas referencing "Link4.xls".


Regards,
vanhunk

snb
08-08-2013, 01:34 AM
Sub M_snb()
For Each lk In ActiveWorkbook.LinkSources
For Each sh In ActiveWorkbook.Sheets
For Each it In sh.Cells.SpecialCells(xlCellTypeFormulas, 16)
If InStr(it.Formula, lk) Then c00 = c00 & vbLf & sh.Name & "!" & it.Address
Next
Next
Next

MsgBox c00
End Sub

vanhunk
08-08-2013, 01:59 AM
Hi snb, It doesn't detect the broken links for some or other reason and gives the following error message: It Run-time error '1004': No cells were found. Thanks for the quick response. Regards, vanhunk

snb
08-08-2013, 03:30 AM
if any of the sheets doesn't contain at least 1 formula that errors out the macro will stuck at that point.
Easily repaired by:

Sub M_snb()
on Error Resume next

For Each lk In ActiveWorkbook.LinkSources
For Each sh In ActiveWorkbook.Sheets
For Each it In sh.Cells.SpecialCells(xlCellTypeFormulas, 16)
If InStr(it.Formula, lk) Then c00 = c00 & vbLf & sh.Name & "!" & it.Address
Next
Next
Next

MsgBox c00
End Sub

vanhunk
08-08-2013, 03:53 AM
PROBLEM It does not detect the broken links which are there. There are formulas that refer to workbooks that no longer exist and it does not detect it. Thanks

snb
08-08-2013, 04:18 AM
Sub M_snb()
on Error Resume next

For Each lk In ActiveWorkbook.LinkSources
For Each sh In ActiveWorkbook.Sheets
For Each it In sh.Cells.SpecialCells(xlCellTypeFormulas)
If InStr(it.Formula, lk) Then
if dir(lk)="" then c00 = c00 & vbLf & sh.Name & "!" & it.Address
end if
Next
Next
Next

MsgBox c00
End Sub

vanhunk
08-08-2013, 05:31 AM
Still not Working: (List cells containing references to files, i.e. containing formulas with broken links) All of the cells contain broken links, i.e. links to a workbook that no longer exists. I want the macro to list the cell addresses of each of these cells. At the moment it does not detect it, even if the formula returns #Ref!. I am trying to attach a document for illustration but can not find the attach file button. Regards, vanhunk

snb
08-08-2013, 06:49 AM
Click the 'Go advanced' button below the quick Reply window.
There you will find the paperclip icon to add attachments.

vanhunk
08-08-2013, 06:57 AM
Tried that, it doesn't show the paperclip icon

snb
08-08-2013, 08:25 AM
Are you sure ?10394

vanhunk
08-12-2013, 11:15 AM
Thanks snb I missed the paperclip.

The attached file describes the problem I have better. I hope it will assist in getting a solution. Hopefully it will be possible.

Regards,

vanhunk

vanhunk
08-12-2013, 11:17 AM
Hi snb,

The attached file goes with the previous post an has your code in it for illustration of the problem.

Regards,
vanhunk

vanhunk
08-16-2013, 01:48 AM
STILL NEED HELP PLEASE

snb
08-16-2013, 06:02 AM
There you go:


Sub M_snb()
On Error Resume Next

For Each lk In ActiveWorkbook.LinkSources
If Dir(lk) = "" Then
For Each sh In ActiveWorkbook.Sheets
For Each it In sh.Cells.SpecialCells(xlCellTypeFormulas)
If InStr(Replace(Replace(it.Formula, "[", ""), "]", ""), lk) Then c00 = c00 & vbLf & sh.Name & "!" & it.Address
Next
Next
End If
Next

MsgBox c00
End Sub

vanhunk
08-17-2013, 01:02 PM
Hi snb,

As always, you've done it again!!! Thank you very much!!!:beerchug:

vanhunk
08-19-2013, 07:05 AM
Hi snb, In InStr(Replace(Replace(it.Formula, "[", ""), "]", ""), lk) it removes the "[" and "]" and compare it to lk. What exactly does lk look like if the formula looks like " ='I:\MS Office Know How\Excel\@WORK IN PROGRESS\[Link3.xls]Sheet2'!$E$7 " for example? Regards, vanhunk The following code is the final result of what I was trying to achieve. It adds a sheet to the workbook, listing all of the broken links, with hyperlinks to cells containing the broken links: Sub ShowAllBrokenLinksInfo() 'Purpose: Identify which cells in which worksheets has broken links, i.e. refers to other workbooks that can no longer be found. 'Requirements: Requires a worksheet to be added to the workbook, named BrokenLinksList Dim aLinks As Variant 'Used to determine if any links exist in the workbook. Dim anyWS As Worksheet 'Used to i.a. page through the existing worksheets in the workbook, 'and used to search through worksheets for links (broken links). Dim anyCell As Range 'Used to search through formula cells for links and broken links. Dim reportWS As Worksheet 'Used for the new or reporting worksheet. Dim nextReportRow As Long 'Used for fixing the next row for inputs into the report sheet. Dim shtName As String 'Used as the reporting sheet for all the links in the document. Dim bWsExists As Boolean 'Used to determine if reporting worksheet already exists. Dim BeginSheet As Worksheet 'Used to capture the worksheet the personal macro is activated from. Dim BeginBook As Workbook 'Used to capture the workbook the personal macro is activated from. Dim anyLink As Object 'Used to determine whether the workbook referred to by the link exists. Application.ScreenUpdating = False 'Switch off screen updating to speed up macro execution. 'The following line names the sheet to be added if not already present. The broken links will be listed in this sheet and will be updated 'every time the macro is executed. This sheet will be referred to as the Report Sheet ("reportWS"): shtName = "BrokenLinksList" 'The following line captures the name of the workbook the macro is activated from and enables the use of the macro from the personal workbook: Set BeginBook = Application.ActiveWorkbook 'The following line determines whether any links exist in the workbook, i.e. in "BeginBook": aLinks = BeginBook.LinkSources(xlExcelLinks) '########################################################################## ######### If Not IsEmpty(aLinks) Then 'i.e. there are links in the workbook and therefore requires us to deal with the next section: '========================================================================== ========= 'THIS SECTION DEALS WITH THE BROKEN LINKS REPORT SHEET: 'The following lines determine whether a broken links report sheet already exists: For Each anyWS In Application.Worksheets 'Page through all the existing sheets in the workbook. If anyWS.Name = shtName Then bWsExists = True 'and compare their names with the selected report sheet name. Next anyWS '----------------------------------------------------------------------------------------------------------------------------------------------------- 'The following lines adds a broken links report sheet if it doesn't already exists: If bWsExists = False Then Application.DisplayAlerts = False 'The following line adds the report sheet for listing of all the broken links, if it does not already exist in the workbook: Set anyWS = ActiveWorkbook.Worksheets.Add(Type:=xlWorksheet) 'Adds new sheet (the report sheet) anyWS.Name = shtName 'Changes its name to the selected report sheet name. anyWS.Select 'Selects the new sheet (report sheet). anyWS.Move After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count) 'Moves it to the last "tab" position. Application.DisplayAlerts = True End If '----------------------------------------------------------------------------------------------------------------------------------------------------- 'The following lines prepare the report sheet and add headings to the columns: Set reportWS = BeginBook.Worksheets(shtName) reportWS.Cells.Clear reportWS.Range("A1") = "Worksheet" reportWS.Range("B1") = "Cell" reportWS.Range("C1") = "Formula" 'End of the section that deals directly with the links report sheet. '========================================================================== ========= '&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 'THIS SECTION DEALS DIRECTLY WITH THE LINKS AND BROKEN LINKS IN THE WORKBOOK: On Error Resume Next '.......................................................................... ........................................................................... ....................................... For Each anyLink In ActiveWorkbook.LinkSources 'Loops through all the link sources for the workbook. '------------------------------------------------------------------------------------------------------------------------------------------------------- If Dir(anyLink) = "" Then 'If the workbook referenced by the link is not found then continue: '========================================================================== ========== For Each anyWS In ActiveWorkbook.Sheets 'Loop through all the sheets in the workbook to find the links. '************************************************************************** ********************* For Each anyCell In anyWS.Cells.SpecialCells(xlCellTypeFormulas) 'Loops through all formulas in the worksheet. '""""""""""""""""""""""""""""""""""""""""""""""""""""""""""''''''''''''''''''''''''''''''''''''''''''''''"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" 'The following line removes the "[" and "]" characters and compares the result with "anylink": If InStr(Replace(Replace(anyCell.Formula, "[", ""), "]", ""), anyLink) Then 'The following line checks whether the sheet contains any links: If Not InStr(anyCell.Formula, "[") > 0 Then GoTo VolgendeSheet nextReportRow = reportWS.Range("A" & Rows.Count).End(xlUp).Row + 1 reportWS.Range("A" & nextReportRow) = anyWS.Name reportWS.Range("B" & nextReportRow) = anyCell.Address reportWS.Range("C" & nextReportRow) = "'" & anyCell.Formula 'The following lines add hyperlinks to the cell address items of the report: With reportWS .Hyperlinks.Add _ Anchor:= _ .Range("B" & nextReportRow), _ Address:= _ "", _ SubAddress:= _ "'" & anyWS.Name & "'!" & anyCell.Address, _ ScreenTip:= _ "GOTO Linked Cell" End With 'The following lines format the headings of the report: With reportWS.Range("A1:C1") .Font.Bold = True .HorizontalAlignment = xlCenter .Font.Size = 11 End With reportWS.Range("B1").End(xlDown).Font.Size = 10 reportWS.Columns("A:C").EntireColumn.AutoFit End If 'End of "InStr(Replace(Replace(anyCell.Formula, "[", ""), "]", ""), anyLink)" '''''''''''''''''''''''''''''''''''''''''"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" Next 'Next anyCell in anyWS.Cells.SpecialCells(xlCellTypeFormulas) '************************************************************************** ******************* VolgendeSheet: Next 'Next anyWS in ActiveWorkbook.Sheets '========================================================================== ========= End If 'End of "If Dir(anyLink) = "" '----------------------------------------------------------------------------------------------------------------------------------------------------- Next 'Next anyLink In ActiveWorkbook.LinkSources" '.......................................................................... ........................................................................... ..................................... 'END OF SECTION THAT DEALS DIRECTLY WITH THE LINKS AND BROKEN LINKS IN THE WORKBOOK. '&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& Else 'Part of In Not IsEmpty(aLinks) block. Else it is empty, i.e. there are no links in workbook. MsgBox "No links to Excel worksheets detected." End If 'End of If Not IsEmpty(aLinks) block '########################################################################## ######### 'Housekeeping: Set reportWS = Nothing Set anyWS = Nothing Set anyCell = Nothing Application.ScreenUpdating = True End Sub

snb
08-19-2013, 07:11 AM
Use the local window to identify the value of a certain variable.
Use F8 to go through the macro step by step.

Your code in the previous post isn't readable.
I think you should remove all hyphens.

vanhunk
08-19-2013, 01:07 PM
Hi snb,

I tried your suggestion and what seemed like everything else, but could not load the code in such a way that the posted code stays in its original format. I have no idea what else to try.

I have now opted to post the code as a Word attachment.

Once again, thank you for your assistance.

Regards,
vanhunk

snb
08-19-2013, 02:22 PM
Why do you consider a worksheet to be an element of Excel instead of a workbook ?

For Each anyWS In Application.Worksheets

should be


For Each anyWS In thisworkbook.Worksheets

or

For Each anyWS In activeworkbook.Worksheets

or

For Each anyWS In workbooks(2).Worksheets

or

For Each anyWS In workbooks("workbookName.xlsx").Worksheets

snb
08-19-2013, 02:30 PM
Sub M_snb()
if not evaluate("isref(" & shtName &"!A1)") then
thisworkbook.sheets.add ,thisworkbook.sheets.count
thisworkbook.sheets(thisworkbook.sheets.count).name=shtname
end if
End Sub

can replace
For Each anyWS In Application.Worksheets 'Page through all the existing sheets in the workbook.
If anyWS.Name = shtName Then bWsExists = True 'and compare their names with the selected report sheet name.
Next anyWS
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'The following lines adds a broken links report sheet if it doesn't already exists:
If bWsExists = False Then
Application.DisplayAlerts = False
'The following line adds the report sheet for listing of all the broken links, if it does not already exist in the workbook:
Set anyWS = ActiveWorkbook.Worksheets.Add(Type:=xlWorksheet) 'Adds new sheet (the report sheet)
anyWS.Name = shtName 'Changes its name to the selected report sheet name.
anyWS.Select 'Selects the new sheet (report sheet).
anyWS.Move After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count) 'Moves it to the last "tab" position.
Application.DisplayAlerts = True
End If

snb
08-20-2013, 01:37 AM
I have to amend my last suggestion: a oneliner suffices:


Sub M_snb()
if not evaluate("isref(" & shtName &"!A1)") then thisworkbook.sheets.add.name=shtname
End Sub

vanhunk
08-20-2013, 03:05 AM
Why do you consider a worksheet to be an element of Excel instead of a workbook ? For Each anyWS In Application.Worksheets should be For Each anyWS In thisworkbook.Worksheets or For Each anyWS In activeworkbook.Worksheets or For Each anyWS In workbooks(2).Worksheets or For Each anyWS In workbooks("workbookName.xlsx").Worksheets Thanks snb, I haven't really thought about it. What is the benefit? Regards, vanhunk

vanhunk
08-20-2013, 03:10 AM
I have to amend my last suggestion: a oneliner suffices:
Sub M_snb() if not evaluate("isref(" & shtName &"!A1)") then thisworkbook.sheets.add.name=shtname End Sub Impressive, Thanks snb I will certainly give it a go. Best Regards, vanhunk

vanhunk
08-21-2013, 03:17 AM
Hi snb, Because I run the macro from the Personal Workbook I have adapted your proposed code to the following: If Not Evaluate("isref(" & shtName & "!A1)") Then BeginBook.Sheets.Add.Name = shtName BeginBook.shtName.Move After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count) 'Moves it to the last "tab" position. I ran the code before the change and it added another sheet to the Personal Workbook, called "Sheet4(BrokenLinksList)" in the VBA Project window. How do I get rid of this sheet? Regards, Henk

snb
08-21-2013, 03:46 AM
Check the difference between 'ThisWorkbook' and 'Activeworkbook' in the VBEditor's helpfiles.
Use:


Sub M_snb()
If Not evaluate("isref(" & shtName &"!A1)") Then ActiveWorkbook.sheets.add.name=shtname
End Sub

vanhunk
08-21-2013, 12:49 PM
Check the difference between 'ThisWorkbook' and 'Activeworkbook' in the VBEditor's helpfiles.
Use:


Sub M_snb()
If Not evaluate("isref(" & shtName &"!A1)") Then ActiveWorkbook.sheets.add.name=shtname
End Sub
Thanks,
ThisWorkBook object refers to the workbook that the code is contained in. ActiveWorkBook object refers to the workbook that is currently active.

What I would like to know is whether there is a performance difference between referring to it as an excel element instead of a workbook element.

vanhunk

vanhunk
08-22-2013, 02:55 AM
I have to amend my last suggestion: a oneliner suffices:
Sub M_snb() if not evaluate("isref(" & shtName &"!A1)") then thisworkbook.sheets.add.name=shtname End Sub Hi snb, Could you please explain the use of & and the placement of the quotation marks? Regards, vanhunk

snb
08-22-2013, 03:36 AM
Have a look:


Sub M_snb()
msgbox "isref(" & shtName &"!A1)"
If Not evaluate("isref(" & shtName &"!A1)") Then ActiveWorkbook.sheets.add.name=shtname
End Sub

vanhunk
08-22-2013, 04:07 AM
Have a look:
Sub M_snb() msgbox "isref(" & shtName &"!A1)" If Not evaluate("isref(" & shtName &"!A1)") Then ActiveWorkbook.sheets.add.name=shtname End Sub I understood what it does, and what the result would look like. What I want to know or understand is how would I have known where to put the &s and "s and why there? Regards

snb
08-22-2013, 04:54 AM
If you need to concatenate a string & a non-string (number, variable, date, Range value, object property) you need to use the concatenation operator &


msgbox "abc " & 5
msgbox "abc" & variables
msgbox "abc" & range("A5").value
msgbox "abc " & Date
msgbox "abc " & Now
msgbox "abc " & activeworkbook.name