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.

無題.png

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.

f1.png

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.

f2.png

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

f3.png

Reference material

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

Recommended Posts

Macro (VBA) that converts the contents selected in the range in Excel into the list initialization code in Python.
Get the EDINET code list in Python
Get the value selected in Selenium Python VBA pull-down
Manipulate the clipboard in Python and paste the table into Excel
The story that 2D list replacement did not work in python
[Python] A program that rotates the contents of the list to the left
OR the List in Python (zip function)
[Python] Sort the list of pathlib.Path in natural sort
Make a copy of the list in Python
Read Excel name / cell range with Python VBA
Python code that removes contiguous spaces into one
About the matter that the contents of Python print are not visible in docker logs
A memo that implements the job of loading a GCS file into BigQuery in Python
How to find the first element that matches your criteria in a Python list