VB 脚本不会遍历给定文件夹中的每个文件 [英] VB script will not traverse every file in a given folder

问题描述

我是 VBscript 的新手，希望编写一个简单的脚本来更改给定文件夹位置中几千个 csv 文件中的几个单元格.我有一个良好的开端，除了当我运行脚本(从只调用脚本的 .bat 文件)时，它一次只更改和移动 3-8 个文件的事实之外，一切似乎都在工作.它更改的文件数量是随机的，它不像总是更改 5 个文件之类的.我不确定代码中有什么问题，为什么它不会编辑和移动每个文件，而一次只做几个，这是我目前所拥有的，在此先感谢您的帮助:

I am new to VBscript and am looking to write a simple script that changes a couple cells in a few thousand csv files in a given folder location. I have a good start, and it all seems to be working, except for the fact that when I run the script (from a .bat file that just calls the script) it only changes and moves 3-8 files at a time. The number of files it changes is random, its not like it always changes 5 files or something. I am not sure what is wrong in the code as to why it will not edit and move every single file and only does a couple at a time, here is what I have so far, thanks in advance for any help:

Set objFSO = Wscript.CreateObject("Scripting.FileSystemObject")
Set colFiles = ObjFSO.GetFolder("C:\Users\xxx\BadCSVs").Files
Set xl = CreateObject("Excel.Application")

For Each objFile in colFiles
  If LCase(objFSO.GetExtensionName(objFile)) = "csv" Then
    Set wb = xl.Workbooks.Open(objFile)
    Set sht = xl.ActiveSheet


      If(sht.Cells(1,11) <> "") Then
        sht.Cells(1,8) = sht.Cells(1,8) & sht.Cells(1,9)
        sht.Cells(1,9) = sht.Cells(1,10)
        sht.Cells(1,10) = sht.Cells(1,11)
        sht.Cells(1,11) = Null

        wb.Save 
        wb.Close True

      Else
        'if file is opened up and has only 10 columns of data, then it makes no changes, then closes it.  
        wb.Close
      End If
  End If

Next
xl.Quit

推荐答案

你的 EVIL global

Your EVIL global

On Error Resume Next

隐藏错误.禁用/删除它并再次测试.

hides errors. Disable/Remove it and test again.

你的

wb.Close SaveChanges=True

传递比较 SaveChanges(未定义/空)与布尔文字 True 的布尔结果.也许你复制了 VBA 代码

passes the boolean result of comparing SaveChanges (undefined/empty) vs. the boolean literal True. Perhaps you copied VBA code

wb.Close SaveChanges:=True 

(标记冒号)在 VBScript 中不合法?

(mark the colon) that is not legal in VBScript?

和

Set xl = CreateObject("Excel.Application")

应该与一个

xl.Quit

如果您在循环中调用 Excel，请在那里终止它.我会尝试在循环之外启动/退出 Excel，但您应该仔细测试该方法.

If you invoke Excel in the loop, terminate it there. I would try to start/quit Excel out of the loop, but you should test that approach carefully.

这篇关于VB 脚本不会遍历给定文件夹中的每个文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！