Find & Replace in Worksheet

[Pat Stanford]

The following script will do a substring find and replace in the active worksheet. It is preliminary, basic code with no error checking. It does not even check to see if you have a worksheet open. Yes, the worksheet does have to be open for editing before you run the script.

If there is enough interest, I may be willing to put some bells and whistles (like a single dialog box for the data entry). that would make it a little nicer.

{*********** Copy below this line ************}

Procedure FindandReplaceInWorksheet;

{This procedure gives two dialog boxes in the first enter the substring you want to search the}

{selected portion of a worksheet for. In the second, end the string you want to replace the first string with. This is highly untested code and is provided without warranty.}

{? 2009, Coviana, Inc - Pat Stanford pat@coviana.com}

{Licensed unde the GNU Lesser General Public License}

Var

S1,S2,S3 :String;

N1 ,N2,N3,N4,N5,N6,N7 :Integer;

H1 :Handle;

Begin

S1:=StrDialog('Enter Substring to Find','Loc1');

S2:=StrDialog('Enter Substring to Replace With', 'Loc2');

H1:=GetTopVisibleWS;

GetWSSelection(H1,N5,N5,N1,N2,N5,N3,N4,N5);

For N5:=N1 to N3 do

Begin

For N6:=N2 to N4 do

Begin

GetWSCellFormula(H1,N5,N6,S3);

N7:=Pos(S1,S3);

If N7 <> 0 then

Begin

Delete(S3,N7,Len(S1));

Insert(S2,S3,N7);

SetWSCellFormula(H1,N5,N6,N5,N6,S3);

end;

end;

end;

ClrMessage;

END;

Run(FindandReplaceInWorksheet);

{*********** and above this line ***************}

[ccroft]

This is a nice example of something that I did not know we could do.

"Vectortasks: where a dream you dare to dream really can come true."

Thanks Pat!

[Ozzie]

Pat,

Thanks again mate - just tried it and it appears to work well

What a great little tool - will save me hours and hours and hours - you cannot imagine how much time I will save

Will continue to check it and test it

thanks mate

Darrell

[Christian Fekete]

What language are you using for this code? Is there a place to learn how to write these programs?

Please advise

Thanks

CF

[Pat Stanford]

The scripting language in Vectorworks is called Vectorscript.

It is a derivative of Pascal. If you have never programmed before, I would start with a book on Pascal and do some simple programs there to get an idea of how the language works.

If you have programmed in some language before I would read the Vectorscript Language Guide that is included in the Vectorworks help.

Reading the threads in this forum will give you some ideas about what you can and can't do in VS. You should also read the Customization--Vectorworks Vectorscript forum for more information. That is also one of the best places to post questions on Vectorscript.

Don't be afraid to ask questions. We will be glad to help.

[unearthed]

Thanks very much for this Pat, very useful straight out of the box! And if you were going to add some bells and whistles I for one would be very keen - as long as it was compatible with VW2012

[RickR]

Nice and simple, Thanks

However it doesn't seem to do record fields for me. Is that by intent or am I off somewhere?