Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 'Excel 2019 VBScript, Insert Image to cells in column E by getting Image url in column Q
- 'If you have questions, visit https://nicolaslagios.com or https://maxservices.gr
- Sub InsertImage()
- Dim image As Object
- Dim imageURL As String
- Dim row As Long
- Dim lastRow As Long
- Dim arr() As String
- 'Find the last row in column Q
- lastRow = ActiveSheet.Cells(Rows.Count, "Q").End(xlUp).row
- 'Loop through all rows in column Q
- For row = 2 To lastRow
- 'Get the image URL from column Q
- imageURL = ActiveSheet.Cells(row, "Q").Value
- 'Check if an image URL exists in the current cell
- If Len(imageURL) > 0 Then
- 'Split the imageURL into an array using | as the delimiter (in case you have more than one images on the cell)
- arr = Split(imageURL, "|")
- 'Loop through all elements in the array
- For i = 0 To UBound(arr)
- 'Use the current element in the array as the imageURL
- imageURL = arr(i)
- 'Try to insert the image
- On Error Resume Next 'Turn error handling on
- 'Set the width and height of the cell in column E to 60 pixels
- ActiveSheet.Cells(row, "E").ColumnWidth = 11
- ActiveSheet.Cells(row, "E").RowHeight = 60
- Set image = ActiveSheet.Shapes.AddPicture(Filename:=imageURL, LinkToFile:=msoFalse, SaveWithDocument:=msoTrue, Left:=Cells(row, "E").Left + 2, Top:=Cells(row, "E").Top + 2, Width:=55, Height:=55)
- If Err.Number <> 0 Then
- 'An error occurred, continue to the next element in the array
- Err.Clear 'Clear the error
- On Error GoTo 0 'Turn error handling off
- Exit For
- End If
- On Error GoTo 0 'Turn error handling off
- 'Set the image to be inline with the text
- image.LockAspectRatio = msoFalse
- 'Exit the loop, because an image was successfully inserted
- Exit For
- Next i
- End If
- Next row
- End Sub
Advertisement
Advertisement