CODEWITHSUNDEEP
c#excelms-wordoffice-interop
RedBrogdon
RedBrogdon

Reputation: 5351

How to Access the Undo Stack in Excel from C# via Office Interop?

I'm building a C# app that needs to interact with an Excel spreadsheet via Office Interop. I'd like to gain access to the full Undo stack so I can make a series of changes to the worksheet and then roll them back one at a time if necessary. I see that the Word Document object in the Interop assemblies has UndoRecord and some other niceties, including an Undo function. For example, I can call Microsoft.Office.Interop.Word._Document.Undo() with a parameter to undo multiple actions. Excel's Undo stuff, on the other hand, looks much more limited. Sure, there's Microsoft.Office.Interop.Excel._Application.Undo(), but it goes only one action deep, and if called again performs a "Redo" instead of going deeper down the stack.

I've found code online where VBA developers coded their own Undo histories for Excel because that was the only way to get the desired functionality. I don't see anything like this for C#, though. Is there any way to mimic the way Word's Interop undo works in Excel?

Upvotes: 1

Views: 2461

Answers (1)

Doug Glancy
Doug Glancy

Reputation: 27478

Alas, what's true for Word isn't for Excel. Probably because so many more people write Word VBA :). See this John Walkenbach page for what is possible in Excel VBA (and interop). Here's part of his description:

Making the effects of your subroutines undoable isn't automatic. Your subroutine will need to store the previous state so it can be restored if the user choose the Edit Undo command. How you do this will vary, depending on what the subroutine does. In extreme cases, you might need to save an entire worksheet. If your subroutine modifies a range, for example, you need only save the contents of that range.

EDIT:

There is the Application.Undo method, but it's very limited. Here's the description from Excel 2010 VBA help:

Application.Undo Method

Cancels the last user-interface action.

Remarks This method undoes only the last action taken by the user before running the macro, and it must be the first line in the macro. It cannot be used to undo Visual Basic commands.

In regards specifically to Interop, I'm sure there's nothing available that's not in VBA itself.

Upvotes: 4

Related Questions