CODEWITHSUNDEEP
c#excelcom
gregsdennis
gregsdennis

Reputation: 8428

How to "lock" excel while interacting via COM

I have an .NET application that reads a database, does some analysis, and updates stats in an Excel spreadsheet using the COM interface. I have the application to the point where it opens the workbook (or detects it, if it's already open), and finds the sheet it needs to update.

I'm encountering an issue where the user can still interact with Excel (close the application/workbook, change data, etc.) while my application is running. I've considered hiding Excel while my app is chewing on data, but that is application-wide and prevents the user from interacting with any open spreadsheet.

Is there a way to lock Excel from changes through the COM interface, but still have it viewable/readable by the user? Alternatively, is there a way to just hide/lock a single workbook?

Upvotes: 5

Views: 2664

Answers (5)

Scott Gall
Scott Gall

Reputation: 146

    Application.Interactive=false;

as Sid suggests is your best bet I would suggest also changing:

    Application.ScreenUpdating = false; // to avoid screen flicker
    Application.DisplayAlerts = false; // if you wish to suppress most excel messages
    Application.EnableEvents = false; // if there is vba in the workbook you wish to avoid triggering

    Application.Calulation = xlCalculationManual; // if it's a calc intensive automation

A good idea to collect your status pre your automation and set all of these properties back to their originals when you are finished with your automation.

Upvotes: 1

deVashe
deVashe

Reputation: 31

Application.Interactive=false;

Upvotes: 3

Sid Holland
Sid Holland

Reputation: 2921

The best way I can think of doing it is to open and them immediately hide the entire workbook. That way you can still interact with it through Interop, but the user has no visibility to it (unless they specifically unhide it but I think a lot of users don't know how to do that).

xlWorkbook.Windows[1].Visible = false;

Upvotes: 0

John Bustos
John Bustos

Reputation: 19574

... The only real intelligent way to do this that I can think of is to create a new instance of an Excel Application, have that one hidden and to do your changes there.

then, if the workbook is already open, just notify the user and ask them to close it.

Upvotes: 0

jlee88my
jlee88my

Reputation: 3043

Would the Worksheets("Sheet1").protect( <password> ) and unprotect(<password>) do the trick?

Upvotes: 0

Related Questions