答案1
假设 A 列包含国家名称,B 列包含国家代码。让我们使用 C 列作为旗帜的超链接。旗帜将从旗帜百科在 中C1
输入以下公式:
="https://flagpedia.net/data/flags/w702/" & LOWER(B1) & ".webp"
拖动以填充其他单元格。按ALT+F11打开 VBA 编辑器,在左侧插入一个模块,然后输入下面的代码。我修改了此代码回答获取 A 列的最后一行并使用 webp 文件(可以更改/删除
Option Explicit
Dim rng As Range
Dim cell As Range
Dim Filename As String
Sub URLPictureInsert()
Dim theShape As Shape
Dim xRg As Range
Dim xCol, lRow As Long
On Error Resume Next
lRow = Cells(Rows.Count, 1).End(xlUp).Row 'Get the last row of column A
Set rng = ActiveSheet.Range("C1:C" & lRow) 'Set the range of the URLs
Dim i As Integer
i = 0
For Each cell In rng
Filename = cell
If InStr(LCase(Filename), "webp") > 0 Then '<--- ONLY USES webp
ActiveSheet.Pictures.Insert(Filename).Select
Set theShape = Selection.ShapeRange.Item(1)
If theShape Is Nothing Then GoTo isnill
xCol = cell.Column + 1
Set xRg = Cells(cell.Row, xCol)
' xRg.Clear
With theShape
.LockAspectRatio = msoFalse
.Width = 60
.Height = 32
If i = 0 Then
.Top = xRg.Top + (xRg.Height - .Height) 'Adjust the top location of the first row's image --Should be re-adjust
i = i + 1
Else
.Top = xRg.Top + 10 + (xRg.Height - .Height) / 2 'Adjust the top location of subsequent rows' images --Should be re-adjust
End If
.Left = xRg.Left + (xRg.Width - .Width) / 2
End With
xRg.RowHeight = theShape.Height 'Adjust row height to fit the image
xRg.ColumnWidth = 10 'Adjust the column height to fit the image
isnill:
Set theShape = Nothing
Range("A2").Select
End If
Next
Application.ScreenUpdating = True
Debug.Print "Done " & Now
End Sub
当代码运行完毕后,标志将显示在 D 列的 URL 旁边。您可能需要重写在工作表上插入图像的代码部分。我重新调整了以适合工作表的高度和宽度。