多层级目录
时间: 2023-09-07 20:13:32 浏览: 46
如果您需要创建多层级目录,可以使用下面这个修改版的代码:
```
Sub Create_Index()
Dim ws As Worksheet
Dim I As Integer, J As Integer, K As Integer
I = 1
ActiveSheet.Cells.Clear
ActiveSheet.Range("A1").Value = "Excel目录"
ActiveSheet.Range("A1").Font.Bold = True
For Each ws In Worksheets
If ws.Visible Then
I = I + 1
ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Cells(I, 1), Address:="", _
SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:=ws.Name
If ws.Range("A1") <> "" Then
J = 2
K = InStr(ws.Range("A1"), "|")
Do While K > 0
I = I + 1
ActiveSheet.Cells(I, J).Value = Mid(ws.Range("A1"), 1, K - 1)
ActiveSheet.Cells(I, J).Font.Bold = True
ws.Range("A1") = Mid(ws.Range("A1"), K + 1)
K = InStr(ws.Range("A1"), "|")
J = J + 1
Loop
I = I + 1
ActiveSheet.Cells(I, J).Value = ws.Range("A1")
ActiveSheet.Cells(I, J).Font.Bold = True
End If
End If
Next ws
End Sub
```
这个代码会在每个工作表的A1单元格中查找多个以竖线“|”分隔的层级目录,并将其在目录表格中以多列的形式显示。如果没有多层级目录,就只会显示单列目录。
希望这个修改版的代码对您有所帮助!