|
'sheet7工作表中A列有重复数据,注释行注释去除会有提示,,,
Option Explicit
Sub test()
Dim i, j, k, ii, brr, filename(), dic, t, s, ss, sht
If Not getfilename(filename, ThisWorkbook.path, "_cn.txt") Then MsgBox "文件!": Exit Sub
Set dic = CreateObject("scripting.dictionary")
sht = Split("sheet6 sheet7")
ReDim arr(UBound(sht))
For i = 0 To UBound(sht)
arr(i) = Sheets(sht(i)).[a1].CurrentRegion
For j = 1 To UBound(arr(i))
' If Not dic.exists(arr(i)(j, 1)) Then
t = Array(i, j)
dic(arr(i)(j, 1)) = t
' Else
' MsgBox "有重复数据:" & sht(i) & Space(2) & arr(i)(j, 1): Exit Sub
' End If
Next j, i
For ii = 1 To UBound(filename)
With CreateObject("ADODB.Stream")
.Type = 2
.Mode = 3
.Open
.LoadFromFile filename(ii)
.Charset = "UTF-8"
.Position = 2
brr = Split(.ReadText, vbNewLine)
.Close
End With
For i = 0 To UBound(brr)
If Left(brr(i), 1) = "=" Then Exit For '开始标志位
Next
If i = UBound(brr) + 1 Then MsgBox "格式问题:" & filename(ii): Exit Sub '无标志位
For i = i + 3 To UBound(brr) Step 10 '有效数据开始行,每段10行
If Left(brr(i), 1) = Space(1) Then Exit For '结束标志
t = Split(brr(i), Space(1))
For j = 1 To UBound(t)
If Len(t(j)) Then
If dic.exists(t(j)) Then
s = dic(t(j))
brr(i + 1) = arr(s(0))(s(1), 2)
brr(i + 1) = Space(39) & brr(i + 1)
brr(i + 3) = brr(i + 3) & Space(10)
brr(i + 3) = brr(i + 3) & arr(s(0))(s(1), 3)
End If
Exit For
End If
Next
t = Trim(Split(brr(i + 5), ":")(1))
t = Split(t, "-")
For j = 0 To UBound(t)
For k = 1 To Len(t(j))
If Not IsNumeric(Mid(t(j), k, 1)) Then
ss = Mid(t(j), k)
If dic.exists(ss) Then
s = dic(ss)
ss = arr(s(0))(s(1), 2)
t(j) = Left(t(j), k - 1) & ss
End If
Exit For
End If
Next k, j
t = Join(t, "-")
brr(i + 5) = brr(i + 5) & Space(10) & t
Next
With CreateObject("ADODB.Stream")
.Type = 2
.Mode = 3
.Charset = "utf-8"
.Open
.WriteText Join(brr, vbNewLine)
.SaveToFile Left(filename(ii), Len(filename(ii)) - 4) & "-输出.txt", 2
.flush
.Close
End With
Next
End Sub
Function getfilename(filename, pth, mark) As Boolean
Dim f, n
If Right(pth, 1) <> "\" Then pth = pth & "\"
f = Dir(pth & "*.*")
Do While Len(f) > 0
If LCase(Right(f, Len(mark))) = LCase(mark) Then
n = n + 1: ReDim Preserve filename(1 To n)
filename(n) = pth & f
End If
f = Dir
Loop
If n > 0 Then getfilename = True
End Function |
评分
-
1
查看全部评分
-
|