Adding VBA into a Python script generated by ModelBuilder

Question

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

Answer 1

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
"""")

Answer 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