|
从多个文本文件中取值,生成需要样式的工作表,请老师帮助提供VBA解决思路,谢谢!
附件已上传,简单说明如下:
1、文本文件有多个,名称不固定,均为txt格式,放在同一目录下。
2、工作表样式已经设定好,取值来自文本文件的表头信息和有账号行的信息。其中客户号、科目号、货币号对“账号”信息的二次拆分(以"."分割)。
3、生成工作表时,希望有种方式,1是可以按照原文本文件名称产生独自的工作表,2是产生一张汇总的工作表。
本帖最后由 suye1010 于 2014-11-10 15:27 编辑
- Const WINDOW_HANDLE = 0
- Const OPTIONS = 0
- Sub Consolidation()
- Application.ScreenUpdating = False
- Dim objShell, objFolder, FolderPath, FSO, FSOFolder, FSOFile, TxtFile
- Dim RegExp As Object, NSH As Worksheet, SRNo As Long, TRNo As Long
- Dim BH, BBMC, JGMC, BBYS, BMMC, BMH, BBRQ, HB
- Dim TempStr, TempArr, ArrS(0 To 10000, 1 To 17), ArrT(0 To 50000, 1 To 17)
- ArrT(0, 1) = "编号"
- ArrT(0, 2) = "报表名称"
- ArrT(0, 3) = "机构名称"
- ArrT(0, 4) = "报表页数"
- ArrT(0, 5) = "部门名称"
- ArrT(0, 6) = "部门号"
- ArrT(0, 7) = "报表日期"
- ArrT(0, 8) = "货币"
- ArrT(0, 9) = "账号"
- ArrT(0, 10) = "客户号"
- ArrT(0, 11) = "科目号"
- ArrT(0, 12) = "货币号"
- ArrT(0, 13) = "账号名称"
- ArrT(0, 14) = "最后交易日"
- ArrT(0, 15) = "余额"
- ArrT(0, 16) = "月日均余额"
- ArrT(0, 17) = "年日均余额"
- Set objShell = CreateObject("Shell.Application")
- Set objFolder = objShell.BrowseForFolder(WINDOW_HANDLE, "选择文本文件所在的文件夹", OPTIONS, "")
- If objFolder Is Nothing Then Exit Sub
- FolderPath = objFolder.Self.Path
- Set FSO = CreateObject("Scripting.FileSystemObject")
- Set FSOFolder = FSO.GetFolder(FolderPath)
- Set RegExp = CreateObject("VBSCRIPT.REGEXP")
- With RegExp
- .Global = False
- .Pattern = "\d{1,10}.\d{4}.\d{3}"
- End With
- For Each FSOFile In FSOFolder.Files
- If FSOFile.Type = "Text Document" Then
- SRNo = 0
- ArrS(0, 1) = "编号"
- ArrS(0, 2) = "报表名称"
- ArrS(0, 3) = "机构名称"
- ArrS(0, 4) = "报表页数"
- ArrS(0, 5) = "部门名称"
- ArrS(0, 6) = "部门号"
- ArrS(0, 7) = "报表日期"
- ArrS(0, 8) = "货币"
- ArrS(0, 9) = "账号"
- ArrS(0, 10) = "客户号"
- ArrS(0, 11) = "科目号"
- ArrS(0, 12) = "货币号"
- ArrS(0, 13) = "账号名称"
- ArrS(0, 14) = "最后交易日"
- ArrS(0, 15) = "余额"
- ArrS(0, 16) = "月日均余额"
- ArrS(0, 17) = "年日均余额"
- Set TxtFile = FSOFile.OpenAsTextStream(1)
- Do While Not TxtFile.AtEndOfStream
- TempStr = TxtFile.readline
- If InStr(TempStr, "编号") Then
- BH = Replace(Split(TempStr)(2), "编号:", "")
- BBMC = Application.WorksheetFunction.Trim(TxtFile.readline)
- End If
- If InStr(TempStr, "机构名称") Then
- JGMC = Replace(Split(TempStr)(2), "机构名称:", "")
- BBYS = Mid(Split(TempStr, "第")(1), 1, 4)
- End If
- If InStr(TempStr, "部门名称") Then BMMC = Application.WorksheetFunction.Trim(Split(TempStr, "部门名称:")(1))
- If InStr(TempStr, "部门号") Then
- BMH = Replace(Split(TempStr)(2), "部门号:", "")
- BBRQ = Split(TempStr)(16)
- HB = Split(TempStr, "货币:")(1)
- End If
- If RegExp.Test(TempStr) Then
- SRNo = SRNo + 1
- TRNo = TRNo + 1
- TempArr = Split(Application.WorksheetFunction.Trim(TempStr))
- ArrS(SRNo, 1) = BH: ArrT(TRNo, 1) = BH
- ArrS(SRNo, 2) = BBMC: ArrT(TRNo, 2) = BBMC
- ArrS(SRNo, 3) = JGMC: ArrT(TRNo, 3) = JGMC
- ArrS(SRNo, 4) = BBYS: ArrT(TRNo, 4) = BBYS
- ArrS(SRNo, 5) = BMMC: ArrT(TRNo, 5) = BMMC
- ArrS(SRNo, 6) = BMH: ArrT(TRNo, 6) = BMH
- ArrS(SRNo, 7) = BBRQ: ArrT(TRNo, 7) = BBRQ
- ArrS(SRNo, 8) = HB: ArrT(TRNo, 8) = HB
- ArrS(SRNo, 9) = TempArr(0): ArrT(TRNo, 9) = TempArr(0)
- ArrS(SRNo, 10) = Split(TempArr(0), ".")(0): ArrT(TRNo, 10) = Split(TempArr(0), ".")(0)
- ArrS(SRNo, 11) = Split(TempArr(0), ".")(1): ArrT(TRNo, 11) = Split(TempArr(0), ".")(1)
- ArrS(SRNo, 12) = Split(TempArr(0), ".")(2): ArrT(TRNo, 12) = Split(TempArr(0), ".")(2)
- ArrS(SRNo, 13) = TempArr(1): ArrT(TRNo, 13) = TempArr(1)
- ArrS(SRNo, 14) = TempArr(2): ArrT(TRNo, 14) = TempArr(2)
- ArrS(SRNo, 15) = TempArr(3): ArrT(TRNo, 15) = TempArr(3)
- ArrS(SRNo, 16) = TempArr(4): ArrT(TRNo, 16) = TempArr(4)
- ArrS(SRNo, 17) = TempArr(5): ArrT(TRNo, 17) = TempArr(5)
- End If
- Loop
- Set NSH = ThisWorkbook.Worksheets.Add
- NSH.Name = Replace(FSOFile.Name, ".txt", "")
- NSH.Cells.NumberFormat = "@"
- NSH.Range("A1").Resize(UBound(ArrS, 1) + 1, UBound(ArrS, 2)) = ArrS
- Erase ArrS
- TxtFile.Close
- End If
- Next
- Set NSH = ThisWorkbook.Worksheets.Add
- NSH.Name = "Total"
- NSH.Cells.NumberFormat = "@"
- NSH.Range("A1").Resize(UBound(ArrT, 1) + 1, UBound(ArrT, 2)) = ArrT
- Set NSH = Nothing
- Set RegExp = Nothing
- Set FSOFolder = Nothing
- Set FSO = Nothing
- Set objFolder = Nothing
- Set objShell = Nothing
- Application.ScreenUpdating = True
- End Sub
复制代码
生成表样式.zip
(17.35 KB, 下载次数: 13)
|
|