以下在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执行