VBA code to user select file without opening convert the extension to .txt from .xls

[SamCha]

Hi all,

Please can you help me on this:

Have few files which have the data in either xml, xhtml,txt format but the extension is xls. The files cannot with opened in secured environment.

We can open the file when we change the extension to .txt. Open the MS 365 excel file > Open browse the .txt file>delimited>save as .xls. Then delete the .txt file from the folder.

The above consumes lot of time. Please can you suggest using vba how this can be done quickly. Was trying to use the below to open the dialogue box to select the file. But couldn't really understand how to use Name As function in vba to convert the extension to .xls without opening the selected file.

With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False 
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb
        .Show
        fullpath = .SelectedItems.Item(1)
    End With
    If InStr(fullpath, ".xls") = 0 Then
        Exit Sub
End Sub

thanks

 

[gravanoc]

Hello. First you need to click the Tools menu in your VBA editor, then References. Look for Microsoft.Scripting.Runtime and check it. Then use this modified code. Change NewName to whatever you want.

Sub Rename()

    Dim fso As FileSystemObject
    Dim f As File
   
    Set fso = New FileSystemObject
   
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb"
        .Show
        fullPath = .SelectedItems.Item(1)
    End With
    If InStr(fullPath, ".xls") = 0 Then
        Exit Sub
    End If
   
    Set f = fso.GetFile(fullPath)
    f.Name = "NewName.xls"
   
End Sub