CODEWITHSUNDEEP
excelvbaimagecharts
franciscofcosta
franciscofcosta

Reputation: 843

VBA to assign images to scatter plot in Excel

I have a scatter plot chart on Excel. Each point in the plot refers to a company. I would like for each of the points in the plot to be filled with an image that corresponds to the company they refer to. The companies names are in the spreadhseet in column G, starting on row 3.

I have managed to write some VBA that loads the images to Excel relating to the names of the companies. For example, if the cell has 'Microsoft' written on it, the script will look for a picture with the same name and post it to a predefined cell in the spreadsheet. I would now like for the script to fill the 'Microsoft' point in the scatter plot with the image it loaded.

The script will run for as long as the cells 

Sub Macro2()

Dim picname As String
Dim shp As Shape
Dim pasteAt As Integer
Dim lThisRow As Long
Dim present As String

lThisRow = 3 'This is the start row

Do While (Cells(lThisRow, 7) <> "")


    pasteAt = lThisRow
  Cells(pasteAt, 2).Select 'This is where picture will be inserted (column)


    picname = Cells(lThisRow, 7) 'This is the picture name

    present = Dir("C:\Users\User\Images\" & picname & ".jpg")

    If present <> "" Then

            Cells(pasteAt, 2).Select

            Call ActiveSheet.Shapes.AddPicture("C:\Users\User\Images\" & picname & ".jpg", _
            msoCTrue, msoCTrue, Left:=Cells(pasteAt, 2).Left, Top:=Cells(pasteAt, 2).Top, Width:=100, Height:=100).Select

    End If

       lThisRow = lThisRow + 1
Loop

End Sub

I would now like to complement the script so that the images would be inserted in the chart.

Upvotes: 1

Views: 1100

Answers (1)

Jon Peltier
Jon Peltier

Reputation: 6063

You need to loop through the series and points in the chart. You don't indicate how the data is arranged and plotted, but I'll assume the chart has one series of whatever X and Y, and the company column is parallel to the X and Y values.

I've tried to merge my additions smoothly:

Sub ImportPicturesAndPutIntoChart()

  Dim picname As String
  Dim shp As Shape
  Dim lThisRow As Long
  Dim present As String
  Dim cht As Chart, srs As Series

  lThisRow = 3 'This is the start row

  Set cht = ActiveSheet.ChartObjects(1).Chart
  Set srs = cht.SeriesCollection(1)

  Do While (Cells(lThisRow, 7) <> "")
    If lThisRow - 2 > srs.Points.Count Then Exit Do

    Cells(lThisRow, 2).Select 'This is where picture will be inserted (column)
    picname = Cells(lThisRow, 7) 'This is the picture name

    present = Dir("C:\Users\User\Images\" & picname & ".jpg")
    If present <> "" Then

      Cells(pasteAt, 2).Select

      Set shp = Shapes.AddPicture("C:\Users\User\Images\" & picname & ".jpg", _
          msoCTrue, msoCTrue, Left:=Cells(lThisRow, 2).Left, Top:=Cells(lThisRow, 2).Top, _
          Width:=100, Height:=100)

      shp.Copy
      srs.Points(lThisRow - 2).Paste

    End If

    lThisRow = lThisRow + 1

  Loop

End Sub

Upvotes: 1

Related Questions