|
|
一般负责工资的同事最担心的是给银行的盘出错，一旦出错，发工资就耽误了。我在工作中编写了检查出错的程序。能查出上报的文本文件多出的空行，也能查出每行数据后或中间多出的空格。该程序经本人使用非常好。经该程序检查过的文件，绝对没问题，放心使用。这是针对工行的上报文件。现给出原文件和代码。
Sub tt()
Dim fso As Object
Dim ts As Object
Dim filename, MyPath, Error As String
Dim fd As FileDialog
Dim Recno, Kongh As Integer
Dim Sfje As Double
MyPath = ThisWorkbook.Path
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.AllowMultiSelect = False
fd.Filters.Clear
'Filters.Add "ËùÓÐ WORD Îļþ", "*.doc", 1
fd.Filters.Add "Îı¾Îļþ", "*.TXT,*.XLS"
fd.InitialFileName = MyPath
If fd.Show = -1 Then
filename = fd.SelectedItems(1)
filenum = FreeFile()
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.OpenTextFile(filename, 1)
Lines = Split(ts.ReadAll, vbCrLf)
ts.Close
Recno = 0
For Each I In Lines
' MsgBox Len(I) & "|" & I & "|" & Len(Trim(Left(I, 14)))
If Len(Trim(Left(I, 14))) = 13 And Len(I) <> 49 Then
MsgBox Len(I) & "|" & I & "|" & Len(Trim(Left(I, 13)))
Error = Error & Recno & "|" & Mid(I, 11, 4)
End If
If Len(Trim(Left(I, 14))) = 12 And Len(I) <> 50 Then
MsgBox Len(I) & "|" & I & "|" & Len(Trim(Left(I, 13)))
Error = Error & Recno & "|" & Mid(I, 11, 4)
End If
If Len(Trim(Left(I, 14))) = 14 And Len(I) <> 48 Then
MsgBox Len(I) & "|" & I & "|" & Len(Trim(Left(I, 13)))
Error = Error & Recno & "|" & Mid(I, 11, 4)
End If
If I <> "" Then
Sfje = Sfje + Round(Mid(Right(I, 15), 1, 9) / 100, 2)
Recno = Recno + 1
Else
Recno = Recno + 1
Kongh = Kongh + 1
MsgBox I & “空行" & Recno
End If
Next
End If
Cells(2, 2).Value = Recno
Cells(5, 2).Value = Sfje
Cells(8, 1).Value = Error
Cells(4, 2).Value = Kongh
MsgBox "检查完成Ï"
End Sub
[ 本帖最后由 weiguoyin868 于 2009-6-18 23:38 编辑 ] |
评分
-
1
查看全部评分
-
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2009-6-18 23:25
楼主
