Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Break Any/All Links in VBA (Excel 2003)

    I have recorded a macro to Copy two worksheets to a new workbook and want to break the link to the original workbook.
    Each day I use the previous day's file and then Save-As using the date as part of the name. That causes the filename linked to to be different each day.
    How do I get Excel to break any or all links without regard to filename(s)?
    ================================================
    Sheets(Array("Daily Payroll Summary", "tw_daily")).Select
    Sheets("tw_daily").Activate
    Sheets(Array("Daily Payroll Summary", "tw_daily")).Copy
    ActiveWorkbook.SaveAs Filename:="CataPayrollemail_DailyPayroll.xls", _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False

    ' ActiveWorkbook.BreakLink Name:="CataPayrollPR_daily_2006-09-04.xls", _
    Type:=xlExcelLinks
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    =============================================

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Break Any/All Links in VBA (Excel 2003)

    You can use

    Dim i As Integer
    Dim varLinks As Variant
    varLinks = ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks)
    For i = UBound(varLinks) To LBound(varLinks) Step -1
    ActiveWorkbook.BreakLink varLinks(i), xlLinkTypeExcelLinks
    Next i

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Break Any/All Links in VBA (Excel 2003)

    Fantastic! Works perfect. Thank you Hans.

    So what is the doing?
    If I read this right, your code sets two variables and then counts the links in the active workbook, then breaks the numbers of links counting backwards?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Break Any/All Links in VBA (Excel 2003)

    ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks) is an array containing the Excel-type link sources present in the workbook. The variable varLinks is set to this array.
    The variable i is used to loop backwards through this array, and break each link in turn. The reason for looping backwards is that as you break links, you are changing the number of link sources. If you loop forward, the code may get confused.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Break Any/All Links in VBA (Excel 2003)

    Hans,
    I don't think there's any need to loop backwards in this instance since you have the link names stored in an array, which does not change, and they are being passed as names to the BreakLink method rather than as an index number. It is a useful technique to be aware of though, particularly when deleting rows etc.! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    FWIW
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Break Any/All Links in VBA (Excel 2003)

    You're correct - since the array is assigned at the beginning, it won't be affected by breaking links.

    It has become a habit always to loop backwards when deleting items, even when it isn't necessary <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Break Any/All Links in VBA (Excel 2003)

    It's a good habit - saves many hours of <img src=/S/confused.gif border=0 alt=confused width=15 height=20> <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> as to why items that should be deleted are still sitting there! (I recently had a reminder of this when moving items out of Outlook folders - because they weren't being deleted I forgot to loop backwards! Drove me <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15> for a while)
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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