Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jul 2007
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Control how linked fields update using code (2003)

    I have a Word doc with many links to Excel spreadsheet. The state of the links seem to jump to "auto update" (even though I have removed all the /a from the field codes.
    Could someone give me a macro which would "freeze" the auto/manual state of the field

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Control how linked fields update using code (2003)

    Hi Des,

    You can lock a field with Ctrl-F11 and unlock it again with Ctrl-Shift-F11. You can doo the same with vba, but we'd need to know which links you want to act on before being able to code appropriately.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Lounger
    Join Date
    Jul 2007
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control how linked fields update using code (2003)

    Paul, thanks very much. A macro to change the state (or is Property the correct word?) of all linked fields would be great.
    Ideally, one macro to change all links to "manual" and another macro to change all links to "Locked".
    Hope you can help.
    Des Murphy

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Control how linked fields update using code (2003)

    Hi Des,

    Try:
    Sub ToggleFieldLock()
    With ActiveDocument.Fields
    .Locked = Not .Locked
    End With
    End Sub

    That's all you need to toggle the lock state.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    New Lounger
    Join Date
    Jul 2007
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control how linked fields update using code (2003)

    Paul, works great and thanks. Is there a way to make it "manual"?
    Also, is there a way to include headers?
    Des

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Control how linked fields update using code (2003)

    Hi Des,

    I don't understand the question: Is there a way to make it "manual"? This isn't an auto macro.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    New Lounger
    Join Date
    Jul 2007
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control how linked fields update using code (2003)

    Paul, in Edit/Links there are three options for updating links; auto, manual and locked. I am looking for a macro to change all links to "manual", and when I have the doc ready for release, a macro to make them all "locked". The links are all to Excel and comprise cell values and charts.
    Hope you can help.
    Des

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Control how linked fields update using code (2003)

    Hi Des,

    That's a rather different proposition. Here's some code to do it - 4 subs in all, so that you can unlock & automate the links again if you need to.

    Sub LockLinks()
    Dim oShp As Shape
    Dim iShp As InlineShape
    For Each oShp In ActiveDocument.Shapes
    If oShp.Type = msoLinkedOLEObject Then _
    If InStr(oShp.OLEFormat.ProgID, "Excel") > 0 Then _
    oShp.LinkFormat.Locked = True
    Next oShp
    For Each iShp In ActiveDocument.InlineShapes
    If iShp.Type = wdInlineShapeLinkedOLEObject Then _
    If InStr(iShp.OLEFormat.ProgID, "Excel") > 0 Then _
    iShp.LinkFormat.Locked = True
    Next iShp
    End Sub

    Sub UnLockLinks()
    Dim oShp As Shape
    Dim iShp As InlineShape
    For Each oShp In ActiveDocument.Shapes
    If oShp.Type = msoLinkedOLEObject Then _
    If InStr(oShp.OLEFormat.ProgID, "Excel") > 0 Then _
    oShp.LinkFormat.Locked = False
    Next oShp
    For Each iShp In ActiveDocument.InlineShapes
    If iShp.Type = wdInlineShapeLinkedOLEObject Then _
    If InStr(iShp.OLEFormat.ProgID, "Excel") > 0 Then _
    iShp.LinkFormat.Locked = False
    Next iShp
    End Sub

    Sub MakeLinksAuto()
    Dim oShp As Shape
    Dim iShp As InlineShape
    For Each oShp In ActiveDocument.Shapes
    If oShp.Type = msoLinkedOLEObject Then _
    If InStr(oShp.OLEFormat.ProgID, "Excel") > 0 Then _
    oShp.LinkFormat.AutoUpdate = True
    Next oShp
    For Each iShp In ActiveDocument.InlineShapes
    If iShp.Type = wdInlineShapeLinkedOLEObject Then _
    If InStr(iShp.OLEFormat.ProgID, "Excel") > 0 Then _
    iShp.LinkFormat.AutoUpdate = True
    Next iShp
    End Sub

    Sub MakeLinksManual()
    Dim oShp As Shape
    Dim iShp As InlineShape
    For Each oShp In ActiveDocument.Shapes
    If oShp.Type = msoLinkedOLEObject Then _
    If InStr(oShp.OLEFormat.ProgID, "Excel") > 0 Then _
    oShp.LinkFormat.AutoUpdate = False
    Next oShp
    For Each iShp In ActiveDocument.InlineShapes
    If iShp.Type = wdInlineShapeLinkedOLEObject Then _
    If InStr(iShp.OLEFormat.ProgID, "Excel") > 0 Then _
    iShp.LinkFormat.AutoUpdate = False
    Next iShp
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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