PDA

View Full Version : [SOLVED] Copy from Excel to Powerpoint to create a report



PSL
03-02-2018, 05:21 AM
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
03-02-2018, 07:11 AM
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
03-02-2018, 07:33 AM
Thanks georgiboy! Let me try it out and let you know how it goes

Cheers,
Roy

PSL
03-02-2018, 07:47 AM
Hi,

So I tried using this and got the following error:
21732

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
03-02-2018, 07:51 AM
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
03-02-2018, 08:28 AM
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
03-02-2018, 08:58 AM
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
03-02-2018, 12:26 PM
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
03-02-2018, 12:56 PM
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
03-03-2018, 06:37 AM
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
03-03-2018, 07:01 AM
Thanks Georgiboy. Quite a create way to manage this.

Cheers,
Roy

PSL
03-03-2018, 10:23 AM
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
03-04-2018, 07:17 AM
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
03-04-2018, 08:02 AM
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
03-04-2018, 11:53 AM
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
03-04-2018, 01:12 PM
You are welcome, glad to be of help.

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

PSL
03-05-2018, 11:41 AM
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
03-05-2018, 02:05 PM
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

21748

21749