帖个VBA的.- Option Explicit
-
- Sub Try()
- Dim lngRow As Long, i As Integer, objFso As Object, objFolder As Object, arrData, _
- objFl As Object, objOpFl As Object, strPth As String, intCol As Integer, strTime As String, intSh As Integer
-
- strPth = "f:\zhubi" '在此指定要处理的目标文件夹
- Set objFso = CreateObject("scripting.filesystemobject")
- If objFso.folderexists(strPth) = False Then
- MsgBox "文件夹" & Chr(34) & strPth & Chr(34) & "不存在!!!" & Chr(10) & vbCrLf & _
- "请按 Alt + F11 打开 VBE 编辑器指定新的路径...", 48, " :("
- Set objFso = Nothing
- Exit Sub
- End If
- strTime = Time
- intSh = 1
- Sheets(intSh).Select
- Application.ScreenUpdating = False
- Range("a1:iv65536").ClearContents
- Cells(1, 1) = "名称": Cells(1, 2) = "日期"
- Set objFolder = objFso.getfolder(strPth)
- Set objOpFl = objFso.opentextfile(strPth & "\" & "内盘笔4.txt", 1)
- lngRow = 1: intCol = 2
- Do Until objOpFl.atendofstream
- arrData = Split(objOpFl.readline, vbTab, -1, 1)
- lngRow = lngRow + 1
- Cells(lngRow, 1) = arrData(0): Cells(lngRow, 2) = arrData(1)
- Loop
- objOpFl.Close
- For Each objFl In objFolder.Files
- If objFso.GetExtensionName(strPth & "\" & objFl.Name) = "txt" Then
- Set objOpFl = objFso.opentextfile(strPth & "\" & objFl.Name)
- lngRow = 1: intCol = intCol + 1
- If intCol > 256 Then
- intCol = 1: intSh = intSh + 1
- For i = 1 To 256
- Columns(i).AutoFit
- Next i
- Sheets(intSh).Select
- End If
- Cells(1, intCol) = objFso.GetbaseName(objFl.Name)
- Do Until objOpFl.atendofstream
- arrData = Split(objOpFl.readline, vbTab, -1, 1)
- lngRow = lngRow + 1
- Cells(lngRow, intCol) = arrData(2)
- Loop
- objOpFl.Close
- End If
- Next
- Set objFso = Nothing: Set objFolder = Nothing: Set objOpFl = Nothing
- For i = 1 To Range("iv1").End(xlToLeft).Column
- Columns(i).AutoFit
- Next i
- Application.ScreenUpdating = True
- MsgBox strTime & vbCrLf & Time & Chr(10) & "Done!", 0, "Right?"
- End Sub
复制代码 在本机上就楼主上传的附件,用namejm的批代码要几分钟时间,而VBA仅一两秒钟,效率简直一个天一个地...
代码贴错了,改一改... |