Hello,
I want to be able to use the Excel 365 image functions (filter, xlookup on images in Excel). I use the Excel Writer in my workflow but the images are not embedded in the Excel cells and I cannot specify alt text using my workflow. I need the alt text otherwise Excel has all the images as picture and I cannot use the filter drop down.
I have alt text in columns A-D, then images in A(IMG)-D(IMG). My images are Png.
Any ideas?
Thank you in advance for any help.
Anne
I would recommend the write to Excel Template to maintain the formatting and existing programming / references in other cells. Here is a workflow that I uploaded a few days ago that writes both png images and string values into a template file.
For a simple solutions, I would just write the text to another column and reference it for filtering via drop-down. You can always hide the referenced filter column in the template file.
Adding alt text specifically might be possible via Python openPyXL.
Hello iCFO,
Thank you for your suggestions. I tried your workflow but the images are not placed in cells. They are in a layer over the Excel cells.
I can embed the pictures using this macro in Excel but would like to send the images from KNIME to Excel already embedded in the cell preferably with the alt text so all the images are not named “Picture”
Here is the Excel macro.
Sub PlaceAllPicturesInCells()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
shp.Select 'shape/picture must be selected first
shp.PlacePictureInCell
Next shp
End Sub
Thank you for your help.
Anne
The only excel side workarounds I could come up with is to write the file name in a neighboring column (which could be hidden), then use VBA or IMAGE formula https://www.ablebits.com/office-addins-blog/excel-image-function/ to insert that cell value as Alt text when embedding the images… I believe that you could reference the Alt text into the formula or VBA script from the neighboring hidden cell.
@mlauber71 - Is embedding images into a table along with Alt text directly from KNIME something that seems possible using Python openPyXL?
Edit: I have never tried this with an image file referenced, but it is possible to write a formula in KNIME and then select the setting under advanced to have cells starting with = written as a formula and re-calculated in the Template file. That would allow you to use KNIME to write the complete IMAGE formula along with the Alt Text and image size specs… Not sure how to embed that image at the same time though. Perhaps you could reference the image location in the formula as well and then run your macro to embed them? Seems like a referenced formula approach would duplicate the images and add to the file size though, so VBA may be a better bet.
Images can be placed with the help of openpyxl but Alt text is not possible as of now.
SDF SMILES image test.xlsx (93.4 KB)
Here is a sample of what I am trying to do. I used @iCFO data and put in a column with SMILES so I have some text. The first worksheet, I can filter by SMILES in a separate column, the images are over the cell in a separate column, the second worksheet, I have the SMILES with the Image over the cell in the same column and can filter by SMILES. The third worksheet I have the Image in the cell and the SMILES as alt text. I can still filter by SMILES but I can also use Excel functions on the image. I show XLOOKUP but I can also right click on an image, then Filter by Selected Cell’s Value to see its associated information.
I would like to be able to write images to Excel and choose if the images are over the cell or in the cell, and if there is alt text.
Thank you for all your help.
I have a VBA script that is successfully locating all of the image files that are placed “over a cell” then it looks at the cell to the left and applies that text as the alt text for the image. (for some reason it is struggling with Placed in Cells, so you would need to run it before the script that places them within cells if you choose to go that way.)
All you would need to do is to write the text values in the column to the left of the image column from KNIME and then execute the macro on the excel side to apply the alt text. If you use the Write to Excel Template node they you can just keep those reference columns hidden if you choose.
Unfortunately I can’t just upload an .xlsm file to the forum. I will paste the VBA script for you and upload a slightly modified version of your .xslx file (with an additional text column to reference for the last tab and images placed over the cells). All you would need to do is change it to .xlsm, open the VBA editor, right click near the sheet names on the left and create a new VBA module, paste in the VBA script, save, close the vba editor, and run the macro.
SDF SMILES image test.xlsx (96.8 KB)
Sub UpdateImageAltText()
Dim ws As Worksheet
Dim shp As Shape
Dim targetCell As Range
Dim cellValue As String
Dim updatedCount As Long
Dim shapeName As String
Dim lastRow As Long
Dim cellAddress As String
Dim leftCell As Range
updatedCount = 0
' Iterate through all sheets
For Each ws In ThisWorkbook.Worksheets
Debug.Print "Processing sheet: " & ws.Name
' Loop through each shape in the sheet
For Each shp In ws.Shapes
shapeName = shp.Name
Debug.Print "Processing shape: " & shapeName
' Check if the shape is a picture
If shp.Type = msoPicture Then
' Determine the cell where the picture is located
cellAddress = GetShapeCellAddress(shp)
If cellAddress <> "" Then
Set targetCell = ws.Range(cellAddress)
' Get the cell to the left of the picture
Set leftCell = ws.Cells(targetCell.Row, targetCell.Column - 1)
cellValue = leftCell.Value
Debug.Print "Target cell address: " & cellAddress
Debug.Print "Cell to the left address: " & leftCell.Address
Debug.Print "Cell to the left value: " & cellValue
' Update the shape's alt text with the cell value
If shp.AlternativeText <> cellValue Then
shp.AlternativeText = cellValue
updatedCount = updatedCount + 1
Debug.Print "Updated Alt Text: " & shp.AlternativeText
Else
Debug.Print "Alt Text already up-to-date."
End If
Else
Debug.Print "No corresponding cell found for shape."
End If
End If
Next shp
Debug.Print updatedCount & " images updated with new alt text."
Next ws
End Sub
' Function to determine the cell address where the shape is located
Function GetShapeCellAddress(shp As Shape) As String
Dim ws As Worksheet
Dim cell As Range
On Error Resume Next
Set ws = shp.TopLeftCell.Worksheet
Set cell = ws.Cells(shp.TopLeftCell.Row, shp.TopLeftCell.Column)
If Not cell Is Nothing Then
GetShapeCellAddress = cell.Address
Else
GetShapeCellAddress = ""
End If
On Error GoTo 0
End Function
Thank you, this is awesome.
It seems like the alt text piece needs to be done before the Place in Cells.
Yes. That was the only way I was able to get it to work smoothly even with chatGPT’s help. Just couldn’t figure out how to target and adjust Picture in Cell images as well, so keep that to step 2 or add it to the bottom of this script.
I don’t expect that image name and alt text will be easily incorporated into an excel writer node. That info wouldn’t be stored in the KNIME data type, so you would probably need to assign 3 fields to a single excel image column to make it work. That seems like a major rebuild.
In case the name of the image is crucial for your work as well, I did a quick modification that will also rename the image based on the text value 2 cells to the left and place all of the images into the cell. Here is the VBA script and the .xlsx test file with the necessary reference columns. It also shows that the image name and alt text columns can be hidden in the template file without impacting the VBA script so that the entire process is visually unintrusive.
SDF SMILES image test.xlsx (90.7 KB)
Sub UpdateImageAltTextRenameAndPlace()
Dim ws As Worksheet
Dim shp As Shape
Dim targetCell As Range
Dim cellValue As String
Dim updatedCount As Long
Dim shapeName As String
Dim cellAddress As String
Dim leftCell As Range
Dim renameCell As Range
Dim renameValue As String
updatedCount = 0
' Iterate through all sheets
For Each ws In ThisWorkbook.Worksheets
Debug.Print "Processing sheet: " & ws.Name
' Loop through each shape in the sheet
For Each shp In ws.Shapes
shapeName = shp.Name
Debug.Print "Processing shape: " & shapeName
' Check if the shape is a picture
If shp.Type = msoPicture Then
' Determine the cell where the picture is located
cellAddress = GetShapeCellAddress(shp)
If cellAddress <> "" Then
Set targetCell = ws.Range(cellAddress)
' Get the cell to the left of the picture
Set leftCell = ws.Cells(targetCell.Row, targetCell.Column - 1)
' Get the cell two columns to the left for renaming
Set renameCell = ws.Cells(targetCell.Row, targetCell.Column - 2)
cellValue = leftCell.Value
renameValue = renameCell.Value
Debug.Print "Target cell address: " & cellAddress
Debug.Print "Cell to the left address: " & leftCell.Address
Debug.Print "Cell to the left value: " & cellValue
Debug.Print "Cell two columns to the left value: " & renameValue
' Update the shape's alt text with the cell value
If shp.AlternativeText <> cellValue Then
shp.AlternativeText = cellValue
updatedCount = updatedCount + 1
Debug.Print "Updated Alt Text: " & shp.AlternativeText
Else
Debug.Print "Alt Text already up-to-date."
End If
' Rename the shape based on the value from two cells to the left
If renameValue <> "" Then
shp.Name = renameValue
Debug.Print "Shape renamed to: " & shp.Name
Else
Debug.Print "No renaming performed as the value is empty."
End If
' Resize and position the image to fit the target cell
PlaceImageInCell shp, targetCell
Debug.Print "Image placed in cell: " & targetCell.Address
Else
Debug.Print "No corresponding cell found for shape."
End If
End If
Next shp
Debug.Print updatedCount & " images updated with new alt text."
Next ws
End Sub
' Function to determine the cell address where the shape is located
Function GetShapeCellAddress(shp As Shape) As String
Dim ws As Worksheet
Dim cell As Range
On Error Resume Next
Set ws = shp.TopLeftCell.Worksheet
Set cell = ws.Cells(shp.TopLeftCell.Row, shp.TopLeftCell.Column)
If Not cell Is Nothing Then
GetShapeCellAddress = cell.Address
Else
GetShapeCellAddress = ""
End If
On Error GoTo 0
End Function
' Sub to resize and position the image within the target cell
Sub PlaceImageInCell(shp As Shape, targetCell As Range)
' Adjust the size of the shape to fit the target cell
With shp
.LockAspectRatio = msoFalse
.Width = targetCell.Width
.Height = targetCell.Height
.Top = targetCell.Top
.Left = targetCell.Left
End With
End Sub
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.