Mississippi Girl
Board Regular
- Joined
- Oct 27, 2005
- Messages
- 155
You know how someone asks you to do something, you do it, and then they ask you to do what they REALLY wanted in the first place? That's happening to me and now that I know what they REALLY want, I'm stuck. So, this is the story:
We have a presentation that is given every month. The presentation is currently populated by manually going to multiple Excel workbooks (with mulitple tabs) that are updated monthly and copy/pasting charts in to PowerPoint. The chart order is different from the order of the spreadsheets, the order of the charts cannot change and for whatever weird management reason, the worksheets cannot be reordered to match the order of the presentation.
I found a super cool macro the other day that I posted that goes through each chart object on each worksheet in a workbook and copy/pastes in to PowerPoint. It works great, but the copy/paste in to PowerPoint matches the sheet order; I need to be able to define the order by the worksheet tab name if that's possible. The other issue I'm running in to is the use of multiple workbooks....
So Excel and PowerPoint Super Heroes - what do you suggest as my best course of action? Make a master Excel file that contains all of my chart objects, then use my super cool macro mentioned above to create the integrated PowerPoint presentation? Or, would it be easier to use the excel macro on each of my four workbooks, then create a PowerPoint macro to integrate and reorder the slide presenation?
In case my ramblings above aren't clear, this is the desired result:
copy / paste chart objects from 4 different workbook files (with mulitple sheets in each) in to a PowerPoint presentation, in a specific order.
Any thoughts, ideas, comments are appreciated.
Here's the code I posted the other day that loops through each worksheet in a workbook and copy/pastes the chart objects in to a pre-determined PowerPoint file. I made some adjustments to the original code to get the formats, sizes, and to define the pre-determined PowerPoint presenation file name. Everything else was created by the author cited in the code:
We have a presentation that is given every month. The presentation is currently populated by manually going to multiple Excel workbooks (with mulitple tabs) that are updated monthly and copy/pasting charts in to PowerPoint. The chart order is different from the order of the spreadsheets, the order of the charts cannot change and for whatever weird management reason, the worksheets cannot be reordered to match the order of the presentation.
I found a super cool macro the other day that I posted that goes through each chart object on each worksheet in a workbook and copy/pastes in to PowerPoint. It works great, but the copy/paste in to PowerPoint matches the sheet order; I need to be able to define the order by the worksheet tab name if that's possible. The other issue I'm running in to is the use of multiple workbooks....
So Excel and PowerPoint Super Heroes - what do you suggest as my best course of action? Make a master Excel file that contains all of my chart objects, then use my super cool macro mentioned above to create the integrated PowerPoint presentation? Or, would it be easier to use the excel macro on each of my four workbooks, then create a PowerPoint macro to integrate and reorder the slide presenation?
In case my ramblings above aren't clear, this is the desired result:
copy / paste chart objects from 4 different workbook files (with mulitple sheets in each) in to a PowerPoint presentation, in a specific order.
Any thoughts, ideas, comments are appreciated.
Here's the code I posted the other day that loops through each worksheet in a workbook and copy/pastes the chart objects in to a pre-determined PowerPoint file. I made some adjustments to the original code to get the formats, sizes, and to define the pre-determined PowerPoint presenation file name. Everything else was created by the author cited in the code:
Code:
Option Explicit
'Both subs require a reference to Microsoft PowerPoint xx.x Object Library.
'where xx.x is your office version (11.0 = 2003, 12.0 = 2007 and 14.0 = 2010).
'Declaring the necessary Power Point variables (are used in both subs).
Dim pptApp As PowerPoint.Application
Dim pptPres As PowerPoint.Presentation
Dim pptSlide As PowerPoint.Slide
Dim pptSlideCount As Integer
Sub ChartsToPowerPoint()
'Exports all the chart sheets to a new power point presentation.
'It also adds a text box with the chart title.
'By Christos Samaras
'http://www.myengineeringworld.net
Dim ws As Worksheet
Dim intChNum As Integer
Dim objCh As Object
Dim x As Long
'Count the embedded charts.
For Each ws In ActiveWorkbook.Worksheets
intChNum = intChNum + ws.ChartObjects.Count
Next ws
'Check if there are chart (embedded or not) in the active workbook.
If intChNum + ActiveWorkbook.Charts.Count < 1 Then
MsgBox "Sorry, there are no charts to export!", vbCritical, "Ops"
Exit Sub
End If
'Open PowerPoint and create a new presentation.
Set pptApp = New PowerPoint.Application
Set pptPres = pptApp.Presentations.Open("C:\Users\kdrieber\Documents\CFO Template.pptx")
For x = pptPres.Slides.Count To 1 Step -1
pptPres.Slides(x).Delete
Next x
'Loop through all the embedded charts in all worksheets.
For Each ws In ActiveWorkbook.Worksheets
For Each objCh In ws.ChartObjects
Call pptFormat(objCh.Chart)
Next objCh
Next ws
'Loop through all the chart sheets.
For Each objCh In ActiveWorkbook.Charts
Call pptFormat(objCh)
Next objCh
'Show the power point.
pptApp.Visible = True
'Cleanup the objects.
Set pptSlide = Nothing
Set pptPres = Nothing
Set pptApp = Nothing
'Infrom the user that the macro finished.
MsgBox "Dude, you're done copying the charts!", vbInformation, "Done"
End Sub
Private Sub pptFormat(xlCh As Chart)
'Formats the charts/pictures and the chart titles/textboxes.
'By Christos Samaras
'http://www.myengineeringworld.net
Dim chTitle As String
Dim j As Integer
On Error Resume Next
'Get the chart title and copy the chart area.
chTitle = xlCh.ChartTitle.Text
xlCh.ChartArea.Copy
'Count the slides and add a new one after the last slide.
pptSlideCount = pptPres.Slides.Count
Set pptSlide = pptPres.Slides.Add(pptSlideCount + 1, ppLayoutBlank)
'Paste the chart and create a new textbox.
pptSlide.Shapes.PasteSpecial ppPasteJPG
If chTitle <> "" Then
pptSlide.Shapes.AddTextbox msoTextOrientationHorizontal, 12.24, 7.19, 694.75, 55.25
End If
'Format the picture and the textbox.
For j = 1 To pptSlide.Shapes.Count
With pptSlide.Shapes(j)
'Picture position.
If .Type = msoPicture Then
.Top = 60
.Left = 0
.Height = 342
.Width = 720
End If
'Text box position and format.
If .Type = msoTextBox Then
With .TextFrame.TextRange
.ParagraphFormat.Alignment = ppAlignCenter
.Text = chTitle
.Font.Name = "Tahoma (Headings)"
.Font.Size = 28
.Font.Color = -7658974
.Font.Bold = msoTrue
End With
End If
End With
Next j
End Sub