Excel宏读取子文件夹文件生成报告时文件名取值卡顿报错的求助

核心问题根因预判 #

你遇到的月度偶发固定取值卡滞，大概率不是宏的业务逻辑错误，而是Excel运行时的全局状态缓存、遍历函数的隐式状态没有被主动清空导致的——这类问题的特点就是日常运行稳定，攒到某次异常中断、残留状态累积到阈值之后就会触发，且不会留下明显的文件痕迹。 #

分步骤排查路径 #

• 第一优先级排查：Dir() 函数的全局缓存泄漏
  这是绝大多数文件遍历类宏出现卡固定值的最常见原因：VBA内置的Dir()函数是带全局状态的——第一次调用带匹配参数的Dir(路径\*.xlsx)会返回第一个匹配的文件名，后续不带参数的Dir()会沿着上次的缓存位置依次返回后续文件。如果某次宏运行中途因为报错、用户中断、Excel闪退没有正常执行完遍历循环，这个缓存状态会一直保留在内存中，后续再运行宏时，Dir()会直接从上次卡住的位置继续取值，完全不会重新扫描指定文件夹，就表现为“宏内部取的文件名被固定死”。
  验证与修复方式：在宏启动文件遍历逻辑的最前面，主动加一句清空缓存的代码：

  ' 强制清空Dir的全局缓存，避免沿用上一次中断的遍历位置
  Dir Path:="", Attributes:=vbNormal
  

  加完这行代码之后，至少跟踪2个运行周期，看是否还会出现取值卡滞的问题。
• 第二优先级排查：临时残留文件的错读
  你之前排查了工作目录的可见文件，但Excel打开文件时生成的临时锁定文件（通常是~$开头的隐藏属性文件），如果宏的过滤逻辑写得不严，很容易被错当成正式待处理文件读入，并且把这个临时文件的路径存到宏的全局变量里。这类文件本身是动态生成、随机删除的，所以你在问题爆发时去本地磁盘检索，经常找不到对应的实体文件。
  验证与修复方式：
  • 在遍历文件的循环逻辑里，加三重过滤规则，跳过所有临时类文件：
    1. 文件名以~$开头的直接跳过
    2. 文件的Hidden、System属性为True的直接跳过
    3. 文件最后修改时间距当前运行时间小于10秒的直接跳过（排除刚生成的未完全写入的临时文件）
  • 在宏的文件读取环节加最小日志，把每次取到的完整文件路径、文件修改时间追加写到同目录下的mac_run_log.txt里，下次出现卡滞时直接翻日志，就能定位到最早是哪一次运行把错误的临时路径存到了变量里。
• 第三优先级排查：Excel启动目录的状态残留
  每月偶发的频率，刚好匹配Excel自动恢复状态的留存周期：如果某次宏运行时Excel崩溃、触发了文档恢复，这个恢复状态会被自动写到Excel的启动加载目录XLSTART下，每次打开Excel就会自动加载这个状态，连带把之前宏运行时存在内存里的全局变量值一起恢复，就会出现固定卡在之前的错误文件名取值、完全不读取新文件夹内容的现象。
  验证与修复方式：
  • 手动打开资源管理器，定位到%AppData%\Microsoft\Excel\XLSTART路径，清空里面所有非必要的启动模板文件
  • 在宏的最开头加全局变量初始化代码，不依赖Excel自动释放内存，每次运行前把所有存储文件名、路径的公共变量强制重置为空值。

兜底优化方案 #

因为问题是固定月度偶发，不需要等到问题爆发再排查，可以在宏的核心逻辑前加一个固定校验：每次运行时先快速扫描指定子文件夹的文件清单，和宏上次运行时记录的清单做哈希对比，如果清单没有变化就走正常流程，如果有变化就强制重置所有遍历相关的状态，完全不使用之前内存里留存的旧值。 #

内容的提问来源于stack exchange，提问作者Sergii Arkhangelskyi