title image


Smiley Re: automatische Verknüpfungen finden
I use the following code:



Sub FindExternalLinks()

On Error Resume Next

Application.DisplayAlerts = False

Worksheets("External Links").Delete

Application.DisplayAlerts = True

On Error GoTo 0



With Worksheets.Add(before:=sheets(1))

.name = "External Links"

End With



For Each s In ActiveWorkbook.Worksheets

Set r = Nothing

On Error Resume Next

Set r = s.UsedRange.SpecialCells(xlFormulas)

On Error GoTo 0

If Not r Is Nothing Then

Worksheets("External Links").Cells(1, 1) = "Sheet Name"

Worksheets("External Links").Cells(1, 2) = "Cell Address"

Worksheets("External Links").Cells(1, 3) = "External Link"

For Each a In r.Areas

For Each c In a.Cells

'''If InStr(c.Formula, "!") Then '''Links to Sheets

'''If InStr(c.Formula, "=") Then '''Formulas

If InStr(c.Formula, "[") Then '''Links to Files

x = x + 1

Worksheets("External Links").Cells(x + 1, 1) = s.name

Worksheets("External Links").Cells(x + 1, 2) = c.Address

Worksheets("External Links").Cells(x + 1, 3) = "'" & c.Formula

End If

Next c

Next a

End If

Next s

sheets("External Links").Select

Range("A1:C1").Font.Bold = True

Columns("A:C").Columns.AutoFit

Range("a1").Select

End Sub



You can also get -for free-

ASAP Utilities at http://www.asap-utilities.com or

PUP 6.0 at http://www.j-walk.com



Hope this helps

Manfred



geschrieben von

Login

E-Mail:
  

Passwort:
  

Beitrag anfügen

Symbol:
 
 
 
 
 
 
 
 
 
 
 
 
 

Überschrift: