Insert Pictures From Google Drive Excel VBA Macro UPDATE!

This is an UPDATE to a previous post where we saw how to insert pictures from Google Drive in Excel using VBA macros. The method explained before needs a slight change to continue working. That change consists of inserting the picture from Google Drive into a shape previously added to the Excel worksheet, instead of adding the picture directly to a range or cell. The macro requires access to the same Google Drive export view URL link including the picture sharing ID described in the previous article.

Macro code:

  
  Sub InsertPictureFromGoogleDrive()
 
      Dim url As String, id As String, shp As Shape
      Dim x As Integer, y As Integer, w As Integer, h As Integer
     
      id = "1cdRMD69iVqwBO_x-4UBxbvfSpMyS-t8W"
      url = "https://drive.google.com/uc?export=view&id=" & id
 
      x = Selection.Left
      y = Selection.Top
      w = Selection.Width
      h = Selection.Height
 
      Set shp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, x, y, w, h)
 
      With shp
          .Line.Visible = msoFalse
          .Fill.UserPicture url
      End With
 
  End Sub
 

Macro explained:

• First, we declare string variables to store the Google Drive picture ID and Google Drive export view URL concatenated with the picture ID. We declare a Shape object variable and four integers for the position coordinates of the shape added to the selection.
• We need to get the picture ID from Google Drive. The picture ID is in the Google Drive sharing link after “/d/” and before “/view…”. See the example below.

https://drive.google.com/file/d/1cdRMD69iVqwBO_x-4UBxbvfSpMyS-t8W/view?usp=sharing

• The picture needs to be shared with anyone in the Google Drive settings (right-click the picture -> Share -> Get Link -> Anyone in the internet with this link can view)
• The final URL is the concatenation of the Google Drive export view path and the picture ID. Here’s how the URL would look like for the example above.

https://drive.google.com/uc?export=view&id=1cdRMD69iVqwBO_x-4UBxbvfSpMyS-t8W

• Please note that there are several other Google Drive URLs to view or preview the picture on the browser. Those would work to add the picture with HTML, for example, but will not work to add the picture in Excel.

https://drive.google.com/file/d/1cdRMD69iVqwBO_x-4UBxbvfSpMyS-t8W/view

https://drive.google.com/file/d/1cdRMD69iVqwBO_x-4UBxbvfSpMyS-t8W/preview

• Then we assign the position coordinates of the selected range to the variables x, y, w, and h, which determine the left and top positions, and width and height of the selected range where we want to add the shape.
• Now we can add a rectangular shape to the selected range in the active worksheet, assigning the shape to an object variable (shp).We could also add any other type of auto shape, but that would affect the appearance of the picture added in the next step. You can see the AutoShape type enumeration and visualization of Microsoft auto shapes in this other page: AutoShape Type Enumeration 
• Finally, we remove the outline of the shape and insert the picture into the shape using the Fill property and UserPicture method of the Shape object, along with the Google Drive export view URL variable (url) as attribute.

This is how we insert pictures from Google Drive in Excel using VBA macros.

Other examples:

• How to Import Specific Cells From Google Sheets with Excel VBA macros
• How to Unhide All Sheets And Cells with Excel VBA macros
  
• How to Separate Words And Numbers with Excel VBA macros