如何使用VBA和Office脚本向Excel添加图像注释URL

如何使用VBA和Office脚本向Excel添加图像注释URL

作者

如何使用VBA和Office脚本向Excel添加图像注释URL

您是否厌倦了手动数据输入和无聊的电子表格？您是否想通过添加图像、注释、URL 和图表来吸引受众关注您的 Excel 报告？在这里了解使用 Excel 自动添加外部内容的最佳方法 Excel VBA 脚本和 Office 脚本。

Excel VBA 和 Office 脚本使 Excel 成为从基础到高级自动化的游乐场。此类自动化从 Excel 的前提扩展到 Power Automate，因此您可以运行一系列 Microsoft 应用程序，只需单击一下即可执行任务。

本 Excel 教程探索了无缝整合图像、深刻评论、动态 URL 等的艺术，让您的电子表格从单调乏味变得精彩纷呈。逐步了解如何运用这些工具的强大功能，不仅可以组织和可视化数据，还可以添加背景层和交互性。

推荐：如何管理Shopify节后退货

在 Excel 中添加图像、注释、URL 等的原因

  • 图像提供了一种快速、直观的方式来传达信息，使得数据解释变得更容易。
  • 图形和图表直观地解释数据中的模式和见解。
  • 注释可以直接在单元格内提供详细的解释、澄清或说明。
  • 评论还可以作为文档，为未来的用户保存见解和知识。
  • URL 方便访问外部资源，支持彻底的研究和引用。
  • 指向网站或内联网页面的超链接可创建具有外部数据集成的交互式报告。
  • 图像、链接和评论相结合，创建用于数据跟踪的综合仪表板。

如何在 Excel 中添加图像

这 插入图片来自 工具里面 插图 插入选项卡中的命令部分是向 Excel 工作表添加图像的标准方法。

但是，此过程是手动的，需要您多次单击才能将多张图片添加到 Excel 表。

假设您想快速添加多幅图像并自动执行该过程，那么您需要使用以下任一方法：

使用 Excel VBA

下面提到的 VBA 脚本可帮助您在 Excel 中创建自动化内容管理系统来管理博客、网站、图形设计业务等的图像。

只要您的 Excel 工作表上的图像名称与您电脑本地驱动器中的图像名称匹配，脚本就会自动将相关图像导入源数据的右侧。

例如，如果你在单元格范围内有服务、注册表和组策略等图像名称 A2:A4，代码将插入来自 B2:B4。

如何使用VBA和Office脚本向Excel添加图像注释URL
如何使用VBA和Office脚本向Excel添加图像注释URL
使用 VBA 在 Excel 中添加图像

以下是代码以及实现该代码所需遵循的步骤：

  1. 致电 Excel VBA 编辑器 通过按 Alt + F11。
  2. 点击 插入 按钮并选择 模块。
  3. 在新模块中，复制并粘贴以下 Excel VBA 脚本：
Sub AddImagesBasedOnTextWithDynamicRange()
    Dim ws As Worksheet
    Dim imgPath As String
    Dim img As Picture
    Dim cell As Range
    Dim destRange As Range
    
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet's name
    Set destRange = ws.Range("B2:B4") ' Change the destination range as needed
    
    For Each cell In ws.Range("A2:A4")
        imgPath = "D:\Images\" & cell.Value & ".jpg" ' Assuming images are in JPG format
        
        If Dir(imgPath)  "" Then ' Check if image file exists
            Set img = ws.Pictures.Insert(imgPath)
            With img
                .Left = destRange.Cells(cell.Row - 1, 1).Left
                .Top = destRange.Cells(cell.Row - 1, 1).Top
                .Width = destRange.Cells(cell.Row - 1, 1).Width
                .Height = destRange.Cells(cell.Row - 1, 1).Height
            End With
        End If
    Next cell
End Sub
  1. 点击 节省 按钮并选择 是的 在随后弹出的窗口中。
  2. 关上 Excel VBA 编辑器。
运行 VBA 宏
运行 VBA 宏

现在，您需要运行代码。操作如下：

  1. 按 Alt + F8 调用 宏 对话框。
  2. 点击 添加基于文本的图像和动态范围 宏。
  3. 打 跑步 执行。
  4. Excel 将通过匹配输入数据单元格区域上的名称将图像导入并粘贴到各自的单元格中 A2:A4 以及本地驱动器中的图像文件名。

以下是一些快速提示，可帮助您修改代码以使其适合您。您还会在 VBA 脚本中找到重要的注释。

  • "Sheet1" 如果您的 Excel 工作表的名称不同，则必须进行更改。
  • "A2:A4" 表示输入数据集。此单元格范围内的值告诉 Excel 要导入哪些图像。因此，请根据您自己的工作表进行更改。
  • "B2:B4" 表示导入图像的目标位置。因此，请根据您的选择修改单元格范围。
  • 本教程中导入的图像均位于 JPG 格式。所以我用了 ".jpg" 脚本中的代码。如果您的文件位于 巴布亚新几内亚 格式，使用 ".png" 反而。

推荐：如何更新Nintendo Switch Joy-Cons

使用 Office 脚本

Office Scripts 是一种比 Excel VBA 更先进的自动化工具。您可以将 Office Scripts 代码与 Power Automate 链接起来，以将 Excel 工作表数据传送到其他应用程序。

使用 Office 脚本在 Excel 中添加图像
使用 Office 脚本在 Excel 中添加图像

以下是在这种情况下使用 Office 脚本的代码和步骤：

  1. 点击 自动化 标签。
  2. 点击 新脚本 在里面 脚本工具 堵塞。
  3. 在里面 代码编辑器，复制并粘贴代码：
async function main(workbook: ExcelScript.Workbook) {
    // Fetch the image from a URL.
    const link = "https://raw.githubusercontent.com/OfficeDev/office-scripts-docs/master/docs/images/git-octocat.png";
    const response = await fetch(link);

    // Store the response as an ArrayBuffer, since it is a raw image file.
    const data = await response.arrayBuffer();

    // Convert the image data into a base64-encoded string.
    const image = convertToBase64(data);

    // Add the image to a worksheet.
    workbook.getWorksheet("WebSheet").addImage(image);
}

/**
 * Converts an ArrayBuffer containing a .png image into a base64-encoded string.
 */
function convertToBase64(input: ArrayBuffer) {
    const uInt8Array = new Uint8Array(input);
    const count = uInt8Array.length;

    // Allocate the necessary space up front.
    const charCodeArray = new Array(count) as string[];

    // Convert every entry in the array to a character.
    for (let i = count; i >= 0; i--) {
        charCodeArray[i] = String.fromCharCode(uInt8Array[i]);
    }

    // Convert the characters to base64.
    const base64 = btoa(charCodeArray.join(''));
    return base64;
}
  1. 点击 保存脚本 按钮。
  2. 点击 跑步 从网站源在 Excel 中添加图像。

在上面的代码中，替换代码元素 "https://raw..." 如果需要插入另一张图片，请使用源 URL。

如何在 Excel 中添加注释

要在 Excel 单元格或单元格区域输入注释，您可能正在使用 插入评论 命令 评论 阻止 插入 选项卡。但是，您必须学习更直观、更省时的方法来提高您的 Excel 水平。

以下是在 Excel 工作表上自动执行评论过程的方法：

使用 Excel VBA

以下 Excel VBA 代码将显示一个弹出消息框，用于在“通过/失败”列中输入每个学生的评论及其分数。您可以将代码重新用于任何其他情况，例如在评估之前向员工分享反馈、向承包商分享反馈等。

找到以下您可以使用的代码：

Excel VBA 代码自动注释
Excel VBA 代码自动注释
Sub AddCommentsWithPassFail()
    Dim ws As Worksheet
    Dim namesRange As Range
    Dim scoresRange As Range
    Dim passFailRange As Range
    Dim cell As Range
    Dim comment As String
    
    Set ws = ThisWorkbook.Sheets("Sheet3") ' Change to your sheet's name
    
    ' Set input cell ranges for names, scores, and pass/fail status
    Set namesRange = ws.Range("A2:A6")
    Set scoresRange = ws.Range("B2:B6")
    Set passFailRange = ws.Range("C2:C6")
    
    For Each cell In namesRange
        ' Display message box with student name, score, and pass/fail status
        comment = InputBox("Enter comments for " & cell.Value & " (Score: " & scoresRange.Cells(cell.Row - namesRange.Cells(1, 1).Row + 1, 1).Value & ", Pass/Fail: " & passFailRange.Cells(cell.Row - passFailRange.Cells(1, 1).Row + 1, 1).Value & "):")
        
        ' Write comment as Excel cell comment in the respective pass/fail cell
        With passFailRange.Cells(cell.Row - passFailRange.Cells(1, 1).Row + 1, 1)
            On Error Resume Next
            .comment.Delete ' Delete existing comment if any
            On Error GoTo 0
            If comment  "" Then
                .AddComment comment
            End If
        End With
    Next cell
End Sub

在上面的 VBA 脚本中， "C2:C6" 是注释的目标。因此，根据您自己的工作表自定义脚本中的此单元格范围。此外，您必须修改 "Sheet3" 根据您的工作簿指定合适的工作表名称。

单元格范围 "A2:A6" 和 "B2:B6" 代码中存在当前数据分析的附加数据。您可以根据工作簿重新利用这些单元格区域。

要执行该脚本，请按照本文前面概述的步骤进行操作。

基本上，运行上述 Excel VBA 脚本时您将遇到以下情况：

运行后，您会看到上述弹出窗口，以文本格式输入学生姓名、分数以及通过/失败状态的评论。

评论视图
评论视图

输入评论并按 好的然后，下一个弹出窗口会出现，供下一个学生使用。这样，只要有数据，弹出窗口就会一直出现 列A 和 乙。

笔记： 不要在同一数据集上重复运行该代码。VBA 脚本将重写或删除旧注释。此外，在使用此 VBA 脚本之前，请备份您的工作簿。

使用 Office 脚本

以下是通过添加注释的代码 C2:C6 Excel 中的单元格地址：

使用 Office 脚本添加自动评论
使用 Office 脚本添加自动评论
function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	workbook.addComment(selectedSheet.getRange("C2"), "OK");
	workbook.addComment(selectedSheet.getRange("C3"), "OK");
	workbook.addComment(selectedSheet.getRange("C4"), "OK");
	workbook.addComment(selectedSheet.getRange("C5"), "OK");
	workbook.addComment(selectedSheet.getRange("C6"), "Feedback given");
}

要自定义评论文本，请替换 "OK" 用双引号括住您自己的评论。

如果你有超出 C6，然后只需在上面的脚本中创建一个新的代码行，如下所示：

workbook.addComment(selectedSheet.getRange("C7"), "Feedback given");

推荐：如何在Ubuntu22.04/20.04上安装wget

如何在 Excel 中添加超链接

在 Excel 中向值、字符串或单元格添加超链接或 URL 的常用方法是通过 插入链接 或者 添加超链接 命令 链接 阻止 插入 标签。

如果您需要添加来自另一个数据集的数千个单元格的链接，则按照上述方法需要几天的时间。我为您提供了一个自动且直观的解决方案。继续阅读！

使用 Excel VBA

下面提供的 Excel VBA 脚本将自动将源数据集的 URL 或超链接链接到目标数据集。

Excel VBA 脚本自动实现超链接
Excel VBA 脚本自动实现超链接
Sub hyperlinkcells()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rng As Range
    Dim cell As Range
    Dim hyperlinkText As String
    
    Set ws = ThisWorkbook.Sheets("Sheet4") ' Change to your sheet's name
    Set rng = ws.Range("B2:B" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row)
    
    For Each cell In rng
        lastRow = cell.Row
        hyperlinkText = cell.Value
        cell.Value = ""
        
        If ws.Hyperlinks.Count

上述脚本将超链接 B栏， 从 B2，直到最后一行包含值。代码将获取 网址 从 C 栏。此外，超链接将在 B2 和 C2， B3 和 C3， 等等。

超链接自动化的原始数据
超链接自动化的原始数据

上面是我用于超链接的数据集的图像 B栏 值 C 栏网址。

使用 VBA 的超链接数据
使用 VBA 的超链接数据

现在，将 VBA 脚本应用到原始数据后，发现上图如此。

如何在 Excel 中添加图表

作为一名研究生和从事数据分析师的专业人士，您一定在 Excel 工作表中添加了数千张图表。

添加图表的常用方法是选择 Excel 工作表上的给定数据，然后单击 插入。在那里，你去 图表 块从一长串图表（如二维柱形图、二维折线图、二维饼图、树形图等）中添加图表。

如果您获得一个包含数千个工作表（其中包含用于准备图表的表格数据）的 Excel 工作簿，则手动过程将不会是高效的举措。

相反，使用这些方法来炫耀你的 Excel 技能并提高效率：

使用 Excel VBA

以下 VBA 代码将循环遍历所有工作表，要求您添加图表数据集，并通过键入其名称来选择图表类型。然后，按“确定”立即填充图表。

Sub AddChartsToWorksheetsWithCancel()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim chartType As String
    Dim dataRange As Range
    Dim chartRange As Range
    
    Set wb = ThisWorkbook
    
    ' Loop through each worksheet
    For Each ws In wb.Worksheets
        ' Prompt user to select data range
        On Error Resume Next
        Set dataRange = Application.InputBox("Select data range for chart in " & ws.Name, Type:=8)
        On Error GoTo 0
        
        If Not dataRange Is Nothing Then
            ' Prompt user to select chart type
            chartType = Application.InputBox("Select chart type for chart in " & ws.Name & " (Column, Pie, Line, etc.):")
            
            ' Create chart
            Set chartRange = dataRange.Offset(1).Resize(dataRange.Rows.Count - 1)
            CreateChart ws, chartRange, chartType
        End If
    Next ws
End Sub

Sub CreateChart(ws As Worksheet, chartRange As Range, chartType As String)
    Dim cht As ChartObject
    Dim chtTop As Double
    Dim chtLeft As Double
    Dim chtWidth As Double
    Dim chtHeight As Double
    
    ' Determine chart position and size
    chtTop = chartRange.Cells(1, 1).Top
    chtLeft = chartRange.Cells(1, 1).Left
    chtWidth = chartRange.Width
    chtHeight = chartRange.Height
    
    ' Add chart to worksheet
    Set cht = ws.ChartObjects.Add(chtLeft, chtTop, chtWidth, chtHeight)
    
    ' Set chart type
    cht.Chart.chartType = GetChartType(chartType)
    
    ' Set chart data source
    cht.Chart.SetSourceData chartRange
End Sub

Function GetChartType(chartType As String) As XlChartType
    Select Case LCase(chartType)
        Case "column"
            GetChartType = xlColumnClustered
        Case "pie"
            GetChartType = xlPie
        Case "line"
            GetChartType = xlLine
        Case Else
            GetChartType = xlColumnClustered
    End Select
End Function

代码将从 工作表1 并继续，直到工作簿中剩下工作表。您也可以取消一张工作表并转到下一张工作表。

选择图表数据集
选择图表数据集

上图显示了 Excel 如何收集您的图表数据。

键入图表名称
键入图表名称

然后，Excel 还会要求您选择图表类型。我在上图中输入了我的图表偏好。

最后，Excel 会自动创建一个美观且专业的图表。

使用 Office 脚本

使用 Office 脚本添加图表
使用 Office 脚本添加图表

以下 Office Scripts 代码将从数据范围创建柱形图 A1:B9：

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Insert chart on sheet selectedSheet
	let chart_1 = selectedSheet.addChart(ExcelScript.ChartType.columnClustered, selectedSheet.getRange("A5:B9"));
}

要自定义上述代码，您可以执行以下操作：

  • 通过更改单元格引用来替换图表的数据范围 "A5:B9" 根据您自己的工作表。
  • 如果需要不同的图表类型，请替换代码元素 columnClustered 使用该图表类型名称。

结论

现在您知道如何使用 Excel VBA 或 Office 脚本自动向 Excel 工作簿添加其他内容。

您学习了使用脚本添加图像、URL、注释和图表的快捷方法。在您自己的工作表中使用这些方法，并在下方评论您使用这些 Excel 技能的经验

推荐：如何在谷歌浏览器中添加Gmail按钮

Previous

在Microsoft Excel中裁剪图像的6种方法

Next

在Microsoft Excel中计算年龄的4种方法

相关文章

在Microsoft Excel中添加时间的4种方法

在Microsoft Excel中添加时间的4种方法

在Microsoft Excel中查找循环引用的7种方法

在Microsoft Excel中查找循环引用的7种方法

将CSV转换为Excel的8种简便方法

将CSV转换为Excel的8种简便方法

在Microsoft Excel中插入分页符的8种方法

在Microsoft Excel中插入分页符的8种方法

发表评论