How to detect / bypass the Workbook Recovery window and process

[jjasmith4]

I'm working with a Word/VBA system, and as it goes, it opens an XLSX to get some information from it. One of many users reported that nothing came from the XLSX, so I manually opened Excel to open the file which the code should have opened, just to prove it wasn't a permissions / access issue.

What happened was that, when I double-clicked the XLSX, Excel opened, and the Recovery window came up, the one that tells you about unsaved documents following an abnormal crash. No other user had that, so it worked fine for the rest. And once I dismissed all the recovered files, closed Excel, and re-ran my Word/VBA system, it worked.

So somehow the presence of unsaved stuff following a crash is what prevents VBA code from opening any workbook. How in VBA can I create a new Excel application object (OK, I can do that) and successfully open a workbook in it without that recovery stuff getting in its way?

Thanks in advance.

 

[jjasmith4]

The Unsaved folder is no good. To be more specific, the presence or absence of files there does not determine whether the Document Recovery dialog will happen.

I had pasted table content from Outlook into a new Excel workbook, then I filtered it using the Data Filter arrows, then I closed it without ever saving it. I then tried opening PowerPoint, adding some content, then End-Tasking it closed, but UnsavedFiles ended up with only a small XLSB which had the Excel-filtered results from before, and when I open Excel, no Document Recovery comes up -- same with PowerPoint.

 

[Paul Ked]

Do you use One Drive? I've just been told Excel copies unsaved files to the online location if you've saved the file to One Drive, not your local machine. That may account for nothing in the Unsaved Files and the starting of Recovery?

 

[jjasmith4]

No, and neither do the users. Any open files during a crash would be local or on the network

 

[Paul Ked]

We'll keep searching...

 

[Jaafar Tribak]

Have you tried setting the Document Recovery Pane visibility to False when launching excel from Word ?

Something like this maybe :

Dim oXl As Object

Set oXl = CreateObject("Excel.Application")
oXl.Visible = True
oXl.CommandBars("Document Recovery").Visible = False

 

[Paul Ked]

That works for me :thumbsup:

Never thought it would be a command bar so I ran this:

Sub TEST()
    Dim i As CommandBar, j As Long
    For Each i In Application.CommandBars
       j = j + 1
       Cells(j, 1) = i.Name
    Next
End Sub

To see what else I'd learn... not much! There are 13 duplicates and 166 unique values (excluding two blanks!)

 

[Paul Ked]

To test I saved a new book with

Private Sub Workbook_Open()
    Application.CommandBars("Document Recovery").Visible = False
End Sub

(Autosave off), made some changes on the sheet and opened it... hey presto, Recovery pane flashed and went!

Excellent, thanks Jaafar

 

[Jaafar Tribak]

To test I saved a new book with
(Autosave off), made some changes on the sheet and opened it... hey presto, Recovery pane flashed and went!
Excellent, thanks Jaafar

Thanks Paul for the feedback and glad you found this useful.

 

[NdNoviceHlp]

I did a few days of messing around with this as it seemed useful. My findings (For XL 2016 version) are that XL creates a registry key for a recovery file when the application is opened. It is located at... HKEY_CURRENT_USER\Software\Microsoft\Office" & Application.Version & "\Excel\Resiliency\startupItems\ It also creates a registry key at HKEY_CURRENT_USER\Software\Microsoft\Office" & Application.Version & \Excel\Resiliency\documentrecovery\ Only the key at the startupitems is relevant as it persists if XL crashes and document recovery will occur on next start up... it does not persist if XL is not in document recovery mode. The key(s) at document recovery also persist following an error but there may be many of them from previous crashes... they probably lead to problems and it seems they can be safely deleted. So it seems U need to code from a non XL application to check if the startupitem key exists when XL is closed in order to determine if XL will initiate the document recovery on wb opening. If it exists then delete it which eliminates the document recovery on start up. Interestingly, it seems that if U delete this key while XL is open and then crash XL, document recovery also doesn't occur. Anyways, I like Jaafar's simple and efficient code but I thought I might as well post the code that I cobbled together. HTH. Dave
****THIS IS WORD VBA
In a Word Module....

'Word Module code
Option Explicit
Const HKEY_CURRENT_USER As Long = &H80000001
Const REG_SZ = 1
Const REG_EXPAND_SZ = 2
Const REG_BINARY = 3
Const REG_DWORD = 4
Const REG_MULTI_SZ = 7

'To fix file recovery errors with Excel closed, use registry editor to remove all keys from
'HKEY_CURRENT_USER\Software\Microsoft\Office\" & Application.Version &
'                                                      "\Excel\Resiliency\documentrecovery\
Sub FixDocRecovery()
Dim RegObj As Object, i As Integer, strComputer As String, ExcelApp As Variant
Dim rPath As String, strValueName As String, arrvaluetypes() As Variant, arrvaluenames As Variant
Dim strKeyText As String, strValue As Variant, intValue As Integer, arrValues() As Variant
'Removes recovered excel file from registry which prevents doc recovery file query on start up.
'look for presence of startupItems in registry...
'HKEY_CURRENT_USER\Software\Microsoft\Office\" & Application.Version & "\Excel\Resiliency\startupItems\
'remove startupItems key if it exists(stops document recovery when Excel starts up)

ExcelApp = "Excel.Application"
If IsAppRunning(ExcelApp) = True Then
'** for testing purposes
MsgBox "Excel is Running!"
Exit Sub
End If

On Error GoTo ErFix
strComputer = "."
Set RegObj = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
strComputer & "\root\default:StdRegProv")
rPath = "Software\Microsoft\Office\" & Application.Version & _
                "\Excel\Resiliency\startupItems\"
RegObj.enumValues HKEY_CURRENT_USER, rPath, arrvaluenames, arrvaluetypes
'If startupItems key exists
If IsArray(arrvaluenames) Then
For i = 0 To UBound(arrvaluenames)
strValueName = arrvaluenames(i)
Select Case arrvaluetypes(i)
Case REG_SZ
RegObj.getStringValue HKEY_CURRENT_USER, rPath, strValueName, strValue
strKeyText = strValue
Case REG_DWORD
RegObj.GetDWORDValue HKEY_CURRENT_USER, rPath, strValueName, intValue
strKeyText = intValue
Case REG_MULTI_SZ
RegObj.GetMultiStringValue HKEY_CURRENT_USER, rPath, strValueName, arrValues
For Each strValue In arrValues
strKeyText = strValue
Next
Case REG_EXPAND_SZ
RegObj.GetExpandedStringValue HKEY_CURRENT_USER, rPath, strValueName, strValue
strKeyText = strValue
Case REG_BINARY
RegObj.GetBinaryValue HKEY_CURRENT_USER, rPath, strValueName, arrValues
For Each strValue In arrValues
strKeyText = strKeyText & " " & strValue
Next
End Select
Next i
'check for data
If strKeyText <> vbNullString Then
'** for testing purposes
MsgBox "StartupItems file exists! Name: " & strValueName & vbCrLf _
& "SubKey Value: " & strKeyText
'delete the subkey strValueName
RegObj.DeleteValue HKEY_CURRENT_USER, rPath, strValueName
End If

Else ' not array ie. no value/file
'** for testing purposes
MsgBox "No StartupItems file!"
End If

Set RegObj = Nothing
Exit Sub

ErFix:
On Error GoTo 0
MsgBox "Error: That didn't work!"
Set RegObj = Nothing
End Sub

Function IsAppRunning(ByVal sAppName) As Boolean
'Generalized Function to Check if an Instance of Application is running
'https://vbadud.blogspot.com/2007/04/generic-function-to-check-if.html
Dim oApp As Object
On Error Resume Next
Set oApp = GetObject(, sAppName)
If Not oApp Is Nothing Then
IsAppRunning = True
End If
Set oApp = Nothing
End Function

To operate, close XL and run the FixDocRecovery sub.

 

[Jaafar Tribak]

So it seems U need to code from a non XL application to check if the startupitem key exists when XL is closed in order to determine if XL will initiate the document recovery on wb opening. If it exists then delete it which eliminates the document recovery on start up

Excellent !

I suspected this Document Recovery setting was stored somewhere in the Registry.

Thank you NdNoviceHlp for investigating this and letting us know.