Excel:自动生成各表单的超链接清单

以下在Excel 2007下操作：

0. 首先将第一个Sheet重命名为“目录”,代码中会引用到。将文件另存为 .xlsm 文件（支持宏）

1. 打开"Excel选项"，在“常用”页面下找到“在功能区显示开发工具选项卡”，勾选上，确定。

2. 在菜单最右边会出现“开发工具”菜单

3.点击“Visual Basic”进入VBA编辑界面

4. 在左侧选择“Sheet1（目录）”，在右侧代码编辑界面粘贴以下内容：

mulu子例程来自网上资料，可以修改适合自己的需求：

Sub mulu()
On Error GoTo Tuichu
Dim i As Integer
Dim shtcount As Integer
Dim SelectionCell As Range
shtcount = Worksheets.Count
If shtcount = 0 Or shtcount = 1 Then Exit Sub
Application.ScreenUpdating = False
For i = 1 To shtcount
If Sheets(i).Name = "目录" Then
Sheets("目录").Move before:=Sheets(1)
End If
Next i
If Sheets(1).Name <> "目录" Then
shtcount = shtcount + 1
Sheets(1).Select
Sheets.Add
Sheets(1).Name = "目录"
End If
' 总共有多少个sheet
Sheets(1).Cells(1, 1) = shtcount
Sheets("目录").Select
Columns("B:B").Delete Shift:=xlToLeft
Application.StatusBar = "正在生成目录…………请等待！"
For i = 2 To shtcount
ActiveSheet.Hyperlinks.Add Anchor:=Worksheets("目录").Cells(i, 2), Address:="", SubAddress:= _
"'" & Sheets(i).Name & "'!R1C1", TextToDisplay:=Sheets(i).Name
Worksheets("目录").Cells(i, 3) = Sheets(i).Cells(3, 1) '描述内容

'隔行背景色不同
'If (i Mod 2) = 1 Then
   'Worksheets("目录").Cells(i, 3).Interior.ColorIndex = 60
   'Worksheets("目录").Cells(i, 2).Interior.ColorIndex = 60
'End If

Next
Sheets("目录").Select
Columns("B:B").AutoFit
Cells(1, 2) = "第三方类库清单"
Cells(1, 3) = "描述"
Set SelectionCell = Worksheets("目录").Range("B1")
With SelectionCell
.HorizontalAlignment = xlDistributed
.VerticalAlignment = xlCenter
.AddIndent = True
.Font.Size = 14
End With
Application.StatusBar = False
Application.ScreenUpdating = True

Call ListFormat

Tuichu:
End Sub

Sub ListFormat()
'
' ListFormat Macro
' 格式化列表
' 由宏录制生成


'
    Columns("B:C").Select
    With Selection.Font
        .Name = "Comic Sans MS"
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Columns("B:C").EntireColumn.AutoFit
    Selection.ColumnWidth = 120.38
    Columns("C:C").Select
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Range("C1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("B1:C1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.Font
        .Name = "微软雅黑"
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection.Font
        .Name = "微软雅黑"
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("B1:C1").Select
    With Selection.Font
        .Name = "微软雅黑"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("C13").Select
End Sub

5. 保存，点击运行图标或按F5执行