Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting linked area (Office 2000)

    The following code establishes a linked area in a protected Word Template with protected text fields:


    Sub CreateLink()
    ActiveDocument.Unprotect Password:="Test"
    Selection.GoTo What:=wdGoToBookmark, Name:="Text1"
    Selection.PasteSpecial Link:=True, DataType:=wdPasteOLEObject, _
    Placement:=wdInLine, DisplayAsIcon:=False
    Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
    With Selection.InlineShapes(1)
    .Height = InchesToPoints(3.18)
    .Width = InchesToPoints(6.18)
    End With
    ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="Test"
    End Sub

    The location, "Text 1" is a protected text field that identifies the location on the template where the link will be pasted.
    What I need to do is delete the link before establishing a new one in the same location with the above macro (keeps the template protected). Is there something I can type at the beginning of the macro to delete the link without also deleting the text field "Text 1"? Then I would run this macro to establish a new link.

    Thanks.

  2. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting linked area (Office 2000)

    Just hoping someone has an answer.

    Thanks.

  3. #3
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Deleting linked area (Office 2000)

    Your code is deleting the existing selection so I would suggest you add a Selection.Collapse line after you goto the bookmark or go to a bookmark after the formfield itself.

    I am wondering what the point of pasting a link is if you replace it this way. Either use the link to update automatically or don't bother linking and use the macro.

    Is your grand plan to bookmark both the form text field and the pasted object? If so then the code will need to be modified to reset the bookmark after the paste has been done.

    Try stepping through the code with the bookmarks set to be visible and pay attention to whether items are in or out of these bookmarks during following each step of the macro. Then think about what will happen when the macro is run twice on the same file. The current code with the Selection.Collapse added won't give a reproducible result so you need to change the logic a little so that the pasted object is replaced rather than added.

    If you need more information to solve this problem, you will need to be clearer on what is actually inside the bookmark before the macro is run and what is supposed to be inside it afterwards.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #4
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting linked area (Office 2000)

    Sorry its taken so long to respond. I was away from my email.

    I used the bookmark to set the location on the page where I will paste the link from Excel. In some cases, there will be no "object" but in others there will be a previously pasted link from Excel. My original code handled only cases where no previous link existed and worked fine. Now, the source of the link has been updated and in cases where there was an existing Word document linked to the Excel file, there will already be a linked area in that document. When I am in this document in Word, I can easily unprotect, click on the object, hit the "delete" key and reprotect the document. I want to be able to automate this process as follows:

    1 Unprotect document
    2. delete bookmark (which should delete any previous object?)
    3. reestablish bookmark with same name
    4. paste a link from Excel (see code which centers, and sizes link in bookmarked field named Text1 from my previous note)
    5. reprotect document using same password
    What I'm looking for I guess is code that will do steps 2 & 3. I should be able to then just add my existing code to take care of the link.

    Thanks.

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Deleting linked area (Office 2000)

    If you step through the way Andrew describes, you will see that unlike most other scenarios, selecting a text formfield and then using PasteSpecial with an OLE object does not delete the formfield, but instead inserts the object to the left of it. The bookmark on the text formfield stays where it was, and is not extended to cover the object. So why not create a new bookmark as part of your process, let's call it, ExcelSnippet:

    <pre>Sub CreateLink()
    'If document is protected for forms, unprotect it
    If ActiveDocument.ProtectionType = wdAllowOnlyFormFields Then
    ActiveDocument.Unprotect 'Password:="Test"
    ElseIf ActiveDocument.ProtectionType <> wdNoProtection Then
    MsgBox "This macro is not relevant to this kind of protection."
    Exit Sub
    End If
    'Select Text1 bookmark, paste special, then bookmark and format object
    With Selection
    .GoTo What:=wdGoToBookmark, Name:="Text1"
    .PasteSpecial Link:=True, DataType:=wdPasteOLEObject, _
    Placement:=wdInLine, DisplayAsIcon:=False
    .MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
    .Bookmarks.Add Name:="ExcelSnippet", Range:=Selection.Range
    With .InlineShapes(1)
    .Height = InchesToPoints(3.18)
    .Width = InchesToPoints(6.18)
    End With
    End With
    ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True ', Password:="Test"
    End Sub</pre>

    Now, with a bookmark expressly identifying your OLE object, you can write code to detect and replace it:

    <pre>Sub CreateLink()
    'If document is protected for forms, unprotect it
    If ActiveDocument.ProtectionType = wdAllowOnlyFormFields Then
    ActiveDocument.Unprotect 'Password:="Test"
    ElseIf ActiveDocument.ProtectionType <> wdNoProtection Then
    MsgBox "This macro is not relevant to this kind of protection."
    Exit Sub
    End If
    'Position insertion point (clearing old OLE object if applicable)
    ' then paste special, bookmark and format object
    With Selection
    If ActiveDocument.Bookmarks.Exists("ExcelSnippet") = True Then
    'Remove the old OLE object
    ActiveDocument.Bookmarks("ExcelSnippet").Select
    .Delete
    Else
    'Position insertion point just before Text1
    .GoTo What:=wdGoToBookmark, Name:="Text1"
    .Collapse Direction:=wdCollapseStart
    End If

    .PasteSpecial Link:=True, DataType:=wdPasteOLEObject, _
    Placement:=wdInLine, DisplayAsIcon:=False
    .MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
    .Bookmarks.Add Name:="ExcelSnippet", Range:=Selection.Range
    With .InlineShapes(1)
    .Height = InchesToPoints(3.18)
    .Width = InchesToPoints(6.18)
    End With
    End With
    ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True ', Password:="Test"
    End Sub</pre>

    Note: I tested this lightly, but I can't endorse it as fault tolerant!!

  6. #6
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting linked area (Office 2000)

    Thanks for your help on this. I'll try it on Monday and let you know how it works.

  7. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Deleting linked area (Office 2000)

    Hi. I listed two CreateLink() procedures. Use only the second one. They were not meant to be combined. Hopefully that explains the dysfunctionality.

  8. #8
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting linked area (Office 2000)

    I had to make some changes for it to run. Although it works when there is no previous link, if there is a link already at Text1 it does the same as my original, simply links a second object directly under the first.

    Would appreciate it if you could look at the revised code and see what went wrong.

    Thanks.

    Sub CreateLink()
    Code deleted by Jefferson Scher to save space
    End Sub
    Attached Files Attached Files

  9. #9
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting linked area (Office 2000)

    I'll try it tomorrow morning before others log on. Looks like it will work. I'll let you know.

    Thanks for your help.

  10. #10
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting linked area (Office 2000)

    We are close but no cigar yet:
    When I had no previous links, the macro established a new link. If I tried envoking it again, it properly deleted the first link and established a second (more recent) link in its place. This is exactly what I wanted it to do. However, where I had a file with a previouls link already established, the macro created a new link on top of the old so there were two links on the page. When I envoked it again, it deleted the more recent link and replaced it with a new one leaving the old link below it on the page.

  11. #11
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Deleting linked area (Office 2000)

    That's because it only checks for the ExcelSnippet bookmark and doesn't "sniff" the selection to the left of Text1 to see if it's your linked sheet. There's probably a way to do that, too, but I don't know if it would be reliable.

  12. #12
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting linked area (Office 2000)

    Couldn't we just have it start by deleting whatever object exists to the left of the bookmark then proceed with the balance of the macro?

  13. #13
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Deleting linked area (Office 2000)

    I think testing on your existing documents is the only way to know whether that is a good idea. I recommend backups. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  14. #14
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting linked area (Office 2000)

    I'll do that. Could you tell me what part of the code specifically looks for an object to the left of the bookmark and what code deletes this object?

    I'll incorporate it in the macro.

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •