Kill file command returns Runtime Error 53

Question

I am new to VBA, I have pieced together the following code to convert all .xls files to .xlsx in a folder and delete old .xls files.

I ran it and it worked...now it does not. Now I get a Runtime error 53 'File not found', the code hangs at the Kill command after successfully saving the file. Any ideas?

Sub ConvertToXlsx()
    Dim strPath As String
    Dim strFile As String
    Dim wbk As Workbook
    ' Path must end in trailing backslash
    strPath = "C:\Work Docs\Command and Control\Test\"
    strFile = Dir(strPath & "*.xls")
    Do While strFile <> ""
        If Right(strFile, 3) = "xls" Then
            Set wbk = Workbooks.Open(Filename:=strPath & strFile)
            ActiveSheet.Name = "NewName" 
            wbk.SaveAs Filename:=strPath & strFile & "x", _
                FileFormat:=xlOpenXMLWorkbook
            wbk.Close SaveChanges:=False
            If Right(strFile, 3) = "xls" Then
            
            Kill strFile
            
            End If
        End If
        strFile = Dir
    Loop
End Sub

You would be better off creating an array of all the `xls` files using `Dir()`, and then loop through the array instead to process each file, as the folder contents change as the loop progresses. — braX, Dec 10 '21 at 03:25
Or use a file system object instead of `Dir()` - https://stackoverflow.com/questions/29167539/batch-convert-xls-to-xlsx-with-vba-without-opening-the-workbooks — braX, Dec 10 '21 at 03:31

Lewis Morris · Answer 1 · 2021-12-10T06:51:29.983

I cant test this currently so sorry if it doesn't work.

I always prefer to use the FileSystemObject to iterate files in folders.

Its creates the files as objects and opens you up to many useful attributes and methods etc.

First you need to

set reference to microsoft scripting runtime

as per this link https://www.automateexcel.com/vba/getfolder-getfile/

Then ...

Sub delete_xls_replace_with_xlsx()

Dim fso As FileSystemObject
Set fso = New FileSystemObject
Dim fldr As Folder
Dim fl As File
Dim wbk As Workbook

'set the folder
Set fldr = fso.GetFolder("C:\Users\username\documents\folder")

'iterate the files in the folder
For Each fl In fldr.Files
    'check if xls
    If Right(fl.Path, 3) = "xls" Then
        'open wb
        Set wbk = Workbooks.Open(fl.Path)
        ' save with new ext
        wbk.SaveAs Filename:=Replace(fl.Path, "xls", "xlsx"), FileFormat:=51
        wbk.Close SaveChanges:=False
        'here it will delete the file
        fso.DeleteFile (fl.Path)
    End If
Next fl

End Sub

Kill file command returns Runtime Error 53

1 Answers1