Identifying Corrupt Workbooks

[ChrisGreaves]

Is it possible to automate the clicking on the "Enter" key? Regardless of which two messages pop-up requires a response by pressing "Enter".

ABSOLUTELY! That's what the Excel component does for you: It kills and restarts the MSWord job.
get that MSWord piece in action, hunting for DOC files until it baulks, and then I'll test and ship you the Excel module.
Cheers, Chris

[snb]

In which cases does this occur ?
Is it an .xls file that should have the .xlsx extension or the other way around or what ?

Clciking the message box away doesn't solve anything.

[jstevens]

snb,

The first message with the "OK" button appears when retrieving: .xlsm, .xlsb or .xlsx files. The other message relates to .xls files.

EL_110.png

[snb]

The message doesn't indicate that the file is 'corrupt', only that the fileformat and the file extension don't fit.

What if

- you rename an .xlsm,xlsb, .xlxs file to an .xls file and try to open it ?
- you rename an .xls file to an .xlsx file and try to open it ?

Can you please post 1 of the .xlsx files and 1 .xls file ?

[jstevens]

snb,

I had tried renaming the files prior to posting in the Lounge. In doing so, Excel still complained about the file format and extensions. It suggested to verify the file for corruption.

Unfortunately I'm unable to post one of the .xlsx and .xls files; although I can't open them.

I tried opening them with earlier versions of Excel and that did not work. I also tried an Excel repair utility on a .xls file and it tried to repair it with a .xlsx file extension. The software failed to repair the file. I chatted with the software company and they mentioned if the file failed to repair it is corrupt.

[snb]

Did you open a file in Excel or in explorer ?
I don't think posting a corrupt file can do any harm.
But if not posting a sample file is more important than getting a possibe solution to open 30.000 files then I wonder why you chose a public forum to post your question. Basic rule: help the helpers to help you.

[ChrisGreaves]

... I wonder why you chose a public forum to post your question. Basic rule: help the helpers to help you.

I completely understand a reluctance to post a file publicly. As an example, If my Password.doc file was corrupt, I'd be reluctant to post it Eileen's Lounge (my home-away-from-home) for debugging.

That said, if John or anyone else trusts in individual human nature, there is always an option to PM and then to email a single document to a member here.
No guarantee of course of absolute confidentiality, but as humans we need to put our trust in some other humans in our daily lives.

@John: are you in contact with any other person, on Eileen's Lounge or not, to whom you could take, or send, a copy of your least-confidential corrupt workbook? In the end, even if you find a 3rd-party utility that claims to de-corrupt files, how could you trust IT not to email a copy of samples back to its creator?

Just a philosophical thought ...

(later) in The Gold Old days ™ I used to try opening a baulky XLS in Lotus, Quattro Pro etc, and vice versa.
Cheers, Chris

[jstevens]

snb/Chris,

I searched and found a couple of corrupt files (.xls and .xlsm) and have posted them per snb's request.

It took me a while to search through the >30,000 files and while I was at it found some files that were not corrupt.

WL_Adv_Filter.xls

Example_EL.xlsm

[rory]

Both of those contain complete garbage, I'm afraid. They're just repeated sets of characters.

[robertocm]

Is it possible to automate the clicking on the "Enter" key?

Try this:

Code: Select all

Application.Wait (Now + TimeValue("0:00:2"))
CreateObject("WScript.Shell").SendKeys "{ENTER}", True

But possibly your objective window is modal and couldn't be automated from the same excel instance. See this reference:
https://stackoverflow.com/questions/161 ... om-vb-code

I would try another option: an AutoIt script running 'in parallel', with a loop waiting for the window to appear and sending the Enter key.

Code: Select all

Opt('WinWaitDelay', 250)
Opt('WinDetectHiddenText', 1)
Opt('MouseCoordMode', 1)
Opt("WinTitleMatchMode", 2) ;1=start, 2=subStr, 3=exact, 4=advanced, -1 to -4=Nocase
Opt("SendKeyDelay", 50)
Opt("SendKeyDownDelay", Default)
Opt("SendCapslockMode", 1)

Opt("MustDeclareVars", 1)
Opt("TrayIconDebug", 1)

; Set the Escape hotkey to terminate the script.
HotKeySet("{ESC}", "_Terminate")

Global $hWnd

;Create an endless loop, 1 will always be 1 therefore True.
While 1
	$hWnd = WinWait("[CLASS:#32770]", "", 60)
	If WinExists($hWnd) Then
		If Not WinActive($hWnd, "") Then
			WinActivate($hWnd)
			WinWaitActive($hWnd, "", 5)
			;WinSetState($hWnd, "", @SW_MAXIMIZE)
			Sleep(500)
		EndIf

		If WinActive($hWnd, "") Then
			Send("{ENTER}")
			Sleep(3000)
		EndIf
	EndIf
WEnd

Func _Terminate()
	Exit
EndFunc   ;==>_Terminate

Let me share this attachment excel file with a 'disordered' collection of code to loop files, find windows and send keys:

AppEvents.xlsm

[jstevens]

robertocm,

Thank you for suggesting Autoit and the script. I got the script to work running in parallel with the Excel VBA code.

When I have an opportunity I will read through your AppEvents file.

Thanks to snb and Chris as well.

[snb]

I don't know how these files got Excel extensions.
Change it to .txt and you's see what's in the file: see also https://eileenslounge.com/viewtopic.php ... 89#p308689

I't s no use trying to 'rescue' these files, since they have nothing to do with Excel.

[robertocm]

I't s no use trying to 'rescue' these files, since they have nothing to do with Excel.

There's no sense in using VBA for trying to open these files,
i tested 7zip and got an error with both,
this error output could be use to clean a folder as in the AutoIt example below

Code: Select all

#include <File.au3>
#include <WinAPIFiles.au3>

;Required 7-zip
Global $Path7z = "C:\Program Files\7-Zip"
;Global $Path7z = @ProgramFilesDir & "\7-Zip"
If Not FileExists($Path7z & "\7z.exe") Then
	MsgBox(16, "", "7z.exe not found in path " & $Path7z)
	Exit
EndIf

#===== CONFIG =====
Global $iPID, $sOutput = ""
Global $aFileList, $iRecycle
Global $sFolder = @ScriptDir & "\TEST\"
Global $sTempDir = @ScriptDir & "\TempDir"
If FileExists($sTempDir) Then DirRemove($sTempDir, $DIR_REMOVE)

#===== LOOP EXCEL FILES =====
;Look for excel files in selected directory and subdirectories
$aFileList = _FileListToArray($sFolder, "*.xls", $FLTA_FILES, True)
;Global $aFileList = _FileListToArrayRec($sFolder, "*.xls*", $FLTAR_FILES, $FLTAR_RECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH)
If Not @error Then
	For $i = 1 To $aFileList[0]
		;use x command to keep the folder stucture, -aoa Overwrite All existing files without prompt, use -r to unzip the subfolders from the zip file
		$iPID = Run('"' & $Path7z & '\7z.exe" x -aoa -r -y "' & $aFileList[$i] & '" -o"' & $sTempDir & '"', $Path7z, @SW_HIDE, $STDERR_CHILD + $STDOUT_CHILD)
		ProcessWaitClose($iPID)
		$sOutput = StdoutRead($iPID)
		;ConsoleWrite($sOutput)
		If StringInStr($sOutput, "Can't open as archive", $STR_NOCASESENSEBASIC) > 0 Then
			While _WinAPI_FileInUse($aFileList[$i])
				Sleep(1000)
			Wend
			$iRecycle = FileRecycle($aFileList[$i])
			If $iRecycle Then
				ConsoleWrite("Deleted: " & $aFileList[$i] & @CRLF)
			Else
				ConsoleWrite("Error deleting: " & $aFileList[$i] & @CRLF)
			EndIf
		EndIf

		DirRemove($sTempDir, $DIR_REMOVE)
	Next
Else
	MsgBox(16, "Error", "No excel files were found in the folder", 3)
EndIf

[snb]

The result of your code won't differ from the result of mine.

[jstevens]

I don't know how these files got Excel extensions.

snb,

The two files I posted are definitely Excel files. Prior to posting them I had found a good copy of each file and was able to open the good copies. My challenge is with the corrupt files.

One thing I found is that the corrupt file's property for "Program Name" is blank while the good copy say's "Microsoft Excel". It appears that all the corrupt files don't have a reference to a "Program Name".

EL_113.png

.

[snb]

If you open the 'corrupt' file in an HEX-editor you will find that it's contents can't remotely be linked to Excel.

[jstevens]

snb,

If you recall the corrupt files reside in a DMG file. I used FileZilla to transfer them there and the transfer setting is set to Binary not ASCII.

[HansV]

Whatever. As several fellow-Loungers have pointed out, the files that you attached don't have any Excel-related content. They contain only garbage, so the operating system doesn't see them as Excel files (apart from the extension).

[jstevens]

Hans,

This topic got quite long winded and I understand the corrupt files are garbage. I have the necessary tools now to identify and delete them from the DMG while preserving those valid Excel files.

Thanks to all who contributed.

[PJ_in_FL]

I don't know how these files got Excel extensions.

snb,

The two files I posted are definitely Excel files. Prior to posting them I had found a good copy of each file and was able to open the good copies. My challenge is with the corrupt files.
...

IIRC, there might still be WRITE CACHE used on storage, though I might be dating myself even bringing this up.

In the case of a cached write, Windows may have allocated space for the file, but for some reason the actual contents weren't written on that space. Perhaps an unanticipated power down or turning off the PC before Windows said it was safe to shut down. Not a definitive list, just thoughts.

So now Windows reads that space allocated to the file and the bits and bytes that are there from whatever was written there previously would be returned as a nice clean EXCEL file, but it's not and now you have a corrupted file that doesn't look a thing like an EXCEL file.