Link to home
Create AccountLog in
Avatar of Shanan212
Shanan212Flag for Canada

asked on

Ways to find out links on Excel

Hi,

I am having a problem with 2 links on my new excel file.

I've tried the following ways

- Searched for part of the linked file name and also searched for '[' throughout the workbook including in formulas

- Designed a custom macro to list each shape throughout the book (22 worksheets) and hundreds of shapes and their associated macro calls in another column. Then I checked this macro column for macro assignments which refer to macros on another workbook

- Used a procedure I found on web to list all 44k + formulas on single sheet as text and then checked out them for any links

- Checked the Name Manager counteless times for any reference to different workbook.

- Checked connections which is empty

Still could not find where these links are!!

Any help towards the right direction is much appreciated!
Avatar of redmondb
redmondb
Flag of Afghanistan image

Hi, Shanan212.

As a quick and dirty start, have you tried "Edit Links" - it's under "Data" in the Ribbon's Menu Bar.

Edit: Assuming that that doesn't help, please save us reinventing the (wobbly!) wheel by posting your custom macro.

Thanks,
Brian.
Are you looking for hyperlinks or for links in you workbook?

If the former do this which you'll need to modify to look in all sheets

Dim lnk As Hyperlink

For Each lnk In Hyperlinks
    Debug.Print lnk.Address
Next

Open in new window

and if the latter do

Dim aLinks
Dim i As Integer

aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
    For i = 1 To UBound(aLinks)
    MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
    Next i
End If

Open in new window

Avatar of Shanan212

ASKER

Custom Marco is below. Please note I am trying to find where 'links' are in my workbook. The custom macro is just to check my point #2 in 1st post which didnt not help me with finding the links

Sub GetShapeProperties()

    Dim sShapes As Shape, lLoop As Long, wsLoop As Worksheet
    Dim wsStart As Worksheet, WsNew As Worksheet

    Set WsNew = Sheets("Sheet1")
    WsNew.Select
    WsNew.Range("A1:B99").ClearContents
    
    WsNew.Range("A1:B1") = _
    Array("Shape Name", "Action")
    
    For Each wsLoop In Worksheets
        For Each sShapes In wsLoop.Shapes
            lLoop = lLoop + 1
            With sShapes
            
            WsNew.Cells(lLoop + 1, 1) = .Name
            WsNew.Cells(lLoop + 1, 2) = .OnAction
            
            End With
        Next sShapes
    Next wsLoop
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of redmondb
redmondb
Flag of Afghanistan image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Hi Brian,

Thanks for the macros. I've tried all 3. The 3rd one isn't working as it skips after this line

For Each obj In ActiveWorkbook.Names

I only have the Findlinks and my problem-file opened when running the macro.

Now, I tried an experiment. I tried to link a 3rd file to my problem file. Then Copy - Paste values for the entries sheet. This copied and pasted values over the sheet and  effectively removed the link (that I manually created)

After this, I created a sample of my problem-file. Copied and pasted values for all 30 sheets. The links still exists!!

Again, we already checked hyperlinks, macro-references and connections (which is empty)

Only thing left is data-validation thats referring to other worksheets I believe. Got any tricks to find this?

I made some progress as in

ActiveCell.SpecialCells(xlCellTypeAllValidation).Select

So I can select the cells in a sheet which has validation. Now any help to to list them in a seperate sheet along with their reference in another column would help!

Thanks!
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I've requested that this question be closed as follows:

Accepted answer: 250 points for redmondb's comment #a38614665
Assisted answer: 0 points for Shanan212's comment #a38607809
Assisted answer: 250 points for redmondb's comment #a38607910
Assisted answer: 0 points for Shanan212's comment #a38614673

for the following reason:

Solved it!
woops

I meant to reward points for Brain. Could a MOD please award points to Brain to his last 2 posts with 'A' quality?

Thanks!
Well done, Shanan212!

For the sake of anyone following, here are two relevant links...
Macros to document Data Validation.
Manually identify Data Validation

Regards,
Brian.
Hi.

Shanan212 sorted this out himself, so please just allocate 0 points to his posts as he requested.

Thanks,
Brian.
Thanks!
Thanks, Shanan212.