• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find the hidden links in Excel Work book

nagovind

nagovind

Member
Dear All


Below is the code to find the hidden links from a workbook linking to external work book


But it is hard to find the location of the cell / object which is linking the said link


How to find the source cell / object which is referring this external links


I have tried the Ctrl^F option with "[" search and all other option it is doing the job well but the query is to execute the below code in enhanced mode so that it will also find the SOURCE of the linking cells or objects


Please advise

[pre]
Code: 
Sub ListLinks()
Dim aLinks As Variant
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
Sheets.Add
For i = 1 To UBound(aLinks)
Cells(i, 1).Value = aLinks(i)
Next i
End If
End Sub
[/pre]
 
Hi Govind ,


Try this :

[pre]
Code: 
Sub ListLinks()
Const Entire_Worksheet = "$1:$1048576"
Dim link_cells() As String, formula As String
Dim i As Integer, j As Integer
Dim aLinks As Variant
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then
If Selection.Address = Entire_Worksheet Then
UsedRange.Select
End If

ReDim link_cells(Selection.Cells.Count, 2)
i = 1

For Each cell In Selection
If InStr(1, cell.formula, "[") <> 0 Then
formula = VBA.Replace(VBA.Replace(cell.formula, "[", ""), "]", "")
For j = 1 To UBound(aLinks)
If InStr(1, formula, aLinks(j)) Then
link_cells(i, 1) = cell.Address
link_cells(i, 2) = aLinks(j)
i = i + 1
End If
Next
End If
Next

Sheets.Add
With ActiveSheet
For j = 1 To i - 1
.Cells(j, 1).Value = link_cells(j, 1)
.Cells(j, 2).Value = link_cells(j, 2)
Next j

.Cells(1, 2).EntireColumn.AutoFit
End With
End If
End Sub
[/pre]
Narayan
 
Hi Narayan


Thank you for your response


It seems the code is ending without any results

May be i have wrongly executed


Below is the example file which is having the link to external macro


I have executed the above code in this file but it is not working


https://dl.dropbox.com/u/101570276/test.rar


Please advise
 
Hi Nagovind..


Is this what you are looking for..

[pre]
Code: 
Sub ListLink()
Dim deb()
Dim i As Long: i = 0
For Each sh In ActiveWorkbook.Worksheets
For Each cell In sh.UsedRange
If Left(cell.formula, 1) = "=" And InStr(cell.formula, "[") > 1 Then
ReDim Preserve deb(i)
deb(i) = sh.Name & "-" & cell.Address
i = i + 1
End If
Next cell
Next sh
Sheets.Add.Name = "ListLink"
Sheets("listLink").Range("A1:A" & UBound(deb) + 1).Value = deb
End Sub
[/pre]

If above code is working as per your your requirement.. then we can add some validation.. and hyperlink too.. to make it more beautiful..


Regards,

Deb
 
Hi Govind ,


My understanding of external links is links to external workbooks in Excel formulae ; the code I posted was to extract these links and cell addresses.


The problem is a wonderful problem !


As I understand it , your Book2.xlsm has the following formula in cell K9 :


=J12+'F:KNExcel_ConsultancyNAGovindtest[Book1.xlsm]Sheet1'!$K$6


As you can see , the external link is a complete link with the full path and file name ; the LinkSources property returns the external link :


F:KNExcel_ConsultancyNAGovindtestBook1.xlsm


Everything will work properly , even the code I posted !


Now , open the external book Book1.xlsm , and see what happens ; the formula in cell K9 now becomes :


=J12+[Book1.xlsm]Sheet1!$K$6


The LinkSources property still returns :


F:KNExcel_ConsultancyNAGovindtestBook1.xlsm


Which is why the code I posted will no longer work.


Narayan
 
@ Debraj Roy, Sorry the code is not working and saying subscript out of range


@ Narayn


Sorry i'm unable to understand the reason


Sorry for rewriting the query


Below is the code which creates a new sheet to show the External referred links that are external to the current workbook fine


Query is to FIND the CELLS OR OBJECTS WHICH IS HOLDING the above references/ links that is referring to external workbook so that we can go to that particular cell or an object and edit/ remove the external links to ensure that the external references are removed and the workbook calculations are now reliable and NOT depend on the other work books


Worst scenario is a object linked to external workbook say a test box or other shapes hyper linked or macro assigned shapes is the major issue here


Please advise to obtain a solution


Sub ListLinks()

Dim aLinks As Variant

aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then

Sheets.Add

For i = 1 To UBound(aLinks)

Cells(i, 1).Value = aLinks(i)

Next i

End If

End Sub
 
Hi Govind ,


I cannot understand how I can explain more clearly ; let me try one more time :


1. The LinkSources property returns the full path + file name link , irrespective of whether the external file is open or not.


2. The statement cell.formula returns the equivalent of the full path + file name if the external file is closed ; if the external file is open , the statement cell.formula returns only the file name.


3. The code I had first posted works if the external file is closed ; it will not work if the external file is open.


4. Since my code is looking at cells , it will NEVER work if the link is within a shape.


Can you clearly explain which of these 4 points is not clear ?


The same code will not work for links within cells and links within shapes , since the properties of these two objects are different , at least as far as I know.


Narayan
 
@ Narayan,


I have done as below with no results


1. Open new workbook "Book 1" enter a value say 100 in cell A1

2. Open another new workbook "Book 2"

3. In workbook Book 2 in cell B1 insert the formula or just link the value of A1 cell of Book 1

4. Save Book 2 exit

5. Save Book 1 exit


6. Open Book 2 now this will show warning message that "This workbook contains links to other data sources"


7. After clicking update and opening the VBA editor and inserting the module and pasted your code (in Book 2 which is having the link to Book 1) After running the code it is adding a sheet without any other result


Please advise
 
Hi Govind ,


I followed your procedure step by step ; the following was put in cells A1 and B1 of the added sheet :

[pre]
Code: 
$B$1	F:TemptempBook1.xlsm
[/pre]
I had forgotten to tell you that prior to running the procedure , you should select the range which has the formulae with the external links ; did you do that ? If you did not , then what you get will be a blank worksheet.


Narayan
 
Dear Narayan,


Yes the code is working once after running the code after selecting the cell which is referring the other sheet


But the requirement itself to find the the cell which is linked to other workbook !


Is there is any possibility that to merge your code with the one which i have posted so that we will get the actual requirement


Thank you
 
Hi Govind ,


With respect to your own procedure , can you explain what should appear in cell A1 of the newly added sheet in Book2.xlsm ? Should it show the cell which has the formula link to the external workbook , which in your case is B1 , or should it show the cell in the external workbook to which the formula is referring , which in your case will be Sheet1!A1 ?


Narayan
 
Dear Narayan

Thanks for your resolution


Actually your code is working well after selecting the area where the external link is placed. So the requirement is without selecting the area that is holding the external link code has to work


In other words some enhancement is required


There is a workbook with n number of sheets. We don’t know in which cell of which sheet(s) holds the external reference so the intention is just to find the location of the CELL in the whole workbook which are referring to external workbook

Only enhancement required i.e. is after running the code it has to seek the external references from all the sheets in the workbook (without manual selection of the cells/ area) and consolidate those cell with sheet name in a new sheet


Once the above is done it is easy to trace the cells in each sheet after knowing the location of cells in each sheet of a work book


The code which I have posted is even working for the hyperlinks/ assigned macros that are referred by an OBJECT/ SHAPES


Hence I requested you the possibility of merging the code you have posted and the one which I have posted


Thanks very much for your help
 
Hi Govind ,


For a link within a shape what do you want should be printed , just the external link or any additional information ?


Narayan
 
Dear Narayan,


Thank you for your response


1. For a cell which is holding the external reference

2. For a shape which is holding the external reference


For the above 2 cases the result required is


Creating a sheet and in the sheet the summary shall be as below


Cell address with sheet name | PATH of external reference


Shape name identification (say Textbox 1) with Sheet name | PATH of external reference


Hope my query is clear


If there is a solution exits for the above then it is really useful for many users

as the external reference is altering the SENSITIVE CALCULATION results UNKNOWINGLY to ensure the external links that are referring to the other workbook it has to be properly traced this is the intention of this query


Thank You
 
Hi Govind ,


See if this is OK with you ; please note that the code does not check for whether the report sheet exists or not.

[pre]
Code: 
Public Sub Printout_Links()
Const REPORTNAME = "Report_123"

Dim aLinks As Variant
Dim wbk As Workbook
Dim wks As Worksheet
Dim dobj As Object
Dim i As Integer, j As Integer
Dim first_pass As Boolean
first_pass = True

Set wbk = ThisWorkbook
aLinks = wbk.LinkSources(xlExcelLinks)

If Not IsEmpty(aLinks) Then
For Each wks In wbk.Worksheets
For Each dobj In wks.DrawingObjects
fmla = dobj.Formula
If InStr(1, fmla, "]") > 0 Then
ext_link = VBA.Replace(VBA.Replace(fmla, "[", ""), "]", "")
For i = LBound(aLinks, 1) To UBound(aLinks, 1)
If InStr(1, ext_link, aLinks(i)) Then
If first_pass Then
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = REPORTNAME
first_pass = False
End If
Sheets(REPORTNAME).Range("A1").Offset(j, 0) = wks.Name
Sheets(REPORTNAME).Range("A1").Offset(j, 1) = dobj.Name
Sheets(REPORTNAME).Range("A1").Offset(j, 2) = fmla
j = j + 1
End If
Next
End If
Next
Next
End If
If Not first_pass Then Sheets(REPORTNAME).Columns.AutoFit
Set wbk = Nothing
End Sub
[/pre]
Narayan
 
Dear Narayan.

I am just learning VBA so please excuse my potential ignorance.

When I post your code something it is not working, se image. Do you know that the problem is?
 

Attachments

  • Capture.JPG
    Capture.JPG
    59.3 KB · Views: 4
Hi ,

A year or two back , this forum migrated to this platform from an earlier one ; in the process of migration some of the symbols have been lost ; thus what you see as :

" is actually the double quote symbol " ; you need to replace all occurrences of " by the double quote symbol "

> is actually the greater than symbol > ; you need to replace all occurrences of > by the greater than symbol >

&#60; is actually the less than symbol < ; you need to replace all occurrences of &#60; by the less than symbol <

Narayan
 
Thank you so much for replying.

I did the search & replace but now get a new error message

"Run-time error '438':
Object doesnt support this property or method

This relates to the following code:
fmla = dobj.Formula

Do you recognize this?
 
Hi ,

If you can post your workbook , it will make troubleshooting easier ; you need to see whether the object dobj has a property called Formula ; when this error is displayed , can you click on the Debug button , and in the Immediate window , type in the following :

?typename(dobj)

and post what is displayed ?

Narayan
 
Dear Narayan,

I'm getting the below error
Please do the needful

upload_2015-2-7_22-8-39.png
 

Attachments

  • External reference.xlsm
    40.8 KB · Views: 8
Dear Narayan.

I was able to find the hidden links old-school-way, aka deleting sheet after sheet and rows and columns and checking the links along the way until I could pinpoint the location.

The hidden links was indeed due to some zombie data validation links so wish I had checked that problem more closely before I started to look this way.

Tip for anyone else in here with hidden link (repeating others tips):

Check:
Formulas - Name Manager for links that are outof date
Data validation - find these by Home - Editing - Find & Select - Data_Validation
 
You must log in or register to reply here.
Back
Top