excel - 使用 excel 宏更新超链接
问题描述
在此处输入图像描述我正在尝试为 Excel 工作表上的所有嵌入式超链接添加扩展名。我一次做一个单元格记录了一个宏,但效率不高。有人可以帮我简化宏,以便它知道查看所有超链接、打开并在现有超链接的末尾插入附加信息。
Sub Macro5()
'
' Macro5 Macro
' test
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Range("H1").Select
ActiveCell.FormulaR1C1 = "?u=76208058&auth=true"
Range("C2").Select
Selection.Hyperlinks(1).Address = _
"https://www.linkedin.com/learning/teaching-techniques-classroom-management?u=76208058&auth=true"
Range("C3").Select
Selection.Hyperlinks(1).Address = _
"https://www.linkedin.com/learning/learning-how-to-increase-learner-engagement?u=76208058&auth=true"
Range("C4").Select
Selection.Hyperlinks(1).Address = _
"https://www.linkedin.com/learning/teaching-with-technology?u=76208058&auth=true"
End Sub
解决方案
将字符串添加到超链接
- 第一个代码更改指定工作表中所有单元格的超链接地址,而第二个代码仅更改工作表指定列中的超链接地址。
- 适当调整常量部分中的值。
- 该
If
语句检查当前超链接是否已被修改。
编码
Option Explicit
' For the whole sheet:
Sub addTailSheet()
' Keyboard Shortcut: Ctrl+Shift+H
Const SheetName As String = "Sheet1"
Const TailCell As String = "H1"
Dim ws As Worksheet
Dim hyp As Hyperlink
Dim Tail As String
Set ws = ThisWorkbook.Worksheets(SheetName)
With ws
Tail = .Range(TailCell).Value
For Each hyp In .Hyperlinks
If Right(hyp.Address, Len(Tail)) <> Tail Then
hyp.Address = hyp.Address & Tail
End If
Next
End With
MsgBox "Hyperlinks modified."
End Sub
' For a column:
Sub addTailColumn()
' Keyboard Shortcut: Ctrl+Shift+H
Const SheetName As String = "Sheet1"
Const TailCell As String = "H1"
Const TailColumn As Variant = "C" ' e.g. "C" or 3
Dim ws As Worksheet
Dim hyp As Hyperlink
Dim Tail As String
Set ws = ThisWorkbook.Worksheets(SheetName)
With ws.Columns(TailColumn)
Tail = .Parent.Range(TailCell).Value
For Each hyp In .Hyperlinks
If Right(hyp.Address, Len(Tail)) <> Tail Then
hyp.Address = hyp.Address & Tail
End If
Next
End With
MsgBox "Hyperlinks modified."
End Sub
推荐阅读
- javascript - 根据 Angular 中的变量设置 HTML 输入的数量
- java - Firebase admin sdk java 性能问题
- c++ - 工会成员的析构函数似乎被自动调用
- python - 无法使用功能工具处理数据
- java - 没有为实体指定标识符:XYZ (AnnotationException)
- spring-boot - Sleuth TraceId 在 Spring 状态机中丢失
- bash - 检查软件包是否安装在 rpm 规范文件中的正确方法
- c++ - 为什么输入张量包含的维度比输入的形状多?
- c++ - 错误:从“int”到“节点”的无效转换
*' [-fpermissive] - javascript - 如何在 React Native 中的对象中获取特定值?