Question

excel绝对路径引用怎么改成相对路径

下面是个宏命令，在多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

Answer 1

Excel中绝对和相对引用使用F4键进行切换

Excel版本参考：2010

1、B2单元格输入=，选择a2单元格，按下F4键切换引用

2、行列前面都有$为绝对引用，拖动，不改变引用单元格地址

3、C2直接输入=A2，拖动公式改变

Answer 2

。。。

想了一下，您的这个代码是一次性的。。。即意味着只在你本机上运行宏的时候运行一次，后面就不会运行了。那么超链接保存下来的信息就会被固定为最初的地址。

修改了一下你的代码：（主要部分注释一下便于大家的理解）

Sub Workbook_Open() '使用这个事件可以保证每次文档被打开的时候运行本宏。
Dim ws As Worksheet
Dim i As Integer
Dim ex As Boolean
i = 1
ex = False '是否已经存在“目录”表
For Each ws In ThisWorkbook.Worksheets
If ws.Name = "目录" Then
ex = True
Exit For
End If
Next
If ex = False Then '如果不存在，则创建之。
ThisWorkbook.Worksheets.Add
Worksheets(i).Name = "目录"
End If
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

'其中iif语句为判断文档是否在盘符根目录下，即是否在 D:\ 之类的下面
Worksheets("目录").Hyperlinks.Add Cells(i + 1, 2), _
ThisWorkbook.Path & IIf(Right(ThisWorkbook.Path, 1) = "\", "", "\") & 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).HorizontalAlignment = xlCenter '这里直接使用Range的HorizontalAlignment相关属性是为了避免当前表不是“目录”时出错。下同。
Worksheets("目录").Range("B3:B" & i + 2).HorizontalAlignment = xlGeneral
Worksheets("目录").Range("B3:B" & i + 2).Font.ColorIndex = 5
Worksheets("目录").Range("B3:B" & i + 2).Font.Underline = xlUnderlineStyleNone
Worksheets("目录").Rows("2:2").Font.Bold = True
Worksheets("目录").Rows("2:2").HorizontalAlignment = xlCenter

End Sub

ps：使用此方法，需要在Excel开启VB宏，由此会带来很多安全隐患。根据微软Office2010的设置，文件的扩展名需要变为xlsm而不是xlsx，我不知道2003和2007是否也需要这么改。

如果不是特别需要，建议直接使用Excel的编辑超链接方法，里面有个链接到本文档中的位置选项。

追问

"直接使用Excel的编辑超链接方法"，主要是我的很多excel文件里，都是有100个以上的sheet，用不了这个方法。谢谢哈

Answer 3

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
i = i + 1
Worksheets("目录").Cells(i + 1, 1).Value = i - 1
Worksheets("目录").Cells(i + 1, 2).Value = ws.Name
Worksheets("目录").Hyperlinks.Add Cells(i + 1, 2), _
"", _
ws.Name & "!A1", _
"单击跳转到" & ws.Name, _
ws.Name
End If

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

'Hyperlinks的说明，摘自Office帮助:
'Hyperlinks.Add (Anchor, Address, SubAddress, ScreenTip, TextToDisplay)
'Anchor 必选 Object 超链接的位置。可为 Range 或 Shape 对象。
'Address 必选 String 超链接的地址。
'SubAddress 可选 Variant 超链接的子地址。
'ScreenTip 可选 Variant 当鼠标指针停留在超链接上时所显示的屏幕提示。
'TextToDisplay 可选 Variant 要显示的超链接的文本。

'要想超链接在本文件内：将参数Address设为空""

追问

好的，谢谢，晚上有空就马上试一下^_^

Answer 4

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("目录").Cells(i + 1, 2).FormulaR1C1 = "=HYPERLINK(""#" & Worksheets(i).Name & "!A1"",""点击跳转"")"

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

Answer 5

太高深了，只知道在公式里选择单位格编号按F4键。。。