edit powerpoint from excel macro to insert images

[mttemp]

Hi, I used to work with macros but don't know vba, I just used to record them. I am trying to set up a macro to create powerpoints automatically from excel sheets. I have downloaded a sample one from a forum which creates two text boxes from two columns in the excel. I would like to also add an image box - a different image on each slide. I don't think I can add images to hte excel - or can I? I can add pathways to the images, but how would I tell the macro to convert that pathway to the image itself? Or is there a better way to do it? This is the macro I have downloaded thanks to a site called contextures. Thank you for your help!

Option Explicit

Sub CreateSlidesTest_Text1()
'https://www.contextures.com
'create slide for names
' that pass criteria test
'fill one text box
Dim myPT As Presentation
Dim xlApp As Object
Dim wbA As Object
Dim wsA As Object
Dim myList As Object
Dim myRng As Object
Dim i As Long
Dim col01 As Long
Dim colTest As Long
Dim strTest As String

'column with text for slides
col01 = 1
'test column and criterion
colTest = 3
strTest = "y"

On Error Resume Next
Set myPT = ActivePresentation
Set xlApp = GetObject(, "Excel.Application")
Set wbA = xlApp.ActiveWorkbook
Set wsA = wbA.ActiveSheet
Set myList = wsA.ListObjects(1)
On Error GoTo errHandler

If Not myList Is Nothing Then
  
  Set myRng = myList.DataBodyRange
  
  For i = 1 To myRng.Rows.Count
    'Copy first slide, paste after last slide
    If UCase(myRng.Cells(i, colTest).Value) _
        = UCase(strTest) Then
      With myPT
         .Slides(1).Copy
         .Slides.Paste (myPT.Slides.Count + 1)
      
         'change text in 1st textbox
         .Slides(.Slides.Count) _
           .Shapes(1).TextFrame.TextRange.Text _
             = myRng.Cells(i, col01).Value
      End With
    End If
  Next
Else
  MsgBox "No Excel table found on active sheet"
  GoTo exitHandler
End If

exitHandler:
  Exit Sub
errHandler:
  MsgBox "Could not complete slides"
  Resume exitHandler:
End Sub

Sub CreateSlidesTest_Text2()
'https://www.contextures.com
'create slide for names
' that pass criteria test
'fill two text boxes
Dim myPT As Presentation
Dim xlApp As Object
Dim wbA As Object
Dim wsA As Object
Dim myList As Object
Dim myRng As Object
Dim i As Long
Dim col01 As Long
Dim col02 As Long
Dim colTest As Long
Dim strTest As String

'columns with text for slides
col01 = 1
col02 = 2
'test column and criterion
colTest = 3
strTest = "y"

On Error Resume Next
Set myPT = ActivePresentation
Set xlApp = GetObject(, "Excel.Application")
Set wbA = xlApp.ActiveWorkbook
Set wsA = wbA.ActiveSheet
Set myList = wsA.ListObjects(1)
On Error GoTo errHandler

If Not myList Is Nothing Then
  
  Set myRng = myList.DataBodyRange
  
  For i = 1 To myRng.Rows.Count
    'Copy first slide, paste after last slide
    If UCase(myRng.Cells(i, colTest).Value) _
        = UCase(strTest) Then
      With myPT
         .Slides(1).Copy
         .Slides.Paste (myPT.Slides.Count + 1)
      
         'change text in 1st textbox
         .Slides(.Slides.Count) _
           .Shapes(1).TextFrame.TextRange.Text _
             = myRng.Cells(i, col01).Value
         
         'change text in 2nd textbox
         .Slides(.Slides.Count) _
           .Shapes(2).TextFrame.TextRange.Text _
             = myRng.Cells(i, col02).Value
      End With
    End If
  Next
Else
  MsgBox "No Excel table found on active sheet"
  GoTo exitHandler
End If

exitHandler:
  Exit Sub
errHandler:
  MsgBox "Could not complete slides"
  Resume exitHandler:
End Sub

 

[Domenic]

Try adding the following code after the lines of code that create your two textboxes. You'll need to decide how to specify the path/filename for the image, and you'll need to change the values for Left, Top, Width and Height accordingly. By the way, the -1 for both the width and height means that the defaults are used. And, actually, in this case, you actually omit both the width and height arguments.

          'add image to slide
          .Slides(.Slides.Count).Shapes.AddPicture _
                FileName:="C:\Path\Filename.jpg", _
                LinkToFile:=msoFalse, _
                SaveWithDocument:=msoTrue, _
                Left:=50, _
                Top:=50, _
                Width:=-1, _
                Height:=-1

Hope this helps!

 

[mttemp]

Thank you!! Trying to figure it out but might be too rusty. There is a different image for each row in the excel, so the path would be listed as an extra column. So does that mean for the Filename I should be copying

myRng.Cells(i, col02).Value

having created a col03?

 

[Domenic]

If the third column contains the path and filename then yes, you would reference the third column in myRng.

 

[mttemp]

If the third column contains the path and filename then yes, you would reference the third column in myRng.

Like this?

 'add image to slide
          .Slides(.Slides.Count).Shapes.AddPicture _
                FileName:="myRng.Cells(i, col03).Value", _
                LinkToFile:=msoFalse, _
                SaveWithDocument:=msoTrue, _
                Left:=50, _
                Top:=50, _
                Width:=-1, _
                Height:=-1

 

[Domenic]

You'll need to remove the quotes . . .

FileName:=myRng.Cells(i, col03).Value, _

 

[mttemp]

You'll need to remove the quotes . . .

FileName:=myRng.Cells(i, col03).Value, _

Amazing! It worked! Thank you so much. Loads of tweaking to do to get to the stage where I can use it for what I want to use it for but excited to have got this far!

 

[mttemp]

Oh ... one last thing I forgot to ask. Would using links to photos on my computer/server/web like this be the best way to incorporate the pictures or am I making trouble for myself later? I did it because I couldn't think of any other way to keep all the data in the original excel along with a photo to go with each row of data, as I don't think a cell can contain a photo. (Each row of data refers to a plant in our local park and we will be taking seasonal photos of each plant. So I was going to have a folder full of photos to go with the excel and create a column with the pathways to the photos. I would be interested to know if there are disadvantages to doing it this way or better suggestions. In the end we should have about 500 rows and will be pulling up powerpoints with 10-20 at a time, one plant per slide.)

 

[Domenic]

I'm not sure I quite understand, but the picture added to the slide is not linked. It's a copy of the image file. So if you delete the pictures from your computer/server/web, it won't affect the ones in your slides. And if you need to replace an existing picture with a new one, you would first need to delete the existing one, and then add the new one.

Does this answer your question?

 

[mttemp]

I'm not sure I quite understand, but the picture added to the slide is not linked. It's a copy of the image file. So if you delete the pictures from your computer/server/web, it won't affect the ones in your slides. And if you need to replace an existing picture with a new one, you would first need to delete the existing one, and then add the new one.

Does this answer your question?

Not quite but actually it raises an interesting point I hadn't even considered! I'll have to think about that! (Actually my original question was just about how to create and store the source images alongside the data which we have in excel rows - whether using a column with file pathways as I've done above was even the best way to include an image per row in an excel source in the first place or whether there was some simpler way I hadn't thought of.)

Back to the code itself, my next problem is how to get it to keep running even if there is a row with a blank (eg no text in one column or no photo). At the moment it stops the first time it comes to a row with a blank and says 'Could not complete slides'.