Excel Workbook.SaveAs loop breaks after multiple successes

meckhert

New Member
Joined
Jan 27, 2010
Messages
5
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:

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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,222,227
Messages
6,164,716
Members
451,912
Latest member
HMF009

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top