Insert Picture From Google Drive Excel VBA Macro

In a previous post we saw how to insert pictures from the web in Excel using VBA macros. In this post we see how to insert pictures specifically from Google Drive, which requires the Google Drive view path and the picture sharing ID. The picture will be inserted in the selected range and resized to fit in accordingly.

Macro code:

 

  Sub InsertPictureFromGoogleDrive()

 

      Dim url As String, id As String, path As String

      Dim x As Integer, y As Integer, w As Integer, h As Integer

 

      path = "https://drive.google.com/uc?export=view&id="

      id = "1cdRMD69iVqwBO_x-4UBxbvfSpMyS-t8W"

      url = path & id

 

      x = Selection.Left

      y = Selection.Top

      w = Selection.Width

      h = Selection.Height

 

      ActiveSheet.Shapes.AddPicture url, msoFalse, msoTrue, x, y, w, h

 

  End Sub

 

Macro explained:

• First we declare string variables to store the Google Drive view path, picture ID, and whole url, and four integers for the position coordinates of the range where the picture is added.
• Then we assign the value of the path to Google Drive view, and the file or picture ID. The picture ID is in the Google 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 url is the concatenation of the Google Drive view path and the picture ID.
• Then we assign the position coordinates of the selected range to the variables x, y, w, and h, that determine the left and top positions, and width and height of the range.
• Finally, we add the picture in the active worksheet with the AddPicture method of the Shapes object. It accepts the following parameters:

• The FileName parameter or source url for the picture in this case
• The LinkToFile parameter that can either be msoTrue to link the picture to its source location, or msoFalse to make an independent copy of the picture
• The SaveWithDocument parameter that can either be msoTrue to save the linked picture with the document or msoFalse to store only the link information.
• The four position coordinates that determine the area where the picture is inserted

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

Other examples:

• How to AutoFill Table With SpecialCells with Excel VBA macros
  
• How to Resize All Charts In Worksheet with Excel VBA macros
  
• How to Split Text Into Columns with Excel VBA macros