Check Directory and Convert .xls to .xlsx File

Easiest is to simply check first if the new filename already exists but you cannot use DIR in the middle of a loop where you already use DIR without parameters to get the next file in the loop.

You should be able to use the FSO to check if a file exists. Create a new function and pass the (new) file name. If it returns True then you need to decide what to do.

Please Login or Register  to view this content.

You'll probably need to pass a fully qualified ('C:\dir\dir...XLSX) filename, so test that.

(typed freehand so no testing but the general idea holds)

A VBA basics demonstration for starters :

PHP Code: 

Sub Demo1()
    Dim F(), D, L&, N, B As Boolean
  ReDim F(1 To 1)
        D = Dir(ThisWorkbook.Path & "\*.xls"):  If D = "" Then Beep: Exit Sub
    Do
        L = L + 1
        ReDim Preserve F(1 To L)
        F(L) = "\" & D
        D = Dir
    Loop Until D = ""
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    For Each D In F
        N = ThisWorkbook.Path & Replace(D, ".xls", ".xlsx")
        If Dir(N) > "" Then B = MsgBox("Overwrite " & N & " ?", vbYesNo) = vbYes Else B = True
        If B Then
            With Workbooks.Open(ThisWorkbook.Path & D, 0)
                .SaveAs N, 51
                .Close
            End With
        End If
    Next
        .DisplayAlerts = True
        .ScreenUpdating = True
        .Speech.Speak "Done !"
    End With
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

So you misread or did not well understand …
As for example if your function is called for 1 000 files so it will create 1 000 FSO objects (with useless variable not released)
versus my sample just needing an unique object whatever the files # …

And as it is your post #2 raises an error !

I'd want to ask for assistance in revisiting the code (#3 & #5).
When I copy the entire directory to a different drive or location and run the code, the folder displays some additional file .xlsxx file. The excel screen will be blank.
I search but can't seem to find anything related to .xlsxx file.

The code as follow:-

Please Login or Register  to view this content.

Originally Posted by Marc L

Rather than slowing down the execution by creating a new useless variable object each time the function is called
the better is to use an unique object directly in the VBA procedure :

Alternatives that offer improvements are always welcome but the word "useless" in italics was entirely unnecessary here and is pejorative. Avoid subjective judgements and stick to the facts.