Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying workbooks with links to external file
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying workbooks with links to external file
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying workbooks with links to external file
Glad you were able to figure it out.
-- Regards, Tom Ogilvy "Barb Reinhardt" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I break links to external workbooks en masse? | Excel Discussion (Misc queries) | |||
select all cells in an active sheet containg links to other (external) workbooks. | Excel Programming | |||
Shade cells with links to external workbooks | Excel Programming | |||
how do I activate links without opening external workbooks? | Excel Discussion (Misc queries) | |||
Identifying cells with external links using VBA | Excel Programming |