Macro (VBA) that converts the contents selected in the range in Excel into the list initialization code in Python.

Overview

** VBA code that converts the contents of the selection on Excel to ** Python list initialization code and copies it to the clipboard. Generate a Python program with the first line of the selection as the ** variable name ** and the subsequent lines as the ** elements ** of the list, as shown in the following figure.

How to copy text to the clipboard is introduced in "How to copy a string that does not fail VBA to the clipboard". I used the code as it is.

code

Selection(Range)To a Python list (initialization code)

Sub Range2PythonList()

  If TypeName(Selection) <> "Range" Then
    Exit Sub
  End If
      
  sCol = Selection(1).Column               'Range start column
  eCol = Selection(Selection.Count).Column 'Range end column
  sRow = Selection(1).Row                  'Range start line
  eRow = Selection(Selection.Count).Row    'Range end line
      
  If sRow = eRow Then
    Exit Sub
  End If
      
  pCode = ""
  For c = sCol To eCol
    pCode = pCode & Cells(sRow, c).Value & "=["
    For r = sRow + 1 To eRow
      v = Cells(r, c).Value
      Select Case TypeName(v)
        Case "String"
          pCode = pCode & "'" & v & "'"
        Case "Empty", "Null", "Nothing"
          pCode = pCode & "None"
        Case "Date"
          pCode = pCode & "datetime.datetime(" _
                & Year(v) & "," _
                & Month(v) & "," _
                & Day(v) & "," _
                & Hour(v) & "," _
                & Minute(v) & "," _
                & Second(v) & ")"
        Case Else
          pCode = pCode & v
      End Select
      pCode = pCode & ","
    Next r
    pCode = Left(pCode, Len(pCode) - 1) & "]" & vbCrLf
  Next c
  
  Debug.Print pCode
  SetClip (pCode)

End Sub

'Copy text to clipboard
' https://info-biz.club/windows/vba/vba-set-clipboard.html
Sub SetClip(S As String)
  With CreateObject("Forms.TextBox.1")
    .MultiLine = True
    .Text = S
    .SelStart = 0
    .SelLength = .TextLength
    .Copy
  End With
End Sub

Execution method

Start Excel, start VBA Code Editor with "** Alt + F11 **", and select the target workbook by double-clicking.

A window for writing VBA code will open, so copy and paste the above code there.

Go back to Excel, ** select the range, then ** launch the macro with "** Alt + F8 **" and select "ThisWorkbook.Range2PythonList" to run it.

The contents of the selection are converted to the Python list initialization code and copied to the clipboard. After that, paste it into an appropriate editor and use it (add ʻimport datetime` if necessary).

Reference material

-How to copy VBA non-failing character string to clipboard @ Information Business Support Club -biz.club/)