Excel Workbook fails to open after updating and saving

Question

I have two Workbooks that work in tandem with one another. Twice a week (via MS W10 Task Scheduler and a simple vbscript, which runs Excel with display set to "Hidden"), one of the Workbooks opens (WB-A), performs a web query, uses the retrieved data to update four of its sheets, saves itself, and then opens the other Workbook (WB-B) to begin further processing of the new data.

This all worked beautifully, for a time. Now, after this update process has completed, the only way I can get back into the Workbooks is if I manually cycle to another Office build version (by way of "officec2rclient.exe /update user updatetoversion=16.0.XXXX.XXXX", I have been switching between 1609 and 1610).

Without doing this, Excel will crash, and I will see the error telling me that Excel has "stopped responding". If I open Excel first, and then try to open either of the Workbooks, sometimes I will see an error with only "400" displayed inside a critical alert MsgBox.

Initially I though that perhaps the issue was being caused by the weight of WB-B (~70mb on disk, ~4GB in memory), so I began hacking away at it. I removed all the extraneous conditional formatting, named ranges, and external links (to WB-A). That seemed to help considerably, at least in the load time.

At first blush, it appeared that I had resolved the issue with these improvements. Optimizing the external links reduced the number of calls by about ~60%, and reduced the overall memory usage by ~50%. It was not until after the next scheduled update that I realized I was not out of the woods yet.

Relevant details concerning both Workbooks

1) They contain form buttons (controls), conditional formatting, named ranges, formulas, and are both macro-enabled.

2) When they fail to open, they fail when the buttons (controls) should be appearing (I believe as the Workbook is finishing its "Calculate" process).

3) Attempting to use the built-in "Open and Repair" function within Excel fails to load either Workbook at precisely the same moment as a normal attempt would.

4) Performing a repair installation of Office365 (both Quick and Online methods) does nothing to resolve the issue.

5) There have been other times I will go to open WB-B, and every cell is formatted as a Date. Erasing the incorrectly saved format for "Normal" corrects the issue.

Preceding this issue

Right before this started happening, I was getting the "The picture is too large and will be truncated" message as the automatic update process seemed to be wrapping up and saving each of the Workbooks. Before saving, I added Application.CutCopyMode = False which seemed to eradicate that error, as I no longer see that pop-up.

Next steps

I realize that the conditional formatting and complex formulas I have in each of the Workbooks are significant impediments, so I am mustering up the courage to hack away at each Workbook, again. I can add/remove all of the buttons/conditional formatting in WB-B at will, so as irritating as that would be (to deal with each time I open the file), it is a possibility.

The only other thing I can think of doing at this point is splitting each of the Workbooks in "half".

There are 4 or 5 Sheets I could see allocating to a new Workbook in WB-A, and I could limit the number of Sheets in WB-B (currently it adds a new Sheet during each update, totaling ~160 Sheets as of today). Limiting the number of Sheets in WB-B would mean I would have to create a hidden sheet that contained all of the "post cut-off" Sheets' relevant data, as there are Subs that rely on said data to complete successfully. These Sheets that would be removed would need to be moved to a new Workbook, as they visually aide in the analysis of the Workbook in its entirety.

Plea for help

Before I get into all of that, I am hoping that someone knows of some simple bit of VBA I can use to fix all of this. It seems to me that Excel must be caching something related to these Workbooks, but I have not been able to locate any info regarding that. I was under the impression that using Application.CutCopyMode = False cleared the clipboard, but maybe there is more to it than that.

Update (1-1-16)

It appears that the issue is directly related to the files stored in %temp%. After clearing that, I am no longer experiencing any issues.

So to summarize

1) What are the proper methods for clearing all caches Excel may be using (including Clipboard)?

2) What could explain a Workbook failing to open and crashing Excel, yet successfully opening after switching to a different Office build version?

3) Is there a line of code that will delete all temp files Excel stores in %temp%?

Relevant system info: Windows 10 Pro 64-bit, AMD FX 8350, 16 GB DDR3, Office365 1609 (Build 7369.2055)+

Note: I also see this issue on my laptop, which is Intel/DDR4, so it is doubtful that the issue has anything to do with the processor/RAM.

Answer 1

Public Declare Function CloseClipboard Lib "user32" () As Long
Public Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function EmptyClipboard Lib "user32" () As Long

Property Hwnd As Long
    read-only
    Member of Excel.Application

The normal architecture is UI in one thing and data in another. I think you should mostly be in a database rather than using a spreadsheet as a database.

Excel doesn't copy anything to the clipboard until it exits or you paste in a non excel program.

So call OpenClipboard, EmptyClipboard, then CloseClipboard. This will remove any ambiguity about memory. The end result will be none used for sure. According to Help don't use Excel's HWnd but use 0.

This is VB6/VBA6 alias is optional.

Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long

Private Sub Form_Load()   
    x = OpenClipboard(0)
    y = EmptyClipboard()
    z = CloseClipboard()
    MsgBox x & " " & y & " " & z
End Sub