电子表格处理超多字段的SQL

 

 

Sub t()

'
' 宏1 宏
'
' 快捷键: Ctrl+k
'

Dim srange As Range
Dim newrows As Integer
Dim rows As Integer
Dim resultSheet As Worksheet
Dim fieldname As String
Dim fieldnames As String

Dim comment As String
Dim comments As String

Dim cols As Integer

Dim cursheet As Worksheet

cols = 5

Dim iscomment As Boolean

iscomment = True

Set cursheet = ActiveSheet


Set srange = Selection

rows = srange.rows.Count

Set resultSheet = ActiveWorkbook.Sheets.Add()


cursheet.Activate


newrows = Int(rows / cols) + (IIf((rows Mod cols) > 0, 1, 0))
comments = "-- "

With srange
For i = 1 To rows
fieldnames = fieldnames & srange.Cells(i, 1).Value & ","

comments = comments & srange.Cells(i, 2).Value & ","

If i < rows Then
If i Mod cols = 0 Then

If iscomment = True Then

resultSheet.Cells((i \ cols) * 2 - 1, 1).Value = comments

resultSheet.Cells((i \ cols) * 2, 1).Value = fieldnames

Else
resultSheet.Cells((i \ cols), 1).Value = fieldnames
End If
fieldnames = ""
comments = "-- "
End If
Else

If iscomment = True Then

resultSheet.Cells((i \ cols) * 2 + 1, 1).Value = comments

resultSheet.Cells((i \ cols) * 2 + 2, 1).Value = fieldnames

Else
resultSheet.Cells((i \ cols) + 2, 1).Value = fieldnames
End If

End If

Next i

End With


End Sub

　　

选择待处理的数据，执行后