How to write a Python file using VBA?

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

Solution

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