标题: 代码实现自动目录功能,但是有点小毛病搞不定,求救 [打印本页] 作者: fannky 时间: 2008-11-10 16:35 标题: 代码实现自动目录功能,但是有点小毛病搞不定,求救 Private Sub Workbook_Open()
Dim S, I As Integer
S = Me.Worksheets.Count
Sheet1.Cells(1, 1) = "序号"
Sheet1.Cells(1, 2) = "名称"
For I = 1 To S - 1
With Worksheets(1)
.Hyperlinks.Add .Range("B" & I + 1), ThisWorkbook.Sheets(I).Name '///这里出问题
End With
Sheet1.Cells(I + 1, 1) = I
Sheet1.Cells(I + 1, 2) = Me.Sheets(I).Name
Next I
End Sub
代码如上,我超链接到的位置是当前工作簿中的特定表,这个超链接位置该怎样指定呢?作者: ui 时间: 2008-11-10 17:24
Hyperlinks 加上去 不行吗作者: fannky 时间: 2008-11-10 17:38
不行啊作者: fannky 时间: 2008-11-11 14:56
Private Sub Workbook_Open()
Dim S, I As Integer
S = Me.Worksheets.Count
Sheet1.Activate '需加这句,否则目录将会出现在关闭时作激活的工作表上
Sheet1.Cells(1, 1) = "序号"
Sheet1.Cells(1, 2) = "名称"
For I = 1 To S - 1
Range("B" & I + 1).Hyperlinks.Add Anchor:=Range("B" & I + 1), Address:="", SubAddress:=ThisWorkbook.Sheets(I).Name + "!A1"
Sheet1.Cells(I + 1, 1) = I
Sheet1.Cells(I + 1, 2) = Me.Sheets(I).Name
Next I
End Sub
=====================================================
以上代码可以解决自动生成目录并链接到对应的Sheet
但是当工作表的名字中有特殊字符的时候其对应的超链接就会出错,那位大虾可以帮我解决啊?
[ 本帖最后由 fannky 于 2008-11-13 10:35 编辑 ]作者: fannky 时间: 2008-11-13 10:36
Private Sub Workbook_Open()
Dim S, I As Integer
S = Me.Worksheets.Count
Sheet1.Activate '需加这句,否则目录将会出现在关闭时作激活的工作表上
Sheet1.Cells(1, 1) = "序号"
Sheet1.Cells(1, 2) = "名称"
For I = 1 To S - 1
Range("B" & I + 1).Hyperlinks.Add Anchor:=Range("B" & I + 1), Address:="", SubAddress:=ThisWorkbook.Sheets(I).Name + "!A1"
Sheet1.Cells(I + 1, 1) = I
Sheet1.Cells(I + 1, 2) = Me.Sheets(I).Name
Next I
End Sub
=====================================================
以上代码可以解决自动生成目录并链接到对应的Sheet
但是当工作表的名字中有特殊字符的时候其对应的超链接就会出错,那位大虾可以帮我解决啊?