VBA编程,批量修改超链接
'========================================================= '定义全局变量 '========================================================= Const nStartRow As Integer = 5 '开始修改的行数 Const nLinkColumn As Integer = 4 '要修改超链接的单元格的列数 Const strCIHome As String = "CI_HOME" '要修改的字符串 Const nDefaultRow As Integer = 2 '用户自定义路径输入框所在行数 Const nDefaultColumn As Integer = 2 '用户自定义路径输入框所在列数 '========================================================= '批量修改超链接 '把连接中的"CI_HOME"修改成用户自定义的路径 '========================================================= Private Sub SetHyperLinkButton_Click() Dim nRow As Integer '行数 Dim HyperLinkRange As Range '处理的单元格 Dim strUserSetPath As String '用户自定义的路径 Dim strHyperLink As String '超链接字串 strUserSetPath = ActiveSheet.Cells(nDefaultRow, nDefaultColumn).Value '用户自定义路径输入框所在单元格 '遍历所有行数 For nRow = nStartRow To ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row Set HyperLinkRange = ActiveSheet.Cells(nRow, nLinkColumn) '如果当前单元格有链接则修改之 If HyperLinkRange.Hyperlinks.Count > 0 Then strHyperLink = HyperLinkRange.Hyperlinks(1).Address strHyperLink = Replace(strHyperLink, strCIHome, strUserSetPath) ActiveSheet.Hyperlinks.Add HyperLinkRange, strHyperLink End If Next End Sub Private Sub AddHylinkButton_Click() End Sub