Converting all EXP files in a folder to Excel files.

[Anusuya12]

Hi,

I have a set of files stored in a folder with .exp. I need a macro which can convert the .exp files to .xls files. I want the name of the file to remain the same, only the extension to be changed.

Appreciate your help on this.

Thanks.

 

[John_w]

Do you just want the files to be renamed? If so use the Name statement in a Dir loop.

Otherwise, what is the data format of these .exp files? Are they Excel workbooks/CSV files which Excel can open using File - Open? If so, post the code generated by the macro recorder of you opening/importing one of the files.

 

[Anusuya12]

Hi,

I just need the file to be renamed. I not aware of how to use DIR code. Can you let me know that.

Thanks.

 

[John_w]

Try this. You need to modify the line where shown to specify the folder containing the .exp files.

Public Sub Loop_Rename_Files_in_Folder()

    Dim folder As String
    Dim filename As String
   
    folder = "C:\Path to\your\folder\"               'MODIFY THIS LINE - FOLDER CONTAINING FILES TO BE RENAMED
    
    If Right(folder, 1) <> "\" Then folder = folder & "\"
    filename = Dir(folder & "*.exp")
    Do While filename <> vbNullString
        Name folder & filename As folder & Left(filename, InStrRev(filename, ".")) & "xls"
        filename = Dir
    Loop

End Sub

 

[Anusuya12]

Hi there,

It works fine. Similar to this I need a code which can renames the files with unknown extension.
These files are actually transfered from another application. So it comes with number as extension.
say for example R301.123; R301.235
Now I need to rename this file as R301.123.xls;R301.235.xls
Thanks.

 

[John_w]

Public Sub Loop_Rename_Files_in_Folder2()

    Dim folder As String
    Dim filename As String
   
    folder = "C:\Path to\your\folder\"               'MODIFY THIS LINE - FOLDER CONTAINING FILES TO BE RENAMED
    
    If Right(folder, 1) <> "\" Then folder = folder & "\"
    filename = Dir(folder & "*.*")
    Do While filename <> vbNullString
        If Right(filename, 4) <> ".xls" Then Name folder & filename As folder & filename & ".xls"
        filename = Dir
    Loop

End Sub

 

[Anusuya12]

Thank you very much!!!!! It wourks wonderfully...