Copy from Excel to Powerpoint to create a report

[PSL]

Hi All,

As part of my job I'm required to create and update reports from a set of data on an Excel spreadsheet. The report has standard sections like Topic, Title, Date, Ref. No., Summary, Key Words and Detailed Finding. Details about each section is as follows:

Topic: The overall topic of the report. Typically a word or two
Title : Next level details. A sentence
Summary: 2 - 4 points with the main points of the report
Keywords: This has two sections; Financial and Non Financial. These are like hashtags and can be from 2 - 5 under each sub section.
Detailed Findings: This is the most important section and one which needs to change frequently. Each finding is represented as Finding, Summary and Details (bullet points ranging from 1 to 6/7). The excel would have an exhaustive set of findings from which I would need to select 4 that need to be shown in the report. The overall pool can increase as time goes on, but the total number that needs to be presented on the report remains 4.

I've attached the following sample files:
1. Report Format - The ppt format of the report and how the final outcome looks
2. Report Excel - This is where the data is stored and edited to create the report.

Looking to create a macro that can generate the report from the excel into the report format in ppt. I've made some minor excel macros before but wasn't sure if it's possible to create a macro that talks to both excel and powerpoint with all the formatting involved. I'm open to exploring the possibility of changing the template if copy into this would prove to be too challenging!

Look forward to any ideas! Thanks :-)

Note: I wasn't sure if this should be posted in the Excel or Powerpoint section, so do let me know if it would make more sense in the Powerpoint section.

[georgiboy]

Im not sure either as it is using both.

I have had a play with this and thought i would share what i have.

Please note that i believe this could be made simpler if you can modify the way the excel data is arranged.

Sub pp()

    Dim ppSl As PowerPoint.Slide
    Dim strPath As String
    Dim rCell As Range


    strPath = "C:\Users\A\Desktop\Report Format_VBA.pptx" '< your template location
    
    Set oPPTApp = CreateObject("PowerPoint.Application")
    oPPTApp.Visible = msoTrue
    Set oPPTFile = oPPTApp.Presentations.Open(strPath)
    Set ppSl = oPPTFile.Slides(1)
    
    With Sheet1
        ppSl.Shapes("TextBox 3").TextFrame.TextRange.Text = .Range("B2").Value
        ppSl.Shapes("TextBox 4").TextFrame.TextRange.Text = .Range("B3").Value
        ppSl.Shapes("Rectangle 7").TextFrame.TextRange.Text = .Range("B9").Value
        ppSl.Shapes("Rectangle 16").TextFrame.TextRange.Text = ""
        
        For Each rCell In .Range("B21:B" & .Range("B" & Rows.Count).End(xlUp).Row).Cells
            If rCell.Offset(, 1).Value = "y" Then
                With ppSl.Shapes("Rectangle 16").TextFrame.TextRange
                    .Text = .Text & rCell.Value & vbNewLine & vbNewLine
                End With
            End If
        Next rCell
    End With
    
    ppSl.Shapes("Rectangle 16").TextFrame.TextRange.Font.Size = 12
    
    With oPPTFile
        .SaveAs "C:\Users\A\Desktop\Report Format_VBA_Edited.pptx"
        .Close
    End With
    
    oPPTApp.Quit
    Set oPPTApp = Nothing
    Set oPPTFile = Nothing
    
End Sub

I did not use the check boxes, instead i looked for the letter "y" in column "C" to include as part of results.

I would change my Excel data so that every piece of text that is to be formatted differently on the pp presentation resided in its own cell.
I would also have the four results saved in four separate boxes on the presentation rather than one.

Hope this helps anyway

[PSL]

Thanks georgiboy! Let me try it out and let you know how it goes

Cheers,
Roy

[PSL]

Hi,

So I tried using this and got the following error:
Error1.jpg

This is what I did:

1. Removed the second slide from the ppt template and placed it on a folder in desktop
2. Created a macro and added the code in the excel
3. Replaced the checkboxes with 'y' in Column C

Could you maybe share the files you ended up using?

I'm happy to make the changes you suggested. Having a separate text box for each piece of data with different formatting makes sense. Although in the Detailed finding section, each finding would (I think) have to be in the same text box

Regards,
Roy

[georgiboy]

In the Visual Basic editor you will need to select:

Tools/References then select the checkbox for Microsoft PowerPoint

This will effectively allow Excel to talk to PP

[PSL]

Thank! This worked now. Well, it did lose the formatting but I guess I would have to keep them in individual text boxes. I'll try doing that next.

Had a noobish question - I noticed that each textbox in Powerpoint has a unique number. Which is cool, since without the excel grid I wasn't sure how one would address individual boxes / shapes etc. By playing around with the code, I figured that the date text box is "TextBox 5". Is there a way I can see the 'names' of each box (without trial and error)?

Will play around with this and report.

Thanks,
Roy

[georgiboy]

In PowerPoint select one of your text boxes.
When the heading "Format" appears in the top ribbon select it.
Then under the "Arrange" section in the ribbon click on "Selection Pane"
Now as you select shapes and text boxes you should see the name on the right hand side.

[PSL]

Thanks! Yes, this works now. I'm going to try and work it out with the different formatting by cell as copy pasting the entire cell is making powerpoint lose the formatting.

Yes, report back with the results.

Thanks!
Roy

[PSL]

Hi Georgiboy,

So I've figured most of the report out and managed to add a small VBA code to save the file name as the "report title_date". I'm still facing challenges in working with the detailed findings sections. I've done the following now:

1. Settled on the final format of the detailed findings sections and kept all elements with different formatting as separate text boxes (in both excel and powerpoint)
2. Grouped the components of each finding together (i.e. Finding 1, Summary 1 and details grouped and so on..)
3. In the excel, in addition to formatting each element in keeping it in separate cell, I've also removed the bullets. Trying to figure out a way to bring back the bullets after pasting the text from excel to powerpoint.
4. Can't figure how the code will now work, since the text is not in one cell anymore.

I've attached the updated ppt (template and final outcome) and the fresh excel I've created. Since all findings are now separate text boxes (as opposed to a single rectangle earlier) I had to select and distribute them vertically. I suppose that could be possible via a macro as well (I'm trying that as I write this :-))
Have I complicated this too much?

Regards,
Roy

[georgiboy]

With this i have only put the "y" next to the "Lorem Ipsum 1" row for it to bring in that detail.

Sub pp()

    Dim ppSl As PowerPoint.Slide
    Dim strPath As String
    Dim rCell As Range, x As Long


    strPath = "C:\Users\A\Desktop\Detailed Findings_VBA.pptx" '< your template location
    
    Set oPPTApp = CreateObject("PowerPoint.Application")
    oPPTApp.Visible = msoTrue
    Set oPPTFile = oPPTApp.Presentations.Open(strPath)
    Set ppSl = oPPTFile.Slides(1)
    
    With Sheet1
        For Each rCell In .Range("C2:C" & .Range("B" & Rows.Count).End(xlUp).Row).Cells
            If rCell.Value = "y" Then
                x = x + 1
                Select Case x
                Case 1
                    With ppSl.Shapes("Group 40")
                        .GroupItems(1).TextFrame.TextRange.Text = rCell.Offset(0, -1).Value
                        .GroupItems(2).TextFrame.TextRange.Text = rCell.Offset(1, -1).Value
                        .GroupItems(3).TextFrame.TextRange.Text = rCell.Offset(2, -1).Value
                    End With
                Case 2
                    With ppSl.Shapes("Group 41")
                        .GroupItems(1).TextFrame.TextRange.Text = rCell.Offset(0, -1).Value
                        .GroupItems(2).TextFrame.TextRange.Text = rCell.Offset(1, -1).Value
                        .GroupItems(3).TextFrame.TextRange.Text = rCell.Offset(2, -1).Value
                    End With
                Case 3
                    With ppSl.Shapes("Group 42")
                        .GroupItems(1).TextFrame.TextRange.Text = rCell.Offset(0, -1).Value
                        .GroupItems(2).TextFrame.TextRange.Text = rCell.Offset(1, -1).Value
                        .GroupItems(3).TextFrame.TextRange.Text = rCell.Offset(2, -1).Value
                    End With
                Case 4
                    With ppSl.Shapes("Group 43")
                        .GroupItems(1).TextFrame.TextRange.Text = rCell.Offset(0, -1).Value
                        .GroupItems(2).TextFrame.TextRange.Text = rCell.Offset(1, -1).Value
                        .GroupItems(3).TextFrame.TextRange.Text = rCell.Offset(2, -1).Value
                    End With
                End Select
            End If
        Next rCell
    End With
    
    With oPPTFile
        .SaveAs "C:\Users\A\Desktop\Detailed Findings_VBA_Edited.pptx"
        .Close
    End With
    
    oPPTApp.Quit
    Set oPPTApp = Nothing
    Set oPPTFile = Nothing
    
End Sub

Hope this helps

[PSL]

Thanks Georgiboy. Quite a create way to manage this.

Cheers,
Roy

[PSL]

This work like a charm. I only wish Powerpoint would let us record macros so beginners like me could record and learn like I do in Excel. Once the code ran I wanted to distribute the shapes vertically and tried recording that in Excel and replicating in Powerpoint. It does distribute but throws up an error and the distribution is haywire. This is what I tried:

 
With ppS1.Shapes.Range(Array("Group 40", "Group 41", "Group 42", "Group 43"))    
.Distribute msoDistributeVertically, msoFalse
 End With

Any idea where I'm going wrong?


Could you also point me towards basic VBA codes for powerpoint, since I can't just record a macro and figure. Things like Selecting shapes, Editing height and width of shapes etc?

Thanks a ton!

Regards,
Roy

[georgiboy]

Maybe something like this:

    
    Dim strShapes(0 To 4) As String    
    strShapes(0) = "Rectangle 15" ' Header box
    strShapes(1) = "Group 40"
    strShapes(2) = "Group 41"
    strShapes(3) = "Group 42"
    strShapes(4) = "Group 43"
    
    Set shpRng = ppSl.Shapes.Range(strShapes)
    shpRng.Distribute msoDistributeVertically, msoTrue

Hope this helps

[georgiboy]

I have only ever read one book about VBA and that’s “Excel 2007 VBA Programming for dummies” was a great book for the basics in Excel. The rest I learned from forums, google searches and experience within my workplace.

I used to (and still do) google my question with “VBA” at the end, you will be amazed at what you find.
make sure you always gain a full understanding of the code before you use it, this way you learn.

Sorry if it’s a bit lame but this is how I have learnt over the last 10 years.

[PSL]

Thanks man. This is been amazing learning for me. Just ordered the book you mentioned and hope I can learn a bit myself.

I changed the code a bit:

Dim strShapes(1 To 4) As String    
    
    strShapes(1) = "Group 40"
    strShapes(2) = "Group 41"
    strShapes(3) = "Group 42"
    strShapes(4) = "Group 43"
    
    Set shpRng = ppSl.Shapes.Range(strShapes)
    shpRng.Distribute msoDistributeVertically, msoFalse

msoTrue was distributing the text boxes across the vertical length of the slide. MsoFalse appears to distribute it keeping the top and bottom fixed. Which is more what I was looking for.

You have no idea how much time this is going to save me at work. If you're ever in New Delhi, do hit me up, would love to buy you a beer!

Cheers,
Roy

[georgiboy]

You are welcome, glad to be of help.

Been to India twice, it’s a long flight for me, Beautiful country.

[PSL]

Sorry to pick this one up again. But I've really been struggling with one enhancement I'm trying to make. I'm trying to insert an image on the the ppt.

I already have a picture placeholder on the Detailed Findings_VBA.pptx slide - "Picture Placeholder 2"

I'm trying to replace the placeholder with an image (say - "C:\Users\A\Desktop\Sample.jpg")

Could you suggest a way to do this?

Regards,
Roy

[Paul_Hossler]

Thank! This worked now. Well, it did lose the formatting but I guess I would have to keep them in individual text boxes. I'll try doing that next.

Had a noobish question - I noticed that each textbox in Powerpoint has a unique number. Which is cool, since without the excel grid I wasn't sure how one would address individual boxes / shapes etc. By playing around with the code, I figured that the date text box is "TextBox 5". Is there a way I can see the 'names' of each box (without trial and error)?

Will play around with this and report.

Thanks,
Roy

Suggest you use the Selection Pane to give the objects a meaningful name, instead of the generic 'Textbox 2873'

Selecting an object in the selection pane will select it on the slide

Capture.JPG

Capture2.JPG