<patrick_molloy@hotmail.com>De: Patrick Molloy 
Objet: Re: Macro & linking File...
Date : mardi 12 octobre 1999 08:20

dim a variable to use for the selected range rather than selecting the
range itself...

dim rSearch as range
dim rCell as range
SET rSearch = _
activeworksheet.usedrange.specialcells(xlcelltypeformulas,3)
IF NOT rSearch is nothing then
	for each rCell in rSearch
	...
	next
end if


On Wed, 6 Oct 1999 09:19:08 +0200, "St&eacute;phane Royer"<sroyer@vanenburg.com>
 wrote:

>Other solutions i found & fit or that i built :
>What do you think about them ?
>
>
>Sub listlinks_FOR()
>dim s As String, cell As range
>application.screenupdating = false
>
>activesheet.Usedrange.Specialcells(xlCelltypeformulas).select
>'the macro will be quick by using this selection method,
>'i advice using this because the macro takes a long using "for ... each"
>'and the information you want is in formulas
>
>with selection
>    for each cell In selection
>    s = cell.formula
>
>        if s Like "*.xls*" then  ' it corresponds to an external link to a
>workbook
>                cell.Interior.ColorIndex = 36 'light yellow color
>                                              'you can fill a cell with
>cell.address of course
>        end if
>
>   next cell
>end with
>
>range("A1").select
>application.screenupdating = true
>end Sub
>
>Sub listlinks_find()
>
>
>dim cellule As range, Dep As String, i As Integer: i = 1: range("A1").select
>'On Error Resume next
>
>set cellule =
>activesheet.Usedrange.Specialcells(xlCelltypeformulas).find(what:=".xls",
>After:=activecell, LookIn:=xlformulas, _
>        LookAt:=xlPart, SearchOrder:=xlByrows, SearchDirection:=xlnext, _
>        MatchCase:=false) 'can be simplify
>
>
>if cellule Is Nothing then Exit Sub
>        Dep = cellule.address
>
>do
>  set cellule = cells.findnext(cellule)
>  range("B" & i).value = cellule.address
>  range("c" & i).value = Mid(cellule.formula, 3, Len(cellule.formula))
>   'or range("c" & i).value = "'" + cellule.formula
>  i = i + 1
>loop Until cellule.address = Dep
>
>
>end Sub
>
>'When the links are in the page where you create this report, the macro loop
>and makes the search in cells where
>'it has just written to :-)
>'So how to do ?? (it seems using cells is in this case more efficient !!!)
>
>'i thought of working longer on this macro to add the creation of a report
>sheet, in a view to displaying on it
>'the whole workbook links, but itwas late yesterday evening :-)) & i spent
>enough time in front of my computer :-))
>
>
>Bye
>Stephane
>
>St&eacute;phane Royer a &eacute;crit dans le message ...
>>In fact, i don't know how to get the address of such cells, that's why i
>>posted.
>>I know this code doesn't return it :-)))
>>I try to use the instruction address & parent but failed
>>I once read a code that find links by searching "]" & ".xls" strings but is
>>it very efficient
>>
>>bye
>>stephane
>>
>>
>>tom Ogilvy a &eacute;crit dans le message ...
>>>St&eacute;phane
>>>
>>>That code will only show you the same information that is in the
>>Edit=>links
>>>menu: what workbook you are linked to.  It won't show you what items in
>>your
>>>workbook contain links.   You don't say where your code is failing.
>>>
>>>Regards,
>>>tom Ogilvy
>>>
>>>
>>>St&eacute;phane Royer wrote in message ...
>>>>Jim
>>>>
>>>>I worker around this code that lays in help, but doesn't succeed
>>>>I may have missed the easy solution !!
>>>>
>>>>alinks = activeworkbook.linkSources(xlOLElinks)
>>>>if Not IsEmpty(alinks) then
>>>> for i = 1 to ubound(alinks)
>>>>  msgbox "Liaison " & i & ":" & Chr(13) & alinks(i)
>>>> next i
>>>>end if
>>>>
>>>>
>>>>Bye
>>>>STeph
>>>>
>>>>
>>>>Jim Rech a &eacute;crit dans le message ...
>>>>>I think Bill's utility tells you where the link is when it is caused by
>a
>>>>>formula.
>>>>>
>>>>>--
>>>>>Jim Rech
>>>>>Excel MVP
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>