4zhvml8 发表于 2024-7-31 11:20:01

Excel VBA：一键删除所有外边链接


    <h1 style="color: black; text-align: left; margin-bottom: 10px;">问题</h1>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">Excel工作簿内含有一个或多个<span style="color: black;">外边</span>链接，需要清除（如下图）。每次打开工作簿会被提示<span style="color: black;">是不是</span>更新，<span style="color: black;">况且</span><span style="color: black;">外边</span>数据<span style="color: black;">亦</span>占用空间。能<span style="color: black;">不可</span>一键删除所有<span style="color: black;">外边</span>链接？</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">当然<span style="color: black;">能够</span>。先看下效果。</p>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">处理</span><span style="color: black;">方法</span></h1>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-axegupay5k/3216583899dd42a5a8698dadc9e755b5~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1722997623&amp;x-signature=p6TPwQPbXH9UuteMyGP0zqqZYYY%3D" style="width: 50%; margin-bottom: 20px;">
      <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">↑ 存在<span style="color: black;">外边</span>链接</p>
    </div>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/630efcc7cd414aae819d470c940c8753~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1722997623&amp;x-signature=bgrHoHWPeyQaOJ%2Bw65p80PTX%2B1w%3D" style="width: 50%; margin-bottom: 20px;">
      <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">↑ <span style="color: black;">外边</span>链接<span style="color: black;">名单</span></p>
    </div>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/bd8968ca3465450ab0ed91c559b5d49c~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1722997623&amp;x-signature=ij42T1k%2FSKWAlcNkw0XoittpIdo%3D" style="width: 50%; margin-bottom: 20px;">
      <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">↑ 新建VBA宏程序，一键批量删除<span style="color: black;">外边</span>链接</p>
    </div>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/614cfffad1e44f55ad17677e93f3985e~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1722997623&amp;x-signature=8auumBVvfRLFvKF%2FDQs6kiDglMA%3D" style="width: 50%; margin-bottom: 20px;">
      <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">↑ 点击按钮运行VBA程序后，<span style="color: black;">检测</span>结果：OK</p>
    </div>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">VBA程序源代码：</h1>Sub breakAllLinks()
    <span style="color: black;">Call</span> breakLinksByType(xlLinkTypeExcelLinks)
    <span style="color: black;">Call</span> breakLinksByType(xlLinkTypeOLELinks)
    <span style="color: black;">End</span> Sub
    Sub breakLinksByType(vType <span style="color: black;">As</span>XlLinkType)<span style="color: black;">On</span> <span style="color: black;">Error</span> <span style="color: black;">Resume</span> <span style="color: black;">Next</span>
    Err.Clear
    aLinks = ActiveWorkbook.LinkSources(vType)
    <span style="color: black;">For</span> i = <span style="color: black;">1</span> <span style="color: black;">To</span> UBound(aLinks)
    Debug.Print <span style="color: black;">"Link "</span> &amp; i &amp; <span style="color: black;">" : "</span>&amp; sName;
    ActiveWorkbook.BreakLink _
    Name:=aLinks(i), _
    Type:=vType
    If Err.Number = 0 Then
    Debug.Print " has been deleted!"
    iCnt = iCnt + 1<span style="color: black;">End</span> <span style="color: black;">If</span>
    <span style="color: black;">Next</span>
    <span style="color: black;">End</span> Sub<h1 style="color: black; text-align: left; margin-bottom: 10px;">干货</h1>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">Windows下的Excel<span style="color: black;">外边</span>链接有2种：ExcelLinks 和 OLELinks</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">运用</span>2个<span style="color: black;">办法</span>：</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">1) 获取链接数组:</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">Workbook.LinkSources(xlLinkType)</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">2) 断开链接:</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">Workbook.BreakLink(aLinks(i),xlLinkType)</p>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">PS</h1>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">倘若</span>需要批量处理多个工作簿，或需要插件随时在任意工作簿一键运行的话，<span style="color: black;">能够</span>私信我。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">OK. 分享完毕。感谢您的关注、点赞、<span style="color: black;">保藏</span>与点评。下期见~</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><a style="color: black;">#Excel#</a><a style="color: black;">#excel#</a><a style="color: black;">#Excel技巧#</a><a style="color: black;">#职场excel小技巧#</a><a style="color: black;">#VBA#</a><a style="color: black;">#头条创作挑战赛#</a>​</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">《Excel VBA 从入门到封神系列之进阶篇》：Excel工作簿大瘦身：一键删除<span style="color: black;">外边</span>链接</p>




听听海 发表于 2024-8-22 12:06:35

软文发布平台 http://www.fok120.com/

m5k1umn 发表于 2024-10-4 05:59:25

楼主的文章非常有意义，提升了我的知识水平。
页: [1]
查看完整版本: Excel VBA：一键删除所有外边链接