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
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