Excel Workbook.SaveAs loop breaks after multiple successes

[meckhert]

I have been struggling with a solution to this issue for a very long time and I'm at my wits end. I've searched these forums and others, but nothing I have found has worked. I've fully qualified my code and tried just about everything else that I can think of.

What I need to do is something very simple: loop through folder1 of .XLS files and for each file, do a SaveAs with a different filename in folder2. What I want to do actually involves manipulating the files before they are saved (so a renaming script won't work), but ignoring that for now, Excel gives me a: "Method 'SaveAs of object '_Workbook' failed" after 35-85 successful runs. I need to process about 1600 files, so I need to fix this issue. Currently using Excel 2007 fully patched with no add-ins enabled.

The code:

Sub DoLoop()
            
    Dim oXL As Excel.Application
    Dim wbTemplate As Excel.Workbook
    Dim sFolder1 As String
    Dim sFolder2 As String
    Dim sFilename As String
    Dim i As Integer
    
    Set oXL = New Excel.Application
    oXL.Visible = True
    sFolder1 = "C:\test\OldFiles\"
    sFolder2 = "C:\test\NewFiles\"

    
    'start loop
    i = 0
    sFilename = Dir(sFolder1 & "*.xls", vbDirectory)
    Do While Len(sFilename) <> 0
        Set wbTemplate = oXL.Workbooks.Open(sFolder1 & sFilename, ReadOnly:=True)
        wbTemplate.SaveAs Filename:=sFolder2 & i, FileFormat:=56
        wbTemplate.Close SaveChanges:=False
        Set wbTemplate = Nothing
        i = i + 1
        sFilename = Dir()
    Loop
            
    oXL.Quit
    Set oXL = Nothing
    
End Sub

 

[pbornemeier]

Do you have any sub-directories in either OldFiles or NewFiles directories with a name that matches *.xls
Have you tried a debug print wbTemplate.Name statement before the SaveAs statement to see if it failing on the same filename each time?

If you are not explicitly using a name such as i & ".xls" in the SaveAs statement, I am surprised it is working at all since Excel 2007 and later are picky about the SaveAs statement. See http://www.rondebruin.nl/saveas.htm for more details.

 

[meckhert]

Phil,

Thanks for the reply. I was never fully able to determine why the error was occurring, but it was pretty inconsistent. I believe that the issue had something to with corrupt workbooks (1600 files, 1600 separate users, and no way to enforce workbook protection because of another constraint = lots of potential for corruption). However, the solution I came up with opens each workbook in extract data mode and uses a few variant array loops to transfer the data to fresh copies of my template. Probably not the most efficient solution, but getting it right is more important than doing it fast. I've found that OLE automation errors when dealing with lots of workbooks are very difficult to diagnose, but that should be expected when you try to use Excel as an enterprise tool.

Marc