Rename file extension

[ashish mehra]

Hi all,

I have a list of files saved on desktop in a specific folder in .csv & .xls format. I want to change the name & format to .xlsx

I try with following code:

Sub TEST()

Dim wbCounter As Integer
Dim wbPath As String
Dim newFileName As String
Dim myPath As String
Dim newfilenamewithoutext As String

myPath = "C:\Ashish\"


With Application.FileDialog(msoFileDialogOpen)

    .AllowMultiSelect = True
    .Title = "Select Files to be renamed"
    .Show
  
    wbCounter = .SelectedItems.Count
  
    For i = 1 To wbCounter
      
        wbPath = .SelectedItems(i)
        Workbooks.Open (wbPath)
        newFileName = ActiveWorkbook.Name
        ActiveWorkbook.SaveAs Filename:= _
        myPath & "newfilenamewithoutext" & ".xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWindow.Close
      
    Next i

End With

End Sub

Regards,
AM

 

[Deepak]

You are replacing the files due to same name.

 

[Hui]

You may also have an issue with the File Extension not being correct for the File Format

Have a read of: http://www.rondebruin.nl/win/s5/win001.htm

 

[Deepak]

Name must get changed for each new file else that will replace the existing the one or macro stop after throwing error.

Sub TEST()

Dim wbCounter As Integer
Dim wbPath As String
Dim newFileName As String
Dim myPath As String
Dim newfilenamewithoutext As String

myPath = "C:\Ashish\"


With Application.FileDialog(msoFileDialogOpen)

    .AllowMultiSelect = True
    .Title = "Select Files to be renamed"
    .Show
 
    wbCounter = .SelectedItems.Count
 
    For i = 1 To wbCounter
     
        wbPath = .SelectedItems(i)
        Workbooks.Open (wbPath)
      '  newFileName = ActiveWorkbook.Name & "_newfilenamewithoutext"
        'or
        newFileName = "newfilenamewithoutext" & i
     
        ActiveWorkbook.SaveAs Filename:= _
        myPath & newFileName & ".xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWindow.Close
     
    Next i

End With

End Sub

 

[ashish mehra]

You are replacing the files due to same name.

My requirement is to rename the file as per their Vendor Name & its extension to .xlsx

I hope I sound clear.

 

[Deepak]

My requirement is to rename the file as per their Vendor Name & its extension to .xlsx

I hope I sound clear.

No,You are still not clear about the Workbook Name & where the so called Vendor Name in the code.

 

[ashish mehra]

No,You are still not clear about the Workbook Name & where the so called Vendor Name in the code.

Vendor list will be saved at "C:\Ashish\Vendor\VendorList.xlsx" in column A.

 

[Deepak]

Vendor list will be saved at "C:\Ashish\Vendor\VendorList.xlsx" in column A.

Still not clear - How to assign the vendor name to the excel!!!