主题：链接更.新

 

打开excel，循环每一个单元格，一个一个地置换，看你13楼代码。

On Error Resume Next Dim link As Hyperlink, Spcell, Cell As Range For Each sht In Sheets    For Each link In sht.Hyperlinks       link.Delete    Next link    Set Spcell = sht.Cells.SpecialCells(xlCellTypeFormulas)    If Err.Number = 1004 Then GoTo Err    For Each Cell In Spcell          If Cell.Formula Like "*201702*" Then Cell.Value = “201703”    Next Err: Next Set Spcell = Nothing End Sub 会造成更新的错误提示

无语，你问过啊ChangeLink

 

Dim App As New MSExcel.Application
Dim Wb As MSExcel.WorkBook = App.WorkBooks.open("d:\test.xls")
Dim Ws As MSExcel.WorkSheet = Wb.WorkSheets(1)

Dim path As String = ws.cells(2,2).Formula.split("]")(0).replace("=", "").replace("[","").replace("'", "")

wb.ChangeLink(Name:= _
        path, NewName:= _
        "C:\Documents and Settings\Administrator\My Documents\Book1.xls", Type:= _
        MSExcel.XlLinkType.xlLinkTypeExcelLinks)

App.Visible = True

 

 

http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&id=55943&authorid=0&page=0&star=5

 

如何对工作簿的所有工作表相同链接同时替换？

循环多个sheet，逐个处理

=IF(MONTH($B$2)=1,E28,E28-'E:\快盘\excel2017\[汇总单表201701.xls]分户快报'!E28)

="'E:\快盘\excel2017\[集团汇总201702.xls]分户快报'!Z"&ROWS(5)

 

激活公式，有多个表，就循环多个表啊。

 

Dim App As New MSExcel.Application
Dim Wb As MSExcel.WorkBook = App.WorkBooks.Open("d:\test.xls")
Dim Ws As MSExcel.WorkSheet = Wb.WorkSheets(1)
Dim Rg As MSExcel.Range = Ws.UsedRange
rg.Formula = rg.Formula
app.visible = True
'app.quit

老师，链接的公式是不行的，还有其他什么办法呢？

循环所有的链接，然后修改

 

参考vba的代码，如果需要改成foxtable，对应的修改。

 

Dim aLinks
aLinks = ActiveWorkbook.LinkSources(xlOLELinks)
If Not IsEmpty(aLinks) Then
    For i = 1 To UBound(aLinks)
        MsgBox aLinks(i)
    Next i
End If
ActiveWorkbook.ChangeLink aLinks(1), "Excel.Sheet.12|D:\test.xlsx!'", xlLinkTypeOLELinks

 

自己学一下 https://msdn.microsoft.com/zh-cn/library/office/ff839916