I. demand

Although the file format of a batch of files downloaded from the website shows ". xls", they do not match the actual format. Now they need to be stored in the corresponding path according to the rules and saved in the correct format of ". xlsx".
Simple batch modification of name and saving can also be realized with bat command. However, since the format needs to be modified, directly modifying the file suffix format will report an error. The normal step is to [open file save as], so the code will realize the function of automatic saving as.
The storage rules are as follows: column D is the deal name, for example, the deal of HLA 11, 11p.xls, 11c.xls and 11w.xls need to be saved under the path V:\HLA 1119\July 2019, and they will be named Two Way Recon HLA 11 07.03.2019.xlsx respectively.
Transaction Recon HLA 11 07.03.2019.xlsx
Wires Transaction Recon HLA 11 07.03.2019.xlsx

Two, implementation

Sub savefile_all()

    Dim obook As Workbook
    Dim nbook As Workbook
    For j = 1 To 2/*File type*/
        For i = 5 To 14/*deal type*/
        /*Get old file full path file name*/
            old_path = "C:\Users\XXXX\Desktop\position"/*The path of the original file. If the macro file and the original file are in the same folder, you can use ThisWorkbook.Path to get the original file path.*/
            old_file_name = Cells(i, j)/*The original file name is 11p.xls. i is the column number and j is the line number.*/
            old_name = old_path & "\" & old_file_name/*The combination of the original file path and the original file name is the complete path file name of the original file.*/
            Set obook = GetObject(old_name)/*old_name It's just a string. The GetObject method converts it to a usable object.*/
            
            
        /*Get new file full path file name*/
            deal_name = Cells(i, 4)/*deal_name All in column 4, column D*/
            file_type = Cells(34, j)/*File types are all on line 34*/
            new_path = "V:\" & deal_name & "\2019\July 2019"/*The new path is pieced together with deal name*/
            'new_path = "V:\Inactive Deals\" & deal_name & "\2019\July 2019"/*For the three deal s of lines 31-33, if it's not their turn to save them, they will be commented out and opened when it's their turn. You can also write a logic judgment instead of a manual switch.*/
            
            sr = Dir(new_path, vbDirectory)/*Return to new path*/
            If sr = "" Then MkDir (new_path)/*Create a new path if it does not exist*/
    
            new_file_name = file_type & deal_name & "  07.03.2019.xls"/*Splice new filename*/
            new_name = new_path & "\" & new_file_name/*Concatenate new file full path filename*/

         /*Save old as new*/
            Application.DisplayAlerts = False/*During file opening, there will be a prompt pop-up window with mismatched format, which will be closed by default.*/
            obook.SaveAs Filename:=new_name, FileFormat:=xlExcel8 _
                , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
                CreateBackup:=False/*Setting parameters*/
        
            Set nbook = GetObject(new_name)
            Windows(new_file_name).Visible = True/*After saving the new file, the view will be closed and the settings will be opened.*/
            nbook.Save/*Preservation*/
            nbook.Close/*Close*/
        
        Next i
    Next j
    

End Sub