Tuesday, July 29, 2008

Identify Objects that are linked to Source file using Excel VBA

Extract all Linked Objects in Excel Spreadsheet using VBA

At times when you send out some document, you should be careful of the external links that the document contains. The following code might help you:

Sub Extract_Linked_Objects()

Dim oWS As Worksheet ' Worksheet Object

Dim oOLE As OLEObject ' OLE Object

On Error GoTo Err_OLE

Set oWS = ActiveSheet

If oWS.OLEObjects.Count = 0 Then Exit Sub

For Each oOLE In oWS.OLEObjects

If oOLE.OLEType = xlOLELink Then

MsgBox oOLE.SourceName

End If

Next

Finally:

If Not oOLE Is Nothing Then Set oOLE = Nothing

If Not oWS Is Nothing Then Set oWS = Nothing

Err_OLE:

If Err <> 0 Then

Err.Clear

GoTo Finally

End If

End Sub





No comments:

Post a Comment

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.