Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.
Keywords: Excel Replace string ReplaceFormat SearchFormat
VBA code below:
Columns("I:I").Select Selection.Replace What:="/Alenia", Replacement:=", Alenia", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=FalseMy code:
Trackfile = StrCat(AeroStd, "Summarydata030204.xls") ; Default file name if one does not exist in ini file OpenExcel(TrackFile) ; Get ready for Excel Global col changes XLapp.Visible = @TRUE ; Slow the process down - make the sheet visible colrange = xlApp.Columns("I:I") ; Define the area to be replaced Range = colrange.Select ; Select the area to have replacements Selection = XLapp.Selection xlbyRows = 1 xlPart = 2 ; False = 0 :ReplaceAll Selection.Replace(:: What="/Alenia", Replacement=", Alenia", LookAt= %xlPart%, SearchOrder=%xlByRows%, MatchCase=0, SearchFormat=0, ReplaceFormat=0) b = XLActive.save CloseExcel() ExitWorks okay, as afar as the Selection.Replace..Get an OLE exception.
Note: I am using an UDF to Open Excel.
Any ideas??
xlPart = 2 xlByRows = 1 objXL = ObjectOpen("Excel.Application") objXL.Visible = @TRUE objWorkbooks=objXL.WorkBooks objWorkbooks.Open("C:\Temp\Data files\test.xls") objColumns=objXL.Columns("A:A") objColumns.Select objSelection=objXL.Selection ;Define search format objFindFormat = objXL.FindFormat objFont = objFindFormat.Font objFont.Name = "Arial" objFont.FontStyle = "Regular" objFont.Size = 10 ;Define replace format objReplaceFormat = objXL.ReplaceFormat objFont = objReplaceFormat.Font objFont.Name = "Arial" objFont.FontStyle = "Bold" objFont.Size = 10 objSelection.Replace(:: What="A2", Replacement=", Alenia", LookAt=%xlPart%, SearchOrder=%xlByRows%,MatchCase=0, SearchFormat=objFindFormat ,ReplaceFormat=objReplaceFormat ) ObjectClose(objReplaceFormat) ObjectClose(objFindFormat) ObjectClose(objFont) ObjectClose(objSelection) ObjectClose(objColumns) ObjectClose(objWorkbooks) ObjectClose(objXL) exit
Article ID: W15632
File Created: 2003:05:13:11:29:18
Last Updated: 2003:05:13:11:29:18