VBA Loop through and rename all workbooks

[rplohocky]

Hello,
Can someone look at this code and add a command to rename each workbook as the macro runs?

This macro loops through a folder and converts all workbooks from .csv to .xlsx. I would also like it to rename each workbook. Looking at this code, is it possible?

Sub ConvertCSVToXlsx()
'This macro loops through the workbook in the prenamed folder and changes the file extension from csv to xlsx.

    Dim myfile As String
    Dim oldfname As String, newfname As String
    Dim workfile
    Dim folderName As String
    
'Below sets a WORRKBOOK reference to ("6251 Vivint Rental Report.xlsm") workbook.
    Dim src As Workbook
    Set src = Workbooks("6251 Vivint Rental Report.xlsm")
    
'Below set a CELL reference to the("6251 Vivint Rental Report.xlsm") workbook.
'The numbers below refer to row number and column number respectivly. (33, 4) 33= row, 4 = column.
    Dim cellValue As String
    cellValue = src.Worksheets("Data").Cells(17, 14)
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
'Capture name of current file
    myfile = ActiveWorkbook.Name
    
'Set folder name to work through
    folderName = "\\fleet.ad\data\Data1\VMSSHARE\FS\FPSCOEASSO\Temporary Fleet Reports\6251 Vivint Rental report\" & cellValue
'Below adds a backslash to the end of code above to locate the folder.
        If Right(folderName, 1) <> "\" Then
          folderName = folderName & "\"
    End If
'Loop through all CSV filres in folder
    workfile = Dir(folderName & "*.CSV")
    Do While workfile <> ""
'       Open CSV file
        Workbooks.Open Filename:=folderName & workfile
'       Capture name of old CSV file
        oldfname = ActiveWorkbook.FullName
'       Convert to XLSX
        newfname = folderName & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & ".xlsx"
        ActiveWorkbook.SaveAs Filename:=newfname, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWorkbook.Close
'       Delete old CSV file
        Kill oldfname
        Windows(myfile).Activate
        workfile = Dir()
        Loop
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
'Below calls the macro below this one to prep the workbook before sending.
    'Call LoopExcelWBsInFolderVivintXXXX
End Sub

 

[Joe4]

Looks like you can rename it by modifying this line here:

newfname = folderName & [COLOR=#ff0000]Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)[/COLOR] & ".xlsx"

The part in red is what is giving it the file name. You can change it to suit your needs.
If you need help with that, please provide your requirements on what the files should be renamed to.