topic Re: Field Calculator VBA ArcGIS 9.3 - how to replace the last three characters of a string? in Geoprocessing Questions

Field Calculator VBA ArcGIS 9.3 - how to replace the last three characters of a string?

StephenDrew1 — Mon, 22 Sep 2014 15:32:08 GMT

Hello, I'm a newbie to VBA Script, so any of the below script could be wrong. I'm trying to use Field Calculator to replace any "_10" at the end of a filename (i.e. an entry in a field, in an attribute table) with "_11". Could anyone help me please? Many thanks in advance! sdrew1

Pre-logic VBA Script Code

Dim MyStr as string

Dim strLen as integer

strLen = Len([FILENAME])

If Right([FILENAME],3) = "_10" Then

MyStr = Left([FILENAME], strLen-3)) & "_11"

Else

MyStr = [FILENAME]

End if

Filename =

MyStr

Re: Field Calculator VBA ArcGIS 9.3 - how to replace the last three characters of a string?

TMgapinski — Mon, 22 Sep 2014 15:55:40 GMT

you'd have to give me more information, have you tried cut and paste?! Repeat the string?

Re: Field Calculator VBA ArcGIS 9.3 - how to replace the last three characters of a string?

StephenDrew1 — Mon, 22 Sep 2014 16:02:46 GMT

There are a lot of them in the database. E.g. the FileName field is populated with record entries such as "New_Town_Planning_Project_10" and I want to make all the thousends of records entries be "New_Town_Planning_Project_11".

Re: Field Calculator VBA ArcGIS 9.3 - how to replace the last three characters of a string?

KenBuja — Mon, 22 Sep 2014 16:39:40 GMT

You shouldn't declare the variables as a type. Try

Dim MyStr

Dim strLen

Re: Field Calculator VBA ArcGIS 9.3 - how to replace the last three characters of a string?

StephenDrew1 — Tue, 23 Sep 2014 07:45:26 GMT

Thanks Ken, for the help. It sadly still doesn't work (I get an error message that sayd 'Error Running VBA code: User interrupt.'). If you or anyone has any other ideas I would be a very grateful recipient! Steve

Re: Field Calculator VBA ArcGIS 9.3 - how to replace the last three characters of a string?

PeteCrosier — Tue, 23 Sep 2014 14:07:08 GMT

How about a Python one-liner?

Filename = !Filename![:-3] + !Filename![-3:].replace("_10", "_11")

Re: Field Calculator VBA ArcGIS 9.3 - how to replace the last three characters of a string?

StephenDrew1 — Tue, 23 Sep 2014 14:13:41 GMT

Hi Pete, thanks for the help but it's Arc 9.3 so only works in VBA - unless I'm missing something - I'm not used to Python (either). Thanks for trying to help - any clue about VBA would be very grateful!?

Re: Field Calculator VBA ArcGIS 9.3 - how to replace the last three characters of a string?

PeteCrosier — Tue, 23 Sep 2014 14:33:26 GMT

I think 9.3 should support Python if you run the Field Calculator geoprocessing tool, otherwise for VBA this should work:

Dim length

Dim result

length = Len([Filename])

If length > 2 Then

result = Left([Filename], length - 3) & Replace(Right([Filename], 3), "_10", "_11")

Else

result = [Filename]

End If

---

Filename =

result

Re: Field Calculator VBA ArcGIS 9.3 - how to replace the last three characters of a string?

StephenDrew1 — Wed, 24 Sep 2014 08:26:11 GMT

That's great Pete, thanks very much! That works brilliantly. And I think I'm learning - I can actually work out why you've written it as you have. The only bit I don't know about is why you might have written 'if the length is greater than 2'? Is that in case there are default blank fields? (If you don't want to answer this that's ok - I'm just learning and can't find links on the net to teach me about it...). Cheers Pete. Steve

Re: Field Calculator VBA ArcGIS 9.3 - how to replace the last three characters of a string?

JoeBorgione — Wed, 24 Sep 2014 10:58:30 GMT

A simple find and replace couldn't be used here?

Re: Field Calculator VBA ArcGIS 9.3 - how to replace the last three characters of a string?

StephenDrew1 — Wed, 24 Sep 2014 11:48:32 GMT

Hi Joe, Thanks for reply. I did consider that but I didn't want it to get rid of the '_100' or '_210' so I thought it might be easier the alternative route. Sorry, you weren't to know that there might be '210' etc as an alternative. Cheers again.

Re: Field Calculator VBA ArcGIS 9.3 - how to replace the last three characters of a string?

PeteCrosier — Wed, 24 Sep 2014 12:18:58 GMT

Yeah, in case there's a string that's blank or too short for the Left and Right to work properly.

As Joe said, you can probably use the Replace function better if it supports the 'normal':

Replace(string, find, replace[, start[, count[, compare]]])

.. VBA format in field calculator.

Re: Field Calculator VBA ArcGIS 9.3 - how to replace the last three characters of a string?

StephenDrew1 — Wed, 24 Sep 2014 12:23:02 GMT

Ah great, thanks very much! Good to see the Replace function written out, thats informative!

Re: Field Calculator VBA ArcGIS 9.3 - how to replace the last three characters of a string?

JoeBorgione — Thu, 25 Sep 2014 01:33:34 GMT

My mistake; just re-read the original post and I was thinking attribute value and you are renaming files.

Re: Field Calculator VBA ArcGIS 9.3 - how to replace the last three characters of a string?

StephenDrew1 — Thu, 25 Sep 2014 09:05:53 GMT

No problem - thanks for posting.