** 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.
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
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).
-How to copy VBA non-failing character string to clipboard @ Information Business Support Club -biz.club/)
Recommended Posts