|
本帖最后由 gufengaoyue 于 2015-7-27 18:16 编辑
- Sub 方法一()
- 'On Error Resume Next
- Dim fName, arr$(1 To 4 ^ 10, 1 To 2), b, brr, crr As Variant
- fName = Dir(ThisWorkbook.Path & "")
- Do Until fName = ""
- If UCase(Right(fName, 3)) = "TXT" Then
- a = a + 1:
- arr(a, 1) = fName
- arr(a, 2) = Val(Left(fName, 8))
- End If
- fName = Dir()
- Loop
- BubbleSort2 arr, a
- Cells.Clear
- For i = 1 To a
- Open ThisWorkbook.Path & "" & arr(i, 1) For Input As #1
- brr = Split(Trim(StrConv(InputB(LOF(1), 1), vbUnicode)), vbCrLf)
- Close #1
- ReDim crr(1 To UBound(brr) + 1, 1 To 7)
- For b = 0 To UBound(brr)
- brr(b) = Trim(brr(b))
- tmp = Split(Replace(brr(b), vbTab, "|"), "|")
- If UBound(tmp) < 5 Then tmp = Split(Replace(brr(b), vbTab, "|") & String(5 - UBound(tmp), "|"), "|")
- For t = 0 To UBound(tmp)
- crr(b + 1, t + 1) = tmp(t)
- Next
- Next
- Cells(1, (i - 1) * 6 + 1).Resize(b, 6) = crr
- Next
- End Sub
- Sub BubbleSort2(ByRef arr, x)
- Dim i&, j&, vSwap1, vSwap2
- For i = x To 2 Step -1
- For j = 1 To i - 1
- If arr(j, 2) > arr(j + 1, 2) Then
- vSwap1 = arr(j, 1)
- vSwap2 = arr(j, 2)
- arr(j, 1) = arr(j + 1, 1)
- arr(j, 2) = arr(j + 1, 2)
- arr(j + 1, 1) = vSwap1
- arr(j + 1, 2) = vSwap2
- End If
- Next
- Next
- End Sub
复制代码
- Sub 方法二()
- On Error Resume Next
- Dim fName, arr$(1 To 4 ^ 10, 1 To 2), tmpFolder$, b
- fName = Dir(ThisWorkbook.Path & "")
- tmpFolder$ = ThisWorkbook.Path & "\Xmp": CreFold tmpFolder
- Do Until fName = ""
- If UCase(Right(fName, 3)) = "TXT" Then
- a = a + 1:
- arr(a, 1) = Replace(fName, "-", "")
- arr(a, 2) = Val(Left(fName, 8))
- If Not b(tmpFolder & "" & arr(a, 1)) Then FileCopy ThisWorkbook.Path & "" & fName, tmpFolder & "" & arr(a, 1)
- End If
- fName = Dir()
- Loop
- Cells.ClearContents
- BubbleSort2 arr, a
- For i = 1 To a
- brr = RstTxt(tmpFolder, arr(i, 1)).getrows()
- For b = 0 To UBound(brr, 2)
- tmp = Split(Replace(brr(0, b), vbTab, "|"), "|")
- If UBound(tmp) < 5 Then brr(0, b) = Split(Replace(brr(0, b), vbTab, "|") & String(5 - UBound(tmp), "|"), "|") Else brr(0, b) = tmp
- Next
- Cells(1, (i - 1) * 5 + 1).Resize(UBound(brr, 2), 5) = Application.Transpose(Application.Transpose(brr))
- Next
- Shell "cmd.exe /C rd /s /q " & tmpFolder, 0
- End Sub
- Function b(fPath As String)
- Dim fs, F
- Set fs = CreateObject("Scripting.FileSystemObject")
- If fs.fileexists(fPath) Then b = True Else b = False
- Set fs = Nothing: Set F = Nothing
- End Function
- Function CreFold(fPath)
- Dim fs, F
- Set fs = CreateObject("Scripting.FileSystemObject")
- fs.createfolder fPath & ""
- Set fs = Nothing: Set F = Nothing
- End Function
- Function RstTxt(Folder, Txt)
- Dim Cnn, Rst
- Set Cnn = CreateObject("adodb.connection")
- Set Rst = CreateObject("adodb.recordset")
- Cnn.Open "Provider=microsoft.ace.oledb.12.0;Extended Properties='text;IMEX=1;HDR=NO;FMT=Delimited( )';Data Source=" & Folder
- Rst.Open "select * from " & Txt & " WHERE f1 <> null", Cnn, 1, 3
- Set RstTxt = Rst
- End Function
- Sub BubbleSort2(ByRef arr, x)
- Dim i&, j&, vSwap1, vSwap2
- For i = x To 2 Step -1
- For j = 1 To i - 1
- If arr(j, 2) > arr(j + 1, 2) Then
- vSwap1 = arr(j, 1)
- vSwap2 = arr(j, 2)
- arr(j, 1) = arr(j + 1, 1)
- arr(j, 2) = arr(j + 1, 2)
- arr(j + 1, 1) = vSwap1
- arr(j + 1, 2) = vSwap2
- End If
- Next
- Next
- End Sub
复制代码 |
|