How to Create an Excel Image Column
HTML-код
- Опубликовано: 28 сен 2024
- How to write a VBScript that automatically sets the background of an excel comment to be a custom image that's only visible on hover.
Below is the content of the script used in the video:
'put the excel file, the image folder, and the script in the same folder
spreadsheetName = "Countries.xlsx"
imagesType = ".png"
imageNameColumn = "A"
imageColumn = "D"
imageWidth = 150
Set objExcel = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")
Set oImage = CreateObject("WIA.ImageFile")
directory = fso.GetParentFolderName(WScript.ScriptFullName)
Set objWorkbook = objExcel.Workbooks.Open(directory & "\" & spreadsheetName)
objExcel.Application.Visible = True
for i = 1 to objExcel.ActiveWorkbook.Worksheets(1).UsedRange.Rows.Count
imageName = objExcel.Cells(i, imageNameColumn).Value
imageFile = directory & "\" & imageName & imagesType
if fso.FileExists(imageFile) then
oImage.LoadFile imageFile
with objExcel.Cells(i, imageColumn)
.AddComment " "
.Comment.Shape.Fill.UserPicture imageFile
.Comment.Shape.Width = imageWidth
.Comment.Shape.Height = oImage.Height * imageWidth/oImage.Height
end with
end if
next
Available here: gist.github.co...
Oh man! I wish if you help me to have the same feature on Google Sheets
I cannot get this to work for me. Edited everything correctly but no luck.
Super ! its working! Thank you :)