PDA

View Full Version : Automating Footer - Frustrating the heck out of me



Sirius Black
08-04-2006, 01:41 PM
Hi all:
I'm trying to automated a text box for a few slides. Sounds easy enough, and I can automate part of the text box; however I run into a problem when my loop makes its second pass. I've been racking my brains on this one, and finally just want to :banghead: repeatedly.
Here is the code that I have set up:



'Text Box Collection 1a
SldID = 4
For SldID = SldID To 10 Step 3
'Copy Range in Excel
WsAggChrt.Range("M2").Copy
'Paste to PowerPoint TextBox
With PPTPres.Slides(SldID).Shapes("Text Box 12").TextFrame.TextRange
.Characters(Start:=17, Length:=26).Paste
End With
With PPTPres.Slides(SldID).Shapes("Text Box 12").TextFrame.TextRange.Font
.Name = "Times New Roman"
.Size = 14
.Bold = msoTrue
End With
WsAggChrt.Range("M3").Copy
If PPTPres.Slides(SldID).Shapes("Text Box 12").TextFrame.TextRange.Characters(50) > "" Then
With PPTPres.Slides(SldID).Shapes("Text Box 12").TextFrame.TextRange
.Characters(Start:=51, Length:=27).Paste
End With
Else
With PPTPres.Slides(SldID).Shapes("Text Box 12").TextFrame.TextRange
.Characters(Start:=52, Length:=26).Paste
End With
End If
With PPTPres.Slides(SldID).Shapes("Text Box 12").TextFrame.TextRange.Font
.Name = "Times New Roman"
.Size = 14
.Bold = msoTrue
End With
Next SldID


The issue comes in the form of the range that is being copied from Excel. It's a variable range, which is throwing my automation off. The range in Excel will either be 26 or 27 characters long depending on the the number of weeks the model is looking at. Maybe because it's Friday afternoon, but I can't for the life of me out to factor in the variable length of the Excel range.
Here is what the Text Box looks like to start:

Total US
2004 - 52 Weeks Ending 12/31/2004
2005 - 52 Weeks Ending 12/31/2005
2006 - 52 Weeks Ending 12/31/2006

The Excel Ranges will replace 52 Weeks Ending 12/31/2004, 52 Weeks Ending 12/31/2005 & 52 Weeks Ending 12/31/2006, while keeping 2004 - , 2005 - , & 2006 - .


Thanks,

Sirius

Sirius Black
08-04-2006, 02:28 PM
So after knocking my head around my desk for a bit, I had an epiphany. Why not use a find and replace throughout the presentation?

Here is the code that I am using:



Dim PPTApp As PowerPoint.Application
Dim PPTPres As PowerPoint.Presentation
Dim PPTSld As PowerPoint.Slide
Dim SldID As Integer
Dim WbAgg As Workbook, WsAggChrt As Worksheet
Dim Period1 As String
Dim Period2 As String
Dim Period3 As String

Set PPTApp = GetObject(, "PowerPoint.Application")
Set PPTPres = PPTApp.ActivePresentation
PPTApp.ActiveWindow.ViewType = ppViewSlide
Set WbAgg = ThisWorkbook
Set WsAggChrt = WbAgg.Sheets("Chart Data")
Set Period1 = WsAggChrt.Range("M2")
Set Period2 = WsAggChrt.Range("M3")
Set Period3 = WsAggChrt.Range("M4")

PPTPres.Slides.Range.Shapes.Range.TextFrame.TextRange.Replace(FindWhat:="52 Weeks Ending 12/31/2004", Replacewhat:="Period1"))

End Sub


In theory this should work. Maybe I'm missing something? Anyway, I can't even get out of the VB Editor, as I keep getting a Compile Error Expected: End of Statement

Any Ideas how to correct???

Thanks,

Sirius