Krdytkyu

I have written a macro to process all excel files within a user-chosen folder and then save the processed files as a new file in a new folder ("FINAL"). The macro I have works, but is slow. Do you have any suggestions as to how I can improve the speed?

Questions:

1. Can I process these files without actually opening and closing the excel file? Is the opening and closing of the file slowing down the process?



2. 
   

   Is there a better way to code the sort process? For Worksheet("Notes") there is data in all rows of Column "A", and in Worksheet("Orders"), Column "A" contains empty row gaps (3-5 empty rows between rows with data).

   
   
   

   
   

   Sub PreProcessing()
   
   
   
   Application.Calculation = xlCalculationManual
   
   Application.EnableAnimations = False
   
   Application.DisplayStatusBar = False
   
   
   
   'Choose Folder
   
   Set FolderPath = Application.FileDialog(msoFileDialogFolderPicker)
   
       With FolderPath
   
           .AllowMultiSelect = False
   
           .Show
   
       End With
   
   
   
   Application.ScreenUpdating = False
   
   Application.DisplayAlerts = False
   
   
   
   ChosenFolder = FolderPath.SelectedItems(1)
   
   GetDirectory = Mid(ChosenFolder, InStrRev(ChosenFolder, "") + 1)
   
   ChosenFile = Dir(ChosenFolder & "*Output_Final*")
   
   
   
   'Loop through files in the folder
   
   Do While Len(ChosenFile) > 0
   
   
   
       'Open The Workbook
   
       Workbooks.Open Filename:=GetDirectory & "" & ChosenFile
   
   
   
       'Format "Notes" Worksheet
   
       With Cells
   
           .ClearFormats
   
           .RowHeight = 14.4
   
           .ColumnWidth = 8.11
   
       End With
   
   
   
       LR = Cells(Rows.Count, 1).End(xlUp).Row
   
       Range("A" & LR).ClearContents
   
   
   
       Range(Cells(1,1), Cells(1,1).End(xlToRight)).AutoFilter
   
       ActiveWorkbook.Worksheets("Notes").AutoFilter.Sort.SortFields.Clear
   
       ActiveWorkbook.Worksheets("Notes").AutoFilter.Sort.SortFields.Add _
   
           Key:=Range("A1"), _
   
           SortOn:=xlSortOnValues, _
   
           Order:=xlAscending, _
   
           DataOption:=xlSortNormal
   
       With ActiveWorkbook.Worksheets("Notes").AutoFilter.Sort
   
           .Header = xlYes
   
           .MatchCase = False
   
           .Orientation = xlTopToBottom
   
           .SortMethod = xlPinYin
   
           .Apply
   
       End With
   
       Range(Cells(1,1), Cells(1,1).End(xlToRight)).AutoFilter
   
   
   
   
   
       'Format "Orders" Worksheet
   
       Sheets("Orders").Select
   
       With Cells
   
           .ClearFormats
   
           .RowHeight = 14.4
   
           .ColumnWidth = 8.11
   
       End With
   
   
   
       LastCell = Range("A1").SpecialCells(xlCellTypeLastCell).Address
   
       Columns("A:A").Select
   
       ActiveWorkbook.Worksheets("Orders").Sort.SortFields.Clear
   
       ActiveWorkbook.Worksheets("Orders").Sort.SortFields.Add _
   
           Key:=Range("A1"), _
   
           SortOn:=xlSortOnValues, _
   
           Order:=xlAscending, _
   
           DataOption:=xlSortNormal
   
       With ActiveWorkbook.Worksheets("Orders").Sort
   
           .SetRange Range("A2:" & LastCell)
   
           .Header = xlNo
   
           .MatchCase = False
   
           .Orientation = xlTopToBottom
   
           .SortMethod = xlPinYin
   
           .Apply
   
       End With
   
   
   
       'Delete remaining sheets
   
       Application.DisplayAlerts = False
   
           Sheets("C").Delete
   
           Sheets("D").Delete
   
           Sheets("E").Delete
   
   
   
       'Save file
   
       Sheets("Notes").Select
   
   
   
       strFileFullName = ActiveWorkbook.FullName
   
       SaveHere = Left(ActiveWorkbook.FullName, InStrRev(ActiveWorkbook.FullName, "")) & "FINAL"
   
       NewName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5) & "_Processed"
   
       newFileFullPath = SaveHere & NewName & ".xlsx"
   
   
   
       ActiveWorkbook.SaveAs Filename:=newFileFullPath
   
       ActiveWorkbook.Close
   
       ChosenFile = Dir
   
   
   
   Loop
   
   
   
   Application.ScreenUpdating = True
   
   Application.DisplayAlerts = True
   
   Application.Calculation = xlCalculationAutomatic
   
   Application.EnableAnimations = True
   
   Application.DisplayStatusBar = True
   
   
   
   MsgBox "Pre-Processing Complete for " & GetDirectory
   
   
   
   End Sub
   
   

   
   

   
   

EDITED: The Excel documents I am processing have some weird formatting byproducts by the software that produced the file. I just want to remove these so that the Excel files are essentially plain text files which can be used later by a different program. There are 50+ documents that I need to process - which is why I am looking to optimize a macro, so that I don't need to manually open/close/reformat each file individually.

These are the steps that I need to perform for each Excel file:

1. Open Excel file




2. In the "Notes" folder, remove all formats, re-adjust column width & row height. Delete the final cell in Column "A".




3. In the "Notes" folder, remove all formats, re-adjust column width & row height. Also sort on Column "A" so that there are no empty rows within the data.




4. Remove the rest of the sheets in the workbook ("C", "D", "E").




5. Save As new file. Close the original workbook without saving.