VB script will not traverse every file in a given folder

Question

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

On Error Resume Next could be burying errors that might explain your inconsistent behavior. — David W

Ekkehard.Horner Ekkehard.Horner · Accepted Answer · 2015-06-19T20:12:28

Your EVIL global

On Error Resume Next

hides errors. Disable/Remove it and test again.

Your

wb.Close SaveChanges=True

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

wb.Close SaveChanges:=True

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

And

Set xl = CreateObject("Excel.Application")

should be paired with an

xl.Quit

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 script will not traverse every file in a given folder

1 Answers