Results 1 to 15 of 18

20050806, 01:07 #1
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Charts size and position VBA (Excel and ppt 2003)
I have a ppt file with 100 slides. It is liked to an excel file with about 90 chart sheets in it. I have positioned everything in the ppt file the way i want it as far as size and position on the slide. Now I have 10 more ppt files and i don't want to manually position and size all of them again. They would get sized and positioned just like the one i just finished.
How can I code a macro to go through the first one and save the positioning of each chart on the slide and then use this information for the other reports? Thank you for the help.

20050806, 01:31 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Charts size and position VBA (Excel and ppt 2003)
Do the slides with charts have other elements such as a title box, or are the charts the sole occupants of those slides?

20050806, 01:33 #3
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Charts size and position VBA (Excel and ppt 2003)
yes, unfortunately there are other elements such as footnote, title, page number. The chart usually sits in the middle of the slide but the "size" of it is different on each slide. There are pies, horiz and vert bar graphs, and stacked bar graphs.

20050806, 01:41 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Charts size and position VBA (Excel and ppt 2003)
Next question: have the other 10 presentations already been populated, i.e. the macro "only" needs to fix the position and size of the charts, or should the macro get the charts from somewhere and place them on the slides?

20050806, 01:44 #5
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Charts size and position VBA (Excel and ppt 2003)
The charts are already in the ppt files.

20050806, 01:55 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Charts size and position VBA (Excel and ppt 2003)
This should do it, hopefully. Don't forget to substitute the correct names for the presentations. P1.ppt stands for the one where the charts have already been positioned/sized correctly.
Sub FixCharts()
Dim ppt1 As Presentation
Dim ppt2 As Presentation
Dim i As Integer
Dim j As Integer
Dim k As Integer
Set ppt1 = Presentations("P1.ppt")
Set ppt2 = Presentations("P2.ppt")
For i = 1 To ppt1.Slides.Count
For j = 1 To ppt1.Slides(i).Shapes.Count
If ppt1.Slides(i).Shapes(j).Type = msoLinkedOLEObject Then
For k = 1 To ppt2.Slides(i).Shapes.Count
If ppt2.Slides(i).Shapes(k).Type = msoLinkedOLEObject Then
Exit For
End If
Next k
ppt2.Slides(i).Shapes(k).Top = ppt1.Slides(i).Shapes(j).Top
ppt2.Slides(i).Shapes(k).Left = ppt1.Slides(i).Shapes(j).Left
ppt2.Slides(i).Shapes(k).Height = ppt1.Slides(i).Shapes(j).Height
ppt2.Slides(i).Shapes(k).Width = ppt1.Slides(i).Shapes(j).Width
Exit For
End If
Next j
Next i
Set ppt2 = Nothing
Set ppt1 = Nothing
End Sub

20050806, 18:19 #7
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Charts size and position VBA (Excel and ppt 2003)
wow! woopie, this works wonderfully. Saved me TONS of time. Can't thank you enough!

20050806, 18:49 #8
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Charts size and position VBA (Excel and ppt 2003)
This works great. I forgot that there are several instances where 2 charts (ole objects) are on the same slide. How can I modify the macro to take that into account? Thank you

20050806, 21:15 #9
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Charts size and position VBA (Excel and ppt 2003)
Take out the Exit For
<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

20050806, 21:43 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Charts size and position VBA (Excel and ppt 2003)
Is there a way to identify the charts? We'd need to be able to determine which chart in the master presentation corresponds to which chart in the target presentation.

20050807, 01:17 #11
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Charts size and position VBA (Excel and ppt 2003)
each chart is named the slide number plus an "A" if there is 1 chart and a "B" if there is a second chart. So if slide 25 has 2 charts they are named 25A and 25B

20050807, 09:57 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Charts size and position VBA (Excel and ppt 2003)
OK, then you can check for those names within the two For ... Next loops:
If ppt1.Slides(i).Shapes(j).Type = msoLinkedOLEObject Then
If ppt1.Slides(i).Shapes(j).Name = i & "A" Or ppt1.Slides(i).Shapes(j).Name = i & "B" Then
...
End If
End If
You should be able to work out the details yourself.

20050808, 17:31 #13
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Charts size and position VBA (Excel and ppt 2003)
Hi Hans,
I can't figure out where to find the "name" of the shape in ppt. I was trying to debug the below but i cannot figure out either in Excel or ppt how I can "see" the name of the chart
Sub MatchSizePositionTemplatePPT()
Dim ppt1 As Presentation
Dim ppt2 As Presentation
Dim i As Integer
Dim j As Integer
Dim k As Integer
'On Error Resume Next
Set ppt1 = Presentations("cigarettes.ppt")
Set ppt2 = Presentations("yogurt.ppt")
For i = 1 To ppt1.Slides.Count
For j = 1 To ppt1.Slides(i).Shapes.Count
If ppt1.Slides(i).Shapes(j).Type = msoLinkedOLEObject Then
If ppt1.Slides(i).Shapes(j).Name = i & "A" Or ppt1.Slides(i).Shapes(j).Name = i & "B" Then
For k = 1 To ppt2.Slides(i).Shapes.Count
If ppt2.Slides(i).Shapes(k).Type = msoLinkedOLEObject Then
Exit For
End If
Next k
ppt2.Slides(i).Shapes(k).Top = ppt1.Slides(i).Shapes(j).Top
ppt2.Slides(i).Shapes(k).Left = ppt1.Slides(i).Shapes(j).Left
ppt2.Slides(i).Shapes(k).Height = ppt1.Slides(i).Shapes(j).Height
ppt2.Slides(i).Shapes(k).Width = ppt1.Slides(i).Shapes(j).Width
Exit For
End If
End If
Next j
Next i
Set ppt2 = Nothing
Set ppt1 = Nothing
End Sub

20050808, 18:56 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Charts size and position VBA (Excel and ppt 2003)
I already gave you the way to get the name of a shape: ppt1.Slides(i).Shapes(j).Name <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

20050809, 12:46 #15
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Charts size and position VBA (Excel and ppt 2003)
Now I'm trying to move the ole object to the back so the text box is in the front on the ppt file. I've tried this....
Sub FixCharts()
Dim ppt1 As Presentation
Dim ppt2 As Presentation
Dim i As Integer
Dim j As Integer
Dim k As Integer
On Error Resume Next
Set ppt1 = Presentations("cigarettes.ppt")
Set ppt2 = Presentations("Yogurt 2.ppt")
For i = 1 To ppt1.Slides.Count
For j = 1 To ppt1.Slides(i).Shapes.Count
If ppt1.Slides(i).Shapes(j).Type = msoLinkedOLEObject Then
For k = 1 To ppt2.Slides(i).Shapes.Count
If ppt2.Slides(i).Shapes(k).Type = msoLinkedOLEObject Then
' x = ppt2.Slides(i).Shapes(k).Name
' MsgBox x
Exit For
End If
Next k
ppt2.Slides(i).Shapes(k).Top = ppt1.Slides(i).Shapes(j).Top
ppt2.Slides(i).Shapes(k).Left = ppt1.Slides(i).Shapes(j).Left
ppt2.Slides(i).Shapes(k).Height = ppt1.Slides(i).Shapes(j).Height
ppt2.Slides(i).Shapes(k).Width = ppt1.Slides(i).Shapes(j).Width
ppt2.Slides(i).Shapes(k).ZOrder msoSendBack
If Not ppt2.Slides(i).Shapes(k + 1) Then
ppt2.Slides(i).Shapes(k + 1).Top = ppt1.Slides(i).Shapes(j + 1).Top
ppt2.Slides(i).Shapes(k + 1).Left = ppt1.Slides(i).Shapes(j + 1).Left
ppt2.Slides(i).Shapes(k + 1).Height = ppt1.Slides(i).Shapes(j + 1).Height
ppt2.Slides(i).Shapes(k + 1).Width = ppt1.Slides(i).Shapes(j + 1).Width
End If
Exit For
End If
Next j
Next i
Set ppt2 = Nothing
Set ppt1 = Nothing
MsgBox ("slides match")
End Sub
Is there a different method to use because this doesn't work? Thank you for the help.