|
Sub test()
Dim p$, f$, A, i&, r&
Application.ScreenUpdating = False
Cells.ClearContents
p = ThisWorkbook.Path & "\"
f = Dir(p & "*.txt")
Do While f <> ""
'读文本
Open p & f For Input As #1
A = Split(StrConv(InputB(LOF(1), 1), vbUnicode), vbCrLf)
Close #1
'写入工作表
r = Cells(65536, 3).End(xlUp).Row + 1
Cells(r, 3).Resize(UBound(A), 1) = Application.Transpose(A)
'添加日期
f = Left(Right(f, 12), 8)
Range(Cells(r, 2), Cells(65536, 3).End(xlUp).Offset(0, -1)) = f
'标记标题行
i = i + 1
If i <> 1 Then Cells(r, 1) = 1
f = Dir
Loop
Range("a:a").SpecialCells(xlCellTypeConstants).EntireRow.Delete '删除其它标题行
Rows(1).Delete '删除(无数据的)首行
Columns(1).Delete '删除(辅助的)首列
Columns(2).TextToColumns Comma:=True '分列文本中的数据
'将日期移到最后一列
i = Range("a1").End(xlToRight).Column + 1
Columns(1).Cut
Columns(i).Insert
Range("a1").End(xlToRight) = "日期"
' Range("a1").CurrentRegion.EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
没做修改,只是加了点注释
|
评分
-
查看全部评分
|