3

I have created a model that does a calculate field and have used VBA in the code block. But when I export it to Python, it doesn't work. Here is the VBA code:

new_value = [HDATA_N_LAREF_ACTIVITY_TEXT] 
if len(new_value) > 100 then
    desc = Left(new_value,100)
    desc = Left( desc, InStrRev( desc, " ") - 1) & " " & "..."
end if

When i exported it it came out as this....

# Process: Calculate Field...
gp.CalculateField_management(DWWEB_FINAL, "DESCRIPTION", "desc", "VB", "new_value = [HDATA_N_LAREF_ACTIVITY_TEXT] 
if len(new_value) > 100 then
    desc = Left(new_value,100)
    desc = Left( desc, InStrRev( desc, \" \") - 1) & \" \" & \"...\"
end if
")

EXTRA INFO:

I have had a look at a previous script that was created (before I started this role), where within the Python script the VBA code is all on one line and contains "\n", where there is a new line within the code, so it should end up like this:

gp.CalculateField_management(myTempWorkspace + os.sep + "DWWEB_Joined", "DESCRIPTION", "desc","VB","new_value = [HDATA_N_LAREF_ACTIVITY_TEXT]\\nIf len(new_value) > 100 then \\ndesc = Left(new_value,100)\\ndesc = Left( desc, InStrRev( desc, " ") - 1) & " " & ""...""\\nend if\\n\\n\\n")

but in PythonWin it gives me the following error:

>>> Unhandled exception while debugging...
Traceback (most recent call last):
  File "D:\ScheduledTasks\Scripts\DES_HYMS_Streetworks_v1.py", line 255, in ?
    gp.CalculateField_management(myTempWorkspace + os.sep + "DWWEB_Joined", "DESCRIPTION", "desc","VB","new_value = [HDATA_N_LAREF_ACTIVITY_TEXT]\\nIf len(new_value) > 100 then \\ndesc = Left(new_value,100)\\ndesc = Left( desc, InStrRev( desc, " ") - 1) & " " & ""...""\\nend if\\n\\n\\n")
RuntimeError: 
(Microsoft VBScript compilation error) Syntax error
Failed to execute (CalculateField).

But that doesn't run, so what do i have to do to get the code block to work?

Thanks

  • Are you getting an error? – Chad Cooper May 10 '12 at 1:02
  • Question updated. – MapMan May 10 '12 at 8:40
  • 1
    @Halil Siddique, What about just exporting the model to VBS and running that as a script? – artwork21 May 10 '12 at 11:38
3

Try putting your code block in a verbatim triple quote string:

gp.CalculateField_management(DWWEB_FINAL, "DESCRIPTION", "desc", "VB", """
new_value = [HDATA_N_LAREF_ACTIVITY_TEXT] 
if len(new_value) > 100 then
    desc = Left(new_value,100)
    desc = Left( desc, InStrRev( desc, " ") - 1) & " " & "..."
end if
"""")
2

It appears you need to change your conditional statement a bit to fit into python schema.

  1. Remove the 'then' in your if statement.
  2. Add ':' at the end of 'if len(new_value) > 100:'
  3. Remove 'end if'

In addition you will have to update the 'Left' method, below are some helpful links for string manipulation.

String Methods

Slice function

  • I was hoping to keep the code block to VBA rather then changing it to python, as i have got it to work, within the model. – MapMan May 10 '12 at 12:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.