Repair corrupted files and Clean, Trim, Loop through Folder

For #1, do you have a corrupt file you can share? I don;t know how to create one to test code on.
For #2, do you want multiple non-alpha characters removed (e.g. ** or ++)?

Thank you so much for kindness. Two files are attached. On with strange characters, all need to be removed.
Second file is the one which asks to be corrected. It has personal information. Will file be available to other members to view? Any way I can restrict it to only you? Thank you.
All--XLSX-69380-c.XLSX

You can send me a direct message and attach the file there.

I am so sorry, but I do not know how to send direct message. I do not see any option to do that.
May be, I do not have full privileges yet because I just signed up little while ago.

I am so sorry, but I do not know how to send direct message. I do not see any option to do that.
May be, I do not have full privileges yet because I just signed up little while ago.

Hi Arvind ... I just sent you a DM ... you can simply reply to it.
For the future, you can simply hover over my picture, and a Message button should appear.

Arvind,

It seems that Excel will not allow you to overwrite a recovered file without user intervention, so dump all the original files into one directory, and the code below will write the recovered/modified spreadsheets to a new directory. You can always delete the original directory if you so choose, but it's probably a good idea to keep the original files anyway.
The code removes all non-alpha characters (anything not A-Z, a-z, 0-9, or a space).
Unfortunately, the code has no way to know that something like "h1WPBFmSw3fp8" is junk, so it can't remove it.
Please add the following code to a VBA module. Modify the directory names at the top. Dump the original files into the "ToClean" directory, and make sure to create the "Cleaned" directory.  

Option Explicit

Const DirectoryToClean = "C:\Data\Experts Exchange\Excel\ToClean"
Const CleanedDirectory = "C:\Data\Experts Exchange\Excel\Cleaned"

Public Function StripNonAsciiChars(ByVal InputString As String) As String
    Dim i As Integer
    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    With RegEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = "[^a-zA-Z0-9 ]"
        StripNonAsciiChars = Trim(RegEx.Replace(InputString, ""))
    End With
End Function

Sub CleanWorkBook()

Dim lastRow As Long
lastRow = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
Dim lastCol As Integer
Dim colIdx As Integer
Dim rowIdx As Long
Dim sht As Worksheet
Dim strCell As String
Dim bHaveChars As Boolean
Set sht = ActiveSheet

For rowIdx = 1 To lastRow
    If rowIdx > lastRow Then Exit For
    bHaveChars = False
    lastCol = Cells(rowIdx, ActiveSheet.Columns.Count).End(xlToLeft).Column
    'Debug.Print "Checking Row: " & CStr(rowIdx) & " ... Cols: " & CStr(lastCol)
    
    For colIdx = 1 To lastCol
        strCell = StripNonAsciiChars(sht.Cells(rowIdx, colIdx))
        sht.Cells(rowIdx, colIdx) = strCell
        If Len(strCell) > 0 Then bHaveChars = True
    Next
    If bHaveChars = False Then
        ' delete the row
        Rows(rowIdx).Delete
        rowIdx = rowIdx - 1
        lastRow = lastRow - 1
    End If
Next

End Sub

Sub CleanDirectory()

Dim idx As Integer
Dim fso As Object       '// Scripting.fileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
    
Dim fld As Object       '// Scripting.folder
Set fld = fso.GetFolder(DirectoryToClean)
    
Dim fl As Object        '// Scripting.File
Dim wb As Workbook

Dim ToCleanName As String
Dim CleanedName As String

For Each fl In fld.Files
    ToCleanName = DirectoryToClean & "\" & fl.Name
    CleanedName = CleanedDirectory & "\" & fl.Name
    Debug.Print "Cleaning: " & fl.Name
    Application.StatusBar = "Cleaning: " & ToCleanName
    DoEvents
    Set wb = Workbooks.Open(ToCleanName, , , , , , , , , , , , , , True)
    CleanWorkBook
    wb.SaveAs Filename:=CleanedName
    wb.Close saveChanges:=True
Next
    
Application.StatusBar = "Ready."
Debug.Print "Done!"

End Sub

Select allOpen in new window

Hello Sam:
Hope, you had some rest. I had appointments since morning. I am just starting to deploy to VBA, and will be back to you.
Arvind.

Arvind,

Here i the updated code. If there's no corruption (unprintable characters) within the first 10 lines, I assume the rest of the file is ok too.

Sam

Option Explicit

Const DirectoryToClean = "C:\Data\Experts Exchange\Excel\ToClean"
Const CleanedDirectory = "C:\Data\Experts Exchange\Excel\Cleaned"

Public Function StripNonAsciiChars(ByVal InputString As String) As String
    Dim i As Integer
    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    With RegEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = "[^a-zA-Z0-9 ]"
        StripNonAsciiChars = Trim(RegEx.Replace(InputString, ""))
    End With
End Function

' Do not execute this macro directly
' it is called from CleanDirectory

Sub CleanWorkBook()

Dim lastRow As Long
lastRow = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
Dim lastCol As Integer
Dim colIdx As Integer
Dim rowIdx As Long
Dim sht As Worksheet
Dim strCell As String
Dim bHaveChars As Boolean
Dim bCorruptionDetected As Boolean

Set sht = ActiveSheet
bCorruptionDetected = False

For rowIdx = 1 To lastRow
    If rowIdx > lastRow Then Exit For
     ' if no corruption in the first 10 lines, assume the rest of the file is ok
    If rowIdx > 10 And bCorruptionDetected = False Then
        Exit For
    End If
    bHaveChars = False
    lastCol = Cells(rowIdx, ActiveSheet.Columns.Count).End(xlToLeft).Column
    'Debug.Print "Checking Row: " & CStr(rowIdx) & " ... Cols: " & CStr(lastCol)
    
    For colIdx = 1 To lastCol
        strCell = StripNonAsciiChars(sht.Cells(rowIdx, colIdx))
        If Len(strCell) < Len(sht.Cells(rowIdx, colIdx)) Then
            sht.Cells(rowIdx, colIdx) = strCell
            bCorruptionDetected = True
        End If
        If Len(strCell) > 0 Then bHaveChars = True
    Next
    If bHaveChars = False Then
        ' delete the row
        Rows(rowIdx).Delete
        rowIdx = rowIdx - 1
        lastRow = lastRow - 1
    End If
Next

End Sub

Sub CleanDirectory()

Dim idx As Integer
Dim fso As Object       '// Scripting.fileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
    
Dim fld As Object       '// Scripting.folder
Set fld = fso.GetFolder(DirectoryToClean)
    
Dim fl As Object        '// Scripting.File
Dim wb As Workbook

Dim ToCleanName As String
Dim CleanedName As String

For Each fl In fld.Files
    ToCleanName = DirectoryToClean & "\" & fl.Name
    CleanedName = CleanedDirectory & "\" & fl.Name
    Debug.Print "Cleaning: " & fl.Name
    Application.StatusBar = "Cleaning: " & ToCleanName
    DoEvents
    Application.ScreenUpdating = False
    Set wb = Workbooks.Open(ToCleanName, , , , , , , , , , , , , , True)
    CleanWorkBook
    wb.SaveAs Filename:=CleanedName
    wb.Close saveChanges:=True
    Application.ScreenUpdating = True
Next
    
Application.ScreenUpdating = True
Application.StatusBar = "Ready."
Debug.Print "Done!"

End Sub

Select allOpen in new window

Hello Sam:
Screen Update negative and quick sampling if file is corrupted, these both really expedited.
VBA does skip files as you desired.
Now average time of processed files is about 4 to 5 minutes for 6-8 MB file. Removes about 1 to 1.5 MB of junk. I think, Time is acceptable.

I will feed back if there is new information. In the mean time, Thanks again.
Arvind

Amazing nice disposition, extremely prompt response and follow up, fantastic synthesis of VBA.
I thought this VBA not possible. I just tried my luck and Sam Jacobs proved his mettle with so much dedication, worked until 2 AM to author the VBA. He engaged and performed way above and beyond what I could hope for.
He is an inspiration - truly.
I am very happy, and highly recommend Sam Jacobs.
Arvind

Thank you, Arvind.