links in multiple workbooks

  • Thread starter Thread starter Guest
  • Start date Start date
If you mean you're looking for formulas that are retrieving values from other
workbooks, you could use Edit|find and look through formulas for things like
".xls". But I find using Bill Manville's FindLink program much easier:
http://www.oaltd.co.uk/MVP/Default.htm

But if you meant you're looking for all the workbooks that have formulas that
link to a specific workbook (your workbook is "sending" the data), then I don't
think that there's a good way to find all those possible workbooks.

You could open each possible "receiving" workbook and look, but that only works
if you can at least limit the number of workbooks to search.
 
I am looking for cells in a workbook that contribute to a link in a different
workbook.
(your second answer, I believe)
 
The bad news is that workbooks can live anywhere--they can be in folders that
you know about and folders that you don't know about. People can store a copy
on a thumbdrive, a CD a floppy, a network drive, ... and you wouldn't even know
where to start looking.

I saved this from a previous post:

You can find the references/links that a workbook uses by: Edit|links.

But finding the links in the other direction is opening each and checking to see
if it used it. And these files could be anywhere on your local harddrive(s),
(even) floppies, CD ROMS, Network drives....

And if the file is on a network drive the link could be via a mapped drive (like
M:\myfolder\myfile.xls) or via the UNC name:
\\myserver\mysharename\myfolder\myfile.xls

And both of those could be pointing at the same file.

But with all those admonitions, maybe this'll get you started. It just looks
for the C:\path\filename.xls within a link. If found, it adds it to a report
worksheet.

It only looks at one folder at a time. But you can run it against as many
folders as you want.

If you save this macro in a workbook, then don't store it in the same folder as
you're searching. I didn't put any check to make sure that the code doesn't try
to re-open itself (but you could add it if you want).



Option Explicit
Sub testme01()

Application.ScreenUpdating = False

Dim myFiles() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim tempWkbk As Workbook
Dim logWks As Worksheet
Dim oRow As Long
Dim myLinks As Variant
Dim linkCtr As Long

Dim myLinkName As String
'Change what to look for here!
myLinkName = "C:\my documents\excel\book1.xls"

Set logWks = Workbooks.Add(1).Worksheets(1)
With logWks
.Name = "Log_" & Format(Now, "yyyymmdd_hhmmss")
.Range("a1:c1").Value _
= Array("Sequence", "WorkbookName", "Links")
End With
oRow = 1

'change to point at the folder to check
myPath = "c:\my documents\excel\test"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile <> ""
fCtr = fCtr + 1
ReDim Preserve myFiles(1 To fCtr)
myFiles(fCtr) = myFile
myFile = Dir()
Loop

If fCtr > 0 Then
For fCtr = LBound(myFiles) To UBound(myFiles)
Application.StatusBar _
= "Processing: " & myFiles(fCtr) & " at: " & Now
Set tempWkbk = Nothing
On Error Resume Next
Application.EnableEvents = False
Set tempWkbk = Workbooks.Open(Filename:=myPath & myFiles(fCtr), _
UpdateLinks:=0)
Application.EnableEvents = True
On Error GoTo 0

oRow = oRow + 1
logWks.Cells(oRow, 2).Value = myPath & myFiles(fCtr)

If tempWkbk Is Nothing Then
'couldn't open it for some reason
logWks.Cells(oRow, 3).Value = "Error opening workbook"
Else
With tempWkbk
myLinks = .LinkSources
If IsArray(myLinks) Then
For linkCtr = LBound(myLinks) To UBound(myLinks)
If LCase(myLinks(linkCtr)) = LCase(myLinkName) Then
logWks.Cells(oRow, 3).Value _
= "Has Link to: " & myLinkName
End If
Next linkCtr
End If
.Close SaveChanges:=False
End With
End If
Next fCtr
logWks.UsedRange.Columns.AutoFit
Else
logWks.Parent.Close SaveChanges:=False
End If

With logWks
With .Range("a2:a" & .Cells(.Rows.Count, "B").End(xlUp).Row)
.Formula = "=row()-1"
.Value = .Value
End With
End With

With Application
.ScreenUpdating = True
.StatusBar = False
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top