find and replace in vb6

[denby]

ok ill explain this in as little words as possible

i have an excel sheet at c:/excel.xls

it has 5000 rows and 10 collumms full of data number and text


at button click i want to load this entire excel sheet into a multiline textbox

once loaded i want to use some kind of find and replace funtion

find "hi" replace with "hello" for example


once it has update the text in textbox1 i want it to overwrite excel.xls

that is all...can anyone help?

 

[BlackDragon]

Never used VB6 but the solution isn't really tied to any particular language.

Is there a reason for loading all the Excel data into a multline text box and not a variable? If you are just replacing strings in an excel spreadsheet, could you not do it in-situ? i.e. not extracting all the data, processing, and putting it back?

Anyway, below are a couple of fairly simple solutions.

If you want to load all the data into a string, you will have to remember to delimit each cell in the string with a special character so that you can put it back into an Excel spreadsheet. If you have one big string you can just use a string function to do a replace. See here for some string functions

Alternatively you could load it into an array then you won't have to worry about delimiters. An example here for finding strings in an array. Once you have found a string you will have the index and thus it should be quite easy to replace the string in the array.

With either of the above approaches you would have to remember how to split the data back up so that they end up in the correct columns and rows.

 

[denby]

thanks that really helped

 

[Shadez]

how about just using the VBA function that searches the worksheet and replaces text?

Cells.Replace What:="hi", Replacement:="hello", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Found by recording macro and doing a find and replace.