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]

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.)

If you store your images within the workbook itself, it would mean that you would have to copy and paste your images to your slides instead of using the AddPicture method. My guess is that the AddPicture method would be more efficient. Also, unless there can be a clever way of referring directly to the desired picture for each row, you would have to loop through each picture until you find the one located on the desired row, and then copy/paste. Again, probably not as efficient. Also, storing your images within the workbook means that the size of the file will become large, which may or may not be an issue. On the other hand, storing your images in a directory somewhere means that you would have to make sure that they will be available and accessible when needed.

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'.

It looks like you'll get that error when there's no path/filename or the specified path/filename doesn't exist. So if the column contains text, but no path/filename, do you still want to create the slide that includes only the text? What if there's no text, but there's a path/filename?

 

[mttemp]

If you store your images within the workbook itself, it would mean that you would have to copy and paste your images to your slides instead of using the AddPicture method. My guess is that the AddPicture method would be more efficient. Also, unless there can be a clever way of referring directly to the desired picture for each row, you would have to loop through each picture until you find the one located on the desired row, and then copy/paste. Again, probably not as efficient. Also, storing your images within the workbook means that the size of the file will become large, which may or may not be an issue. On the other hand, storing your images in a directory somewhere means that you would have to make sure that they will be available and accessible when needed.

Thanks, that's so useful to have it explained. Glad my instincts were on the right track!

It looks like you'll get that error when there's no path/filename or the specified path/filename doesn't exist. So if the column contains text, but no path/filename, do you still want to create the slide that includes only the text? What if there's no text, but there's a path/filename?

Mainly I was trying to stop it from not creating the later slides when there is some info missing in one. But having thought about your questions I think I might be better off putting some placeholder text ("Add text manually" or "Add image manually" in any blank cells anyway).

Thanks you soooooooo much for all your help, you've been amazing!

 

[Domenic]

That's great, I'm glad I was able to help, cheers!