SaveAs Error

[Michael M]

Hi All
I've looked at this for a couple of hours now....and it's finally done my head, I can't see the error in the Activeworkbook.SaveAs line, where it is failing

Sub LoopFolders()
Dim oFSO
Dim Folder As Object, Files As Object, file As Object, ws As Worksheet, fldr
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set Folder = oFSO.GetFolder("D:\Temp\Alco Tests")
For Each file In Folder.Files
If file.Type Like "*Comma Separated*" Then
Workbooks.Open Filename:=file.Path
Call Process
Application.DisplayAlerts = False
For Each ws In Worksheets
    If ws.Name Like "Sheet*" Then
        ws.Delete
    End If
Next ws
ActiveWorkbook.SaveAs Filename:=file, FileFormat:=56
End If
ActiveWorkbook.Close
Next file
Set oFSO = Nothing
Application.DisplayAlerts = True
End Sub

Any assistance to ease my pain would be appreciated.

 

[John_w]

Try:

ActiveWorkbook.SaveAs Filename:=file.Path, FileFormat:=56

 

[Michael M]

Hi John
Thanks for the response.
Unfortunately, I have already tried that with no joy !!

 

[John_w]

ActiveWorkbook.SaveAs Filename:=file.Path

without the FileFormat works for me. What is the magic number 56 anyway?

 

[Michael M]

Your line works fine if the file is to be saved as a .CSV file
However, I want the files saved as an .xls Excel 2003.
Hence the fileformat:= 56 which is the format code for Excel 2003

 

[Michael M]

I have finally made it work with this code

ActiveWorkbook.SaveAs Filename:=Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) - 4) & ".xls", FileFormat:=xlNormal

I'd like to make it a bit shorter if possible, but beggars can't be choosers !!

John, thanks for your input so far, much appreciated.

I've reduced it to this

wb.SaveAs Filename:=Left(wb.FullName, Len(wb.FullName) - 4) & ".xls", FileFormat:=xlNormal