vba, 统计一个文件夹下（含子文件夹）的所有一类文件的，某列的值统计

1 问题

1.1 问题详细分析

• 问题来源于群里的一个玩家提问
• 需要统计一个文件夹下的多个同类文件
• 提问者要求的是csv，我例子里用的xlsm
• 统计这些表某一列的值，然后显示出统计页

1.2 初期想到的难点

• 文件夹下统计某一种文件不难，但是需要迭代子文件夹，可能需要用到递归
• 用VBA使用文件的内容前，必须先打开文件

 

2 第一版正确代码，基础可以跑而已，未优化

2.1 代码需求点

• 我发给那个提问者的修改点
• 你自己改下参数
• 1 改下你要操作的文件夹的目录 path1 = "C:\Users\Administrator\Desktop\test1"
• 2 改下你要查的文件类型，   If f1.Name Like "*.xlsm" Then    我查的是.xlsm你估计是.csv
• 3 改下你要统计的列 ThisWorkbook.Worksheets("sheet1").Cells(m + 1, 3).Value = Application.Average(Range("a:a").Value)  我统计的是A列

2.2 代码局限性

• EXCEL表的后台打开我处理的不好，尝试了好久，还遇到了  active部件不能创建对象 等问题
• 现在打开的效率有点慢，我故意加了一个延迟，避免卡死
• 因为我这种后台打开表的方式，代码运行完后，那些表还是会一起打开，如果表数量多可能是内存卡死把。。。
• 其他问题

2.3 代码的思路

• 为什么再加一层调用sub？
• 因为主体sub 是需要用到调用自身的，但是因为再次调用自身递归的时候，需要引用的不同的文件夹路径，不能重复调用老路径。所以干脆就让主体SUB也是带参数，被调用的。
• 为什么要用模块级变量
• 也是因为递归，自身调用自身，会导致主过程会被重复调用，但是如果是过程级变量就会每次重置变量值，这个显然是不合符需求的，而是每次递归调用自身后，值都继续累计。
• 设计思路
• 这段代码分层：文件层  \ wb层  \sh层  \内容层

Dim m

Sub test1()
path1 = "C:\Users\Administrator\Desktop\test1"
Call test2(path1)
End Sub
 
 
Sub test2(path1)

Dim fso1 As Object
Set fso1 = CreateObject("scripting.filesystemobject")
 
Application.ScreenUpdating = False
Dim f1 As Object
Dim sh1 As Object
 

For Each f1 In fso1.GetFolder(path1).Files
    If f1.Name Like "*.xlsm" Then
'          Debug.Print f1.Name
          Workbooks.Open (f1.Path)
          Workbooks(f1.Name).Worksheets("sheet1").Cells(1, 9) = 888
          Application.Wait (Now + TimeValue("00:00:01"))
          For Each sh1 In Workbooks(f1.Name).Worksheets
              ThisWorkbook.Worksheets("sheet1").Cells(m + 1, 1).Value = f1.Name
              ThisWorkbook.Worksheets("sheet1").Cells(m + 1, 2).Value = sh1.Name
              ThisWorkbook.Worksheets("sheet1").Cells(m + 1, 3).Value = Application.Average(Range("a:a").Value)
              m = m + 1
          Next
    End If
Next
 
Dim subfd1
For Each subfd1 In fso1.GetFolder(path1).SubFolders
    Call test2(subfd1.Path)
Next
 
 
Application.ScreenUpdating = True
End Sub

 

 

3 解决历程和学习点

3.1 sub  递归调用自己的问题

• 递归是循环，但是一种比较特殊的循环，至少理论上是收敛的
• 和正常的 for 上下限范围循环 或 do loop等条件一样，递归调用自己这种循环，理论上是有尽头的
• 比如我写的这个 subfolder，总是有限数量的。
• 递归肯定不能是无限死循环
• 无限的循环，只能是错误的循环。

 

3.2 包含参数的sub() 不能直接运行，只能被其他sub () 或 function() 调用

• 后来，我直接把 sub 本身带了参数（必须参数），然后递归调用自己的时候也带上path参数（子文件夹参数）
• 然后架了一个更高层的sub，调用我这个处理数据的sub

 

3.3  参数的类型,怎么做可选参数？ ---现在只会加必选参数

• 记得参数里，有必选参数，可选参数？
• 但是怎么设置必选参数，可选参数？还不会弄，得试试
• 后来，我直接把 sub 本身带了参数（必须参数），然后递归调用自己的时候也带上path参数（子文件夹参数）
• 然后架了一个更高层的sub，调用我这个处理数据的sub

 

3.4 参数的生效范围和选择使用

• 因为我写的这段代码涉及到了，调用自身（递归）
• 所以如果只使用 sub过程级变量，下次调用自身又会重置m变量
• 如果使用 static m ，可以实现多次反复调用也继续计数。但坏处是，除非完全关闭全部EXCEL的application,否则即使关闭当前代码的thisworkbook，变量还是不清除。
• 最后采用了 模块级变量 dim  m 放到了模块最前面，这样模块内调用可以传递m

 

3.5 VBA的文件处理系统-----太多，没搞明白其中的差别

如下这些到底什么情况下用什么，感觉每种都有其适用条件

• createobject(file.path)
• fso 和 fso.opentextfile()
• fso 和  fso.getfile()
• fso 和 fso.getfolder()

 

• 尝试过各种后台打开表格的方法----差别很大
• Set wb1 = fso1.getfile(f1.Name)
• Set wb1 = fso1.opentextfile(f1.path, 2)
•  Set wb1 = CreateObject(f1.path)
• Set wb1 = Workbooks.Open(f1.path)

 

• Set objFSO = CreateObject("Scripting.FileSystemObject")
• Set objFile = objFSO.CreateTextFile("c:\windows\xxwl.ini")  
• Set wb1 = CreateObject(f1.path)

 

3.6 文件的类别识别

• 如果文件有多种类型，这下面的写法有时候有差别，原因未知
• wb1.worksheets
• workbooks(wb1.name).worksheets

 

3.7 处理EXCEL文件，必须先打开对应的文件---否则会报错

• 如果对应的EXCEL文件没有先在后台打开，经常报错：下标越界，其实是表没打开

 

3.8 EXCEL表这类文件的后台打开

• 为什么要后台打开？
• 因为如果前台打开表格，会打断当前表的代码运行。
• 方法1： application.screenupdating=false/true  + workbooks.open()
• 方法2： createobject(file.path)  这次不好用，以前是可以的，不知道为啥
• 方法3： 还有些，开启一个新的 application(EXCEL)
• 等等

 

3.9  vba activeX部件不能创建对象？