Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-10-2014, 09:08 AM
amparete13 amparete13 is offline Find and Replace Macro Windows 7 64bit Find and Replace Macro Office 2013
Novice
Find and Replace Macro
 
Join Date: Mar 2014
Posts: 4
amparete13 is on a distinguished road
Default Find and Replace Macro

Hi, Nice forum!



I am trying to translate some presentations, so I would like to make a macro, with several words that I do know they are in all presentations, so that it translate those words automatically.

I have been looking at https://www.msofficeforums.com/power...place-vba.html but it does have the problem that it does not difference between capital letters and not. For example if I want to replace "How" but not "how" it does not work properly.

Can anyone help me to solve this problem?

Thanks in advanced,
Reply With Quote
  #2  
Old 03-10-2014, 09:25 AM
JohnWilson JohnWilson is offline Find and Replace Macro Windows 7 64bit Find and Replace Macro Office 2010 32bit
Programmer
 
Join Date: Nov 2008
Location: UK
Posts: 1,912
JohnWilson has a spectacular aura aboutJohnWilson has a spectacular aura about
Default

Where you see

Replacewhat:=ReplaceString, WholeWords:=True) (several places)

Use

Replacewhat:=ReplaceString, WholeWords:=True, MatchCase:=msoTrue)

The replace will only happen if the case is exactly the same.

There'a a typo in Shyams code BTW

Col should be Cols (2 places)
__________________
Microsoft PowerPoint MVP 2007-2023
Free Advanced PowerPoint Tips and Tutorials
Reply With Quote
  #3  
Old 03-10-2014, 10:26 AM
amparete13 amparete13 is offline Find and Replace Macro Windows 7 64bit Find and Replace Macro Office 2013
Novice
Find and Replace Macro
 
Join Date: Mar 2014
Posts: 4
amparete13 is on a distinguished road
Default

Perfect! That it was I was looking for. Thanks. I did add MatchCase in all the cases where WholeWords was in and now it does works.

I add the code here just in case anyone wants it. Thanks to Shyams ( http://skp.mvps.org/ppt00025.htm )

Now I can change different words at the same time. It is a bit slow, but much faster than manually.

Thanks

Quote:
Sub GlobalFindAndReplace()
Dim oPres As Presentation
Dim oSld As Slide
Dim oShp As Shape
Dim FindWhat As String
Dim ReplaceWith As String

FindWhat = "Motivação"
ReplaceWith = "Motivación"
For Each oPres In Application.Presentations
For Each oSld In oPres.Slides
For Each oShp In oSld.Shapes
Call ReplaceText(oShp, FindWhat, ReplaceWith)
Next oShp
Next oSld
Next oPres

FindWhat = "Crescimento"
ReplaceWith = "Crecimiento"
For Each oPres In Application.Presentations
For Each oSld In oPres.Slides
For Each oShp In oSld.Shapes
Call ReplaceText(oShp, FindWhat, ReplaceWith)
Next oShp
Next oSld
Next oPres

End Sub

Sub ReplaceText(oShp As Object, FindString As String, ReplaceString As String)
Dim oTxtRng As TextRange
Dim oTmpRng As TextRange
Dim I As Integer
Dim iRows As Integer
Dim iCols As Integer
Dim oShpTmp As Shape

' Always include the 'On error resume next' statememt below when you are working with text range object.
' I know of at least one PowerPoint bug where it will error out - when an image has been dragged/pasted
' into a text box. In such a case, both HasTextFrame and HasText properties will return TRUE but PowerPoint
' will throw an error when you try to retrieve the text.
On Error Resume Next
Select Case oShp.Type
Case 19 'msoTable
For iRows = 1 To oShp.Table.Rows.Count
For iCols = 1 To oShp.Table.Rows(iRows).Cells.Count
Set oTxtRng = oShp.Table.Rows(iRows).Cells(iCols).Shape.TextFram e.TextRange
Set oTmpRng = oTxtRng.Replace(FindWhat:=FindString, Replacewhat:=ReplaceString, WholeWords:=True, MatchCase:=msoTrue)
Do While Not oTmpRng Is Nothing
Set oTmpRng = oTxtRng.Replace(FindWhat:=FindString, _
Replacewhat:=ReplaceString, _
After:=oTmpRng.Start + oTmpRng.Length, _
WholeWords:=True, MatchCase:=msoTrue)
Loop
Next
Next
Case msoGroup 'Groups may contain shapes with text, so look within it
For I = 1 To oShp.GroupItems.Count
Call ReplaceText(oShp.GroupItems(I), FindString, ReplaceString)
Next I
Case 21 ' msoDiagram
For I = 1 To oShp.Diagram.Nodes.Count
Call ReplaceText(oShp.Diagram.Nodes(I).TextShape, FindString, ReplaceString)
Next I
Case Else
If oShp.HasTextFrame Then
If oShp.TextFrame.HasText Then
Set oTxtRng = oShp.TextFrame.TextRange
Set oTmpRng = oTxtRng.Replace(FindWhat:=FindString, _
Replacewhat:=ReplaceString, WholeWords:=True, MatchCase:=msoTrue)
Do While Not oTmpRng Is Nothing
Set oTmpRng = oTxtRng.Replace(FindWhat:=FindString, _
Replacewhat:=ReplaceString, _
After:=oTmpRng.Start + oTmpRng.Length, _
WholeWords:=True, MatchCase:=msoTrue)
Loop
End If
End If
End Select
End Sub
Reply With Quote
  #4  
Old 03-11-2014, 05:29 AM
amparete13 amparete13 is offline Find and Replace Macro Windows 7 64bit Find and Replace Macro Office 2013
Novice
Find and Replace Macro
 
Join Date: Mar 2014
Posts: 4
amparete13 is on a distinguished road
Default

I have tried with 50 words at the same time and it does collapse.

¿Is there any way to make the code cleaner so that I can change at the same time a big amount of words?

Thanks!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find and Replace Macro Is the following too complex for find/replace macro? bertietheblue Word VBA 12 11-04-2013 05:35 PM
Find - Replace Macro using a table list mdw Word 0 08-01-2013 04:36 PM
Find and Replace Macro Find and Replace Format macro issue Jack Word VBA 2 12-12-2012 09:24 PM
macro or find/replace JamesVenhaus Word 2 02-27-2012 03:34 PM
Find and Replace Macro - A Better Way Tribos Word VBA 0 10-08-2008 03:22 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:02 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft