Variable codes in ppt from excel (Office 2003)

Topic

New Reply

I want to search through a sheet in Excel from A1 to B50. In Column A are the “codes” that are in powerpoint. In Column B is the text that needs to go in place of the “codes” in powerpt.

So A1 is [country], A2 is [year], A3 is
B1 is Canada, B2 is 2008, B3 is Red

The codes [country]. [year] and are in a powerpt file of 100 slides. I need the code to go through the ppt file and replace the codes with the text in Column B.

I have this code but don’t know exactly how to change it to fit my new excel sheet setup. Can someone help me? Thank you very much.

Sub PPTfind_replace()

Dim sld As PowerPoint.Slide
Dim shp As PowerPoint.Shape
Dim i As Integer
Dim code As String
Dim pptApp As PowerPoint.Application
Dim xlwsText As Excel.Worksheet
Dim myArray(14 To 22) As String

Set xlwsText = ActiveSheet
Set pptApp = GetObject(, “PowerPoint.Application”)

‘ fill array with first row in Excel
For i = 1 To 50
code = “[” & i & “]”
myArray(i) = xlwsText.Cells(i, 23)
For Each sld In pptApp.ActivePresentation.Slides
For Each shp In sld.Shapes
If shp.HasTextFrame Then _
shp.TextFrame.TextRange.Replace _
FindWhat:=code, ReplaceWhat:=myArray(i)
Next shp
Next sld
Next i
Set xlwsText = Nothing
Set pptApp = Nothing
MsgBox (“FindReplace is Finished”)
End Sub

Reply | Quote

Replies

Try this:

Sub PPTfind_replace()
Dim sld As PowerPoint.Slide
Dim shp As PowerPoint.Shape
Dim i As Integer
Dim code As String
Dim theval As String
Dim pptApp As PowerPoint.Application
Dim xlwsText As Worksheet

Set xlwsText = ActiveSheet
Set pptApp = GetObject(, “PowerPoint.Application”)

For i = 1 To 50
code = xlwsText.Cells(i, 1)
theval = xlwsText.Cells(i, 2)
For Each sld In pptApp.ActivePresentation.Slides
For Each shp In sld.Shapes
If shp.HasTextFrame Then _
shp.TextFrame.TextRange.Replace _
FindWhat:=code, ReplaceWhat:=theval
Next shp
Next sld
Next i
Set xlwsText = Nothing
Set pptApp = Nothing
MsgBox “FindReplace is Finished”
End Sub

Reply | Quote

This is working great! Is there a way to keep the same number formatting when it puts the numbers in the ppt file? Some are whole numbers, some are percents and it’s putting them in a decimals. Is there a way to copy the formatting so whatever i format in Excel will carry over to ppt. Thanks a lot.

Reply | Quote

Sorry for the delay, I was away for some days. Try changing the line

theval = xlwsText.Cells(i, 2)

to

theval = xlwsText.Cells(i, 2).Text

The Text property of a cell returns the value of the cell as it is displayed.

Reply | Quote