Identifying workbooks with links to external file

I have a list of workbooks and I want to be able to determine which ones have
a link to an external file. All I want at this point is "YES" or "NO".
What do I need to do programmatically to capture if a link is present in a
file. I've got the FINDLINK utility available, but I need to know how many
documents are affected first.

Tell me what needs to be done to this

Sub LinksPresent()
Dim oWB As Workbook
Dim aWB As Workbook
Dim aWS As Worksheet

Set aWB = ActiveWorkbook
Set aWS = ActiveSheet
Range("H1").Select
ActiveCell.FormulaR1C1 = "Link Present"
For i = 2 To Cells(Rows.Count, "A").End(xlUp).row
Set oWB = Workbooks.Open(Cells(i, "B"), ReadOnly = True, UpdateLinks
= False)
Range("H" & i).Select
ActiveCell.FormulaR1C1 = "=WHAT GOES HERE"
Next i
aWB.Save
End Sub

Thanks in advance,
Barb Reinhardt

Sub LinksPresent()
Dim oWB As Workbook
Dim aWB As Workbook
Dim aWS As Worksheet

Set aWB = ActiveWorkbook
Set aWS = ActiveSheet
Range("H1").Select
ActiveCell.FormulaR1C1 = "Link Present"
For i = 2 To Cells(Rows.Count, "A").End(xlUp).row
Set oWB = Workbooks.Open(Cells(i, "B"), ReadOnly = True, UpdateLinks
= False)
WS.Range("H" & i).Value = iif(commandbars("Edit") _
.Controls("Lin&ks...").Enabled,"Yes","No")
oWB.Close SaveChanges:=False
Next i
aWB.Save
End Sub

to get a list of links

Dim alinks as Variant
aLinks = oWB.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End Ifto put them in the sheet

Dim aLinks as Variant
nLnks as Long
aLinks = oWB.LinkSources(xlExcelLinks)
nLnks = Ubound(aLinks) - lbound(alinks) + 1
WS.Range("H" & i).Resize(1,nLnks) = alinks


--
Regards,
Tom Ogilvy



"Barb Reinhardt" <BarbReinhardt@discussions.microsoft.com> wrote in message
news:D21B0D45-D7B0-48F6-B7F8-A33B65527AF2@microsoft.com...
> I have a list of workbooks and I want to be able to determine which ones
have
> a link to an external file. All I want at this point is "YES" or "NO".
> What do I need to do programmatically to capture if a link is present in a
> file. I've got the FINDLINK utility available, but I need to know how
many
> documents are affected first.
>
> Tell me what needs to be done to this
>
> Sub LinksPresent()
> Dim oWB As Workbook
> Dim aWB As Workbook
> Dim aWS As Worksheet
>
> Set aWB = ActiveWorkbook
> Set aWS = ActiveSheet
> Range("H1").Select
> ActiveCell.FormulaR1C1 = "Link Present"
> For i = 2 To Cells(Rows.Count, "A").End(xlUp).row
> Set oWB = Workbooks.Open(Cells(i, "B"), ReadOnly = True,
UpdateLinks
> = False)
> Range("H" & i).Select
> ActiveCell.FormulaR1C1 = "=WHAT GOES HERE"
> Next i
> aWB.Save
> End Sub
>
> Thanks in advance,
> Barb Reinhardt
>
>
>
>

I needed to change WS to aWS to get this to work

> WS.Range("H" & i).Value = iif(commandbars("Edit") _
> .Controls("Lin&ks...").Enabled,"Yes","No")

"Tom Ogilvy" wrote:

> Sub LinksPresent()
> Dim oWB As Workbook
> Dim aWB As Workbook
> Dim aWS As Worksheet
>
> Set aWB = ActiveWorkbook
> Set aWS = ActiveSheet
> Range("H1").Select
> ActiveCell.FormulaR1C1 = "Link Present"
> For i = 2 To Cells(Rows.Count, "A").End(xlUp).row
> Set oWB = Workbooks.Open(Cells(i, "B"), ReadOnly = True, UpdateLinks
> = False)
> WS.Range("H" & i).Value = iif(commandbars("Edit") _
> .Controls("Lin&ks...").Enabled,"Yes","No")
> oWB.Close SaveChanges:=False
> Next i
> aWB.Save
> End Sub
>
> to get a list of links
>
> Dim alinks as Variant
> aLinks = oWB.LinkSources(xlExcelLinks)
> If Not IsEmpty(aLinks) Then
> For i = 1 To UBound(aLinks)
> MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
> Next i
> End Ifto put them in the sheet
>
> Dim aLinks as Variant
> nLnks as Long
> aLinks = oWB.LinkSources(xlExcelLinks)
> nLnks = Ubound(aLinks) - lbound(alinks) + 1
> WS.Range("H" & i).Resize(1,nLnks) = alinks
>
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Barb Reinhardt" <BarbReinhardt@discussions.microsoft.com> wrote in message
> news:D21B0D45-D7B0-48F6-B7F8-A33B65527AF2@microsoft.com...
> > I have a list of workbooks and I want to be able to determine which ones
> have
> > a link to an external file. All I want at this point is "YES" or "NO".
> > What do I need to do programmatically to capture if a link is present in a
> > file. I've got the FINDLINK utility available, but I need to know how
> many
> > documents are affected first.
> >
> > Tell me what needs to be done to this
> >
> > Sub LinksPresent()
> > Dim oWB As Workbook
> > Dim aWB As Workbook
> > Dim aWS As Worksheet
> >
> > Set aWB = ActiveWorkbook
> > Set aWS = ActiveSheet
> > Range("H1").Select
> > ActiveCell.FormulaR1C1 = "Link Present"
> > For i = 2 To Cells(Rows.Count, "A").End(xlUp).row
> > Set oWB = Workbooks.Open(Cells(i, "B"), ReadOnly = True,
> UpdateLinks
> > = False)
> > Range("H" & i).Select
> > ActiveCell.FormulaR1C1 = "=WHAT GOES HERE"
> > Next i
> > aWB.Save
> > End Sub
> >
> > Thanks in advance,
> > Barb Reinhardt
> >
> >
> >
> >
>
>
>

Glad you were able to figure it out.

--
Regards,
Tom Ogilvy


"Barb Reinhardt" <BarbReinhardt@discussions.microsoft.com> wrote in message
news:618B567E-61E6-49BA-BC4B-D187AA332E58@microsoft.com...
> I needed to change WS to aWS to get this to work
>
> > WS.Range("H" & i).Value = iif(commandbars("Edit") _
> > .Controls("Lin&ks...").Enabled,"Yes","No")
>
> "Tom Ogilvy" wrote:
>
> > Sub LinksPresent()
> > Dim oWB As Workbook
> > Dim aWB As Workbook
> > Dim aWS As Worksheet
> >
> > Set aWB = ActiveWorkbook
> > Set aWS = ActiveSheet
> > Range("H1").Select
> > ActiveCell.FormulaR1C1 = "Link Present"
> > For i = 2 To Cells(Rows.Count, "A").End(xlUp).row
> > Set oWB = Workbooks.Open(Cells(i, "B"), ReadOnly = True,
UpdateLinks
> > = False)
> > WS.Range("H" & i).Value = iif(commandbars("Edit") _
> > .Controls("Lin&ks...").Enabled,"Yes","No")
> > oWB.Close SaveChanges:=False
> > Next i
> > aWB.Save
> > End Sub
> >
> > to get a list of links
> >
> > Dim alinks as Variant
> > aLinks = oWB.LinkSources(xlExcelLinks)
> > If Not IsEmpty(aLinks) Then
> > For i = 1 To UBound(aLinks)
> > MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
> > Next i
> > End Ifto put them in the sheet
> >
> > Dim aLinks as Variant
> > nLnks as Long
> > aLinks = oWB.LinkSources(xlExcelLinks)
> > nLnks = Ubound(aLinks) - lbound(alinks) + 1
> > WS.Range("H" & i).Resize(1,nLnks) = alinks
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "Barb Reinhardt" <BarbReinhardt@discussions.microsoft.com> wrote in
message
> > news:D21B0D45-D7B0-48F6-B7F8-A33B65527AF2@microsoft.com...
> > > I have a list of workbooks and I want to be able to determine which
ones
> > have
> > > a link to an external file. All I want at this point is "YES" or
"NO".
> > > What do I need to do programmatically to capture if a link is present
in a
> > > file. I've got the FINDLINK utility available, but I need to know how
> > many
> > > documents are affected first.
> > >
> > > Tell me what needs to be done to this
> > >
> > > Sub LinksPresent()
> > > Dim oWB As Workbook
> > > Dim aWB As Workbook
> > > Dim aWS As Worksheet
> > >
> > > Set aWB = ActiveWorkbook
> > > Set aWS = ActiveSheet
> > > Range("H1").Select
> > > ActiveCell.FormulaR1C1 = "Link Present"
> > > For i = 2 To Cells(Rows.Count, "A").End(xlUp).row
> > > Set oWB = Workbooks.Open(Cells(i, "B"), ReadOnly = True,
> > UpdateLinks
> > > = False)
> > > Range("H" & i).Select
> > > ActiveCell.FormulaR1C1 = "=WHAT GOES HERE"
> > > Next i
> > > aWB.Save
> > > End Sub
> > >
> > > Thanks in advance,
> > > Barb Reinhardt
> > >
> > >
> > >
> > >
> >
> >
> >