|
下面是个宏命令，在多sheet excel文件里用来自动形成一个“目录”sheet，“目录”sheet里是对各个sheet 的链接。但这个宏命令用的是绝对路径，目录生成后，一旦改变文件位置（比如说，发送到别人电脑上），目录里的链接便失去作用。怎么改，可以生成相对路径的链接呢，最好把里面那个超级链接函数讲清楚，谢谢
Sub 提取目录()
Dim ws As Worksheet
Dim i As Integer
i = 1
ThisWorkbook.Worksheets.Add.Name = "目录"
Worksheets("目录").Cells(i + 1, 1).Value = "序号"
Worksheets("目录").Cells(i + 1, 2).Value = "名称"
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "目录" Then
Worksheets("目录").Cells(i + 1, 1).Value = i - 1
Worksheets("目录").Cells(i + 1, 2).Value = Worksheets(i).Name
Worksheets("目录").Hyperlinks.Add Cells(i + 1, 2), _
ThisWorkbook.Path & "\" & ThisWorkbook.Name, _
Worksheets(i).Name & "!A1", _
"单击跳转到" & Worksheets(i).Name, _
Worksheets(i).Name
End If
i = i + 1
Next
i = ThisWorkbook.Sheets.Count - 1
Worksheets("目录").Range("A3:A" & i + 2).Select
Selection.HorizontalAlignment = xlCenter
Worksheets("目录").Range("B3:B" & i + 2).Select
Selection.HorizontalAlignment = xlGeneral
Selection.Font.ColorIndex = 5
Selection.Font.Underline = xlUnderlineStyleNone
Worksheets("目录").Rows("2:2").Select
Selection.Font.Bold = True
Selection.HorizontalAlignment = xlCenter
End Sub  采纳率：40%12级2013.11.16 Excel中绝对和相对引用使用F4键进行切换 |
|
|
来自： leexingyuan5 > 《word-excel相关》
