写一个将图片链接批量转换成在excel中可预览的图片的vba程序,获取原图片大小后自动更改要插入表格的行高与列宽,要求预留图片链接所在行并标注出来,预留生成图片所在行并标注出来
要在Excel中批量插入图片并调整行高和列宽,可以使用VBA编写一个宏来实现。以下是一个示例代码,它将图片链接批量转换成在Excel中可预览的图片,并自动调整行高和列宽,同时预留图片链接所在行并标注出来,预留生成图片所在行并标注出来。
Sub InsertPictures()
Dim ws As Worksheet
Dim pic As Picture
Dim picLink As String
Dim lastRow As Long
Dim i As Long
Dim picHeight As Double
Dim picWidth As Double
Dim cell As Range
' Set the worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
' Find the last row with data in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Loop through each cell in column A
For i = 1 To lastRow
' Get the picture link from column A
picLink = ws.Cells(i, 1).Value
' Skip empty cells
If picLink <> "" Then
' Insert the picture
Set pic = ws.Pictures.Insert(picLink)
' Get the original size of the picture
picHeight = pic.Height
picWidth = pic.Width
' Adjust the row height and column width
ws.Rows(i + 1).RowHeight = picHeight
ws.Columns("B").ColumnWidth = picWidth / 5.3 ' Adjust the factor as needed
' Move the picture to the correct cell
pic.Top = ws.Cells(i + 1, 2).Top
pic.Left = ws.Cells(i + 1, 2).Left
' Mark the original link row
ws.Cells(i, 1).Interior.Color = RGB(255, 255, 0) ' Yellow color
ws.Cells(i, 1).Value = "Link: " & picLink
' Mark the picture row
ws.Cells(i + 1, 1).Interior.Color = RGB(0, 255, 0) ' Green color
ws.Cells(i + 1, 1).Value = "Picture"
End If
Next i
End Sub
Sheet1
的列 A 中。请根据实际情况调整代码中的工作表名称和列宽调整因子。运行此宏时,请确保图片链接是有效的 URL 或本地路径。