How get all sheet all workbooks folder ? stops at row error "File corrupt"

[SunnySchindler]

Power Query M stops at row 310 which shows "DataFormat.Error: File contains corrupted data".

Assuming corruption is from a Workbook, how to get all workbooks w/o fixing corruption?

= Excel.Workbook(File.Contents([Folder Path] & "\" & [Name]), null, true)

Thanks to code source 

get-all-sheet-names-from-all-workbooks-in-a-folder 

[SunnySchindler · Accepted solution]

This is what I did to solve: Move all non-Excel documents to temporary folder beyond script reach.

I used

robocopy t:\ s:\ /L /S /MOVE /XF *.xl*

which should but didn't catch all png and txt files.

robocopy t:\ d:\ /S /MOVE *.png 

robocopy t:\ d:\ /S /MOVE *.txt

Scripting should be capable to exclude non-Excel documents but beyond my current capability.

[jennratten]

You can limit the results to include only Excel files by filtering the Extension column of the table for text that contains xls.

[jennratten]

This error will be produced when trying to get the content of a file that is encrypted.  I was able to reproduce this error by saving an Excel file and requiring a password.  This error was produced when attempting to read the file from Power Query.

You can add a column that tests whether or not the file is accessible, filter out files that are not accessible, and then continue on with your script. In the example below, the files in rows 1 and 3 are not password protected; the file in row 2 is password protected.

SCRIPT (comments are included to explain what's going on)

let
    Source = Folder.Files("Your folder path goes here"),
    TestEncryption = Table.AddColumn ( 
        Source,
        "isAccessible",
        each 
            let 
                // Use the CSV connector to look at the contents.
                varFile = Csv.Document([Content],[Delimiter=",", Encoding=1252]),
                // Look for a substring that indicates the file is accessible.
                varListOfMatches = List.Select ( varFile[Column1], each Text.Contains ( _, "[Content_Types].xml", Comparer.OrdinalIgnoreCase ) )
            in
                // Does the list have any values (it is not empty)?  yes = accessible, no = unaccessible
                varListOfMatches <> {},
                type logical 
    )
in
    TestEncryption

[SunnySchindler]

Unsucessful delete-hidden-files-that-start-with~ as solution (pointed out by Bob on 2021-04-23 at 19:28 says hidden temporary file with xlsm extension causing query problem.) .  

How to remove files from cmd-dir but not found by cmd-del? Disk Clean-up (Temporary Files same Size afterwards) unable to remove said files. w/o Admin Right to chkdsk NOR Error-Check...