How to write a Python file using VBA?

Question

I want to use excel VBA to edit a script file in python then to be run in ArcGis. First I'm open to any easier way but so far I'm having trouble making a multiple line script with my VBA sub!

& vbCrLf & doesn't work in the command Fileout.Write. When I open my script file everything is on the same line.

neither is & Char(34) & working.

Private Sub CommandButton4_Click()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim path As String
    path = Application.ActiveWorkbook.path


    Dim Fileout As Object
    Set Fileout = fso.CreateTextFile(path & "\" & "test2.py", True, True)
    Fileout.Write "import arcpy & vbCrLf & lyr = arcpy.mapping.Layer( & Char(34) & limits & Char(34) &) & vbCrLf & lyr.visible = True & vbCrLf & arcpy.RefreshActiveView()'"
    Fileout.Close
End Sub

As written your file contents will be import arcpy & vbCrLf & lyr = arcpy.mapping.Layer( & Char(34) & limits & Char(34) &) & vbCrLf & lyr.visible = True & vbCrLf & arcpy.RefreshActiveView()' because you've enclosed all that vba code in double quotes. — Steven Rumbalski
– Steven Rumbalski, Commented Nov 9, 2017 at 18:35
Close your strings with a double quote before the & and re-open them after. Working on answer... — Excelosaurus
– Excelosaurus, Commented Nov 9, 2017 at 18:39

Steven Rumbalski · Accepted Answer · 2017-11-09 19:08:21Z

5

The error is here:

Fileout.Write "import arcpy & vbCrLf & lyr = arcpy.mapping.Layer( & Char(34) & limits & Char(34) &) & vbCrLf & lyr.visible = True & vbCrLf & arcpy.RefreshActiveView()'"

This doesn't give the expected output because you have quotes around the VBA code that you want to be evaluated. Fixing the quotes gives

Fileout.Write "import arcpy" & vbCrLf & "lyr = arcpy.mapping.Layer(" & Char(34) & limits & Char(34) & ")" & vbCrLf & "lyr.visible = True" & vbCrLf & "arcpy.RefreshActiveView()"

Let's use some line continuations (_) to make this code more legible:

Fileout.Write "import arcpy" & vbCrLf _
            & "lyr = arcpy.mapping.Layer(" & Char(34) & limits & Char(34) & ")" & vbCrLf _
            & "lyr.visible = True" & vbCrLf _
            & "arcpy.RefreshActiveView()"

Char(34) can be accomplished via doubled quotes ("" inside a quotes evaluates to a double quote):

Fileout.Write "import arcpy" & vbCrLf _
            & "lyr = arcpy.mapping.Layer(""" & limits & """)" & vbCrLf _
            & "lyr.visible = True" & vbCrLf _
            & "arcpy.RefreshActiveView()"

You can use multiple WriteLine in place of the Write gets rid of the & vbCrLf _ on each line:

Fileout.WriteLine "import arcpy"
Fileout.WriteLine "lyr = arcpy.mapping.Layer(""" & limits & """)"
Fileout.WriteLine "lyr.visible = True"
Fileout.WriteLine "arcpy.RefreshActiveView()"

edited Nov 9, 2017 at 19:08

answered Nov 9, 2017 at 18:42

Steven Rumbalski

45.7k10 gold badges96 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Max Bridge Over a year ago

Thanx I like the WriteLine approach!

Excelosaurus · Accepted Answer · 2017-11-09 18:42:54Z

0

Here goes: you were on the right track but just stumbled when dealing with the string building.

You want to concatenate bits of strings surrounded by double quotes, using the & operator. When you need double quotes within a string, just double them up.

Private Sub CommandButton4_Click()
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim path As String
    path = Application.ActiveWorkbook.path


    Dim Fileout As Object
    Set Fileout = fso.CreateTextFile(path & "\" & "test2.py", True, True)
    Fileout.Write "import arcpy" & vbCrLf & "lyr = arcpy.mapping.Layer(""limits"")" & vbCrLf & "lyr.visible = True" & vbCrLf & "arcpy.RefreshActiveView()'"
    Fileout.Close
End Sub

edited Nov 9, 2017 at 18:42

answered Nov 9, 2017 at 18:41

Excelosaurus

2,8591 gold badge17 silver badges20 bronze badges

1 Comment

bruno desthuilliers Over a year ago

giving a solution is fine, explaining the problem before is way better...

Collectives™ on Stack Overflow

How to write a Python file using VBA?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related