Advertisement
nicolaslagios

Excel 2019 VBScript - Insert Image from url

Jan 7th, 2023 (edited)
2,262
-1
Never
1
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
VisualBasic 2.02 KB | Source Code | 0 1
  1. 'Excel 2019 VBScript, Insert Image to cells in column E by getting Image url in column Q
  2. 'If you have questions, visit https://nicolaslagios.com or https://maxservices.gr
  3. Sub InsertImage()
  4.   Dim image As Object
  5.   Dim imageURL As String
  6.   Dim row As Long
  7.   Dim lastRow As Long
  8.   Dim arr() As String
  9.  
  10.   'Find the last row in column Q
  11.  lastRow = ActiveSheet.Cells(Rows.Count, "Q").End(xlUp).row
  12.  
  13.   'Loop through all rows in column Q
  14.  For row = 2 To lastRow
  15.     'Get the image URL from column Q
  16.    imageURL = ActiveSheet.Cells(row, "Q").Value
  17.    
  18.     'Check if an image URL exists in the current cell
  19.    If Len(imageURL) > 0 Then
  20.         'Split the imageURL into an array using | as the delimiter (in case you have more than one images on the cell)
  21.        arr = Split(imageURL, "|")
  22.        
  23.         'Loop through all elements in the array
  24.        For i = 0 To UBound(arr)
  25.             'Use the current element in the array as the imageURL
  26.            imageURL = arr(i)
  27.             'Try to insert the image
  28.            On Error Resume Next 'Turn error handling on
  29.                'Set the width and height of the cell in column E to 60 pixels
  30.                ActiveSheet.Cells(row, "E").ColumnWidth = 11
  31.                 ActiveSheet.Cells(row, "E").RowHeight = 60
  32.             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)
  33.             If Err.Number <> 0 Then
  34.                 'An error occurred, continue to the next element in the array
  35.                Err.Clear 'Clear the error
  36.                On Error GoTo 0 'Turn error handling off
  37.                Exit For
  38.             End If
  39.             On Error GoTo 0 'Turn error handling off
  40.            'Set the image to be inline with the text
  41.            image.LockAspectRatio = msoFalse
  42.             'Exit the loop, because an image was successfully inserted
  43.            Exit For
  44.         Next i
  45.     End If
  46.   Next row
  47. End Sub
Advertisement
Comments
Add Comment
Please, Sign In to add comment
Advertisement