Search & Replace with OLE in Excel and PowerPoint documents

Question

I succesfully wrote this code to perform Search and Replace in MSWord documents. Now I have the need to do it for xls, xlsx, ppt and pptx too.

The idea is "parse all the file and every string you find replace it".

Imagine I want to replace the string "<MY_USER_NAME>" with the actual user name and it doesn't matter if this string is in Excel sheet 1, 2 or 3 or in a specific powerpoint page.

i googled for code but i just found small experiments, does anyone has more experience on this?

Thanks.

For Excel you need to use the [`Replace`](http://msdn.microsoft.com/en-us/library/ff194086.aspx) method of `Range`. For PowerPoint, see here: http://stackoverflow.com/questions/9811723/find-and-replace-text-in-powerpoint-2010-from-excel-2010-with-vba — David Heffernan, Jul 23 '13 at 07:31

score 2 · Answer 1 · answered Jul 23 '13 at 07:53

I've written the following procedure in my project to replace tags with values in Excel from Delphi program. It replaces all tags on all sheets

OutF - is a Excel Ole object, Slabel - is a tag to be replaced, SValue - is a value to replace the tag.

For example

OutF := CreateOleObject('Excel.Application' );
......

ExcelOutStr(OutF,'<MY_USER_NAME>','Value for MY User Name');

And here is the procedure:

procedure ExcelOutStr(OutF:Variant;SLabel,SValue:String);
var i,j:integer;
begin
  try
    OutF.DisplayAlerts := false;

    //To place a string with linebreaks into one Cell
    SValue:=StringReplace(SValue,#13#10,#10,[rfReplaceAll, rfIgnoreCase]);

    for j:=1 to OutF.Sheets.Count do
    begin
       OutF.WorkSheets[j].Select;

       if length(SValue)<250 then
       begin
              OutF.Cells.Replace(What:=Slabel, Replacement:=SValue, LookAt:=2,SearchOrder:=1, MatchCase:=False);
       end
       else
       begin
              //Excel .replace fails on string with length >250 so replace it in few steps
              i:=1;
              while i<=length(SValue) do
              begin
                 if i+200-1<length(SValue) then
                    OutF.Cells.Replace(What:=Slabel, Replacement:=Copy(SValue,i,200)+SLabel, LookAt:=2,SearchOrder:=1, MatchCase:=False)
                 else
                    OutF.Cells.Replace(What:=Slabel, Replacement:=Copy(SValue,i,200), LookAt:=2,SearchOrder:=1, MatchCase:=False);
                 i:=i+200;
              end;
       end;
    end;
    OutF.WorkSheets[1].Select;
  except
      on E : Exception do ShowMessage('Error: Lablel ['+SLabel+'] '+E.Message);
  end;

end;

Search & Replace with OLE in Excel and PowerPoint documents

1 Answers1