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...

Комментарии • 3

  • @Ezel21love
    @Ezel21love 2 года назад

    Oh man! I wish if you help me to have the same feature on Google Sheets

  • @matthewinpongpan3747
    @matthewinpongpan3747 4 года назад

    I cannot get this to work for me. Edited everything correctly but no luck.

  • @staignas
    @staignas 5 лет назад

    Super ! its working! Thank you :)