Solved

How to recreate OLEobject (linked word doc) after link is broken (invalid link has been set)

Posted on 2013-05-15
2
378 Views
Last Modified: 2013-05-22
Hi,

I have created an Excel dashboard that displays the content of an appropriate linked word document in the form of an OLEobject. The links (path & filename) are stored in a list and when an icon (representing a piece of equipment) is clicked, my macro looks up the link and changes the SourceName property  of the OLEobject appropriately. This works fine until an invalid link is set (and this is a possibility as I will not be maintaining the list).

When this happens the OLEobject changes into a simple Shape i.e. it ceases to be an OLEobject.

If another icon is clicked which does have a valid path, I would like to programmatically "reset" the shape back to being an OLEobject but can't find a way to do this. I can test to see if the link has been broken by cycling through the OLEobjects collection but that is all.

My OLE object has been named "LinkedWordDoc1". The path & file name of the word document are contained in the string sWordDoc.

Here is the relevant bit of my code:

 
   'test for valid OLEobject - linked word doc
    bFound = False
    For Each oOLEobj In ActiveSheet.OLEObjects
        If oOLEobj.Name = "LinkedWordDoc1" Then
            bFound = True
            Exit For
        End If
    Next
    
    'display associated word doc
    If bFound Then
        ActiveSheet.OLEObjects("LinkedWordDoc1").SourceName = "Word.Document.12|" & sWordDoc & "!'"
    Else
    'recreate OLE object
        With ActiveSheet.Shapes.Range(Array("LinkedWordDoc1"))
        
              'THIS IS WHERE THE PROBLEM IS!!!!!!!

        End With
           
    End If

Open in new window

   

Thanks,

Neil
0
Comment
Question by:younglight
2 Comments
 
LVL 14

Accepted Solution

by:
Faustulus earned 500 total points
Comment Utility
Once the link is broken the OLEobject ceases to exist. You need to recreate it from scratch. This code will do the job.
    Else
    'recreate OLE object
        Set oOLEobj = ActiveSheet.OLEObjects.Add(Filename:=sWordDoc, _
                      Link:=True, DisplayAsIcon:=False)
        With oOLEobj
            .Name = "LinkedWordDoc1"
            .Top = 100
            .Left = 50
        End With
    End If

Open in new window

The values of Top and Left define the upper left corner of the object in points. You may have to experiment a little or read the values from an object that already exists.
0
 

Author Closing Comment

by:younglight
Comment Utility
Ok thanks. I'll just need to delete the "shape" left when the link was broken.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now