Open protected Workbook in background when it is already open by other person - VB.Net

0
votes

I have a issue. My code opens protected workbook in background - and it works fine, no issue But then workbook is already open by other user password window pops up - how to change that so user won't be able to see that password window? I add on error goto errhandler which contains that workbook is already open - it works fine when item is no protected, but with password this password window pops up.

Thank You in advance

on error goto errhan
Workbooks.Open Filename:= "C:\johndoe.xls", Password:="Password"
exit sub

errhan
messagebox.show("File is already open")

Any suggestions?

1
excelvb.net
It's quite hard to understand your question. Is this summary correct: when you open a password protected workbook your code works fine, but when you open a password protected workbook that another user has open, it asks for the password? – WSC
yes - but not me but application. Application opens workbook – Łukas Ptak
Are you sure it's asking for the password and not just warning that it's already opened? What .NET library are you using to interface with Excel? – WSC
No. Excel window pops up to type password to Read-only mode (only if workbook is already open) I'm using Microsoft.Office.Interop.Excel – Łukas Ptak
I suggest you read the documentation: docs.microsoft.com/en-us/dotnet/api/… - there's various different parameters you can supply to Open which alter the behaviour. E.g. IgnoreReadOnlyRecommended, ReadOnly, Notify. – WSC

1 Answers

0
votes

I've been unable to reproduce your exact issue. Using Microsoft.Office.Interop.Excel if I try to open a password protected workbook that is already open, I get the following message:

read-only messagebox

This is how I'm opening the file:

wb = wbs.Open(Filename:=Location, Password:="password", Notify:=False, IgnoreReadOnlyRecommended:=True)

This is what I would expect, but you're saying isn't happening.

As a side note, I don't think the Notify property works as expected; even with Notify:=False it still raises the notification.

The main issue you've got is the when using Microsoft.Office.Interop.Excel all of the file opening logic is handled by the Excel.Application instance rather than your application. For example, if you don't provide a password it will ask for one, rather than throwing an exception. This is useful in some cases, but for automating tasks it's a bit annoying as without any feedback you can't write proper logic control.

I would recommend you look into using something like EPPlus to handle your Excel file reading/writing. Not only is it less resource intensive (you don't have to spin up an Excel application instance) it also throws exceptions when the file needs a password, or when it's being used by another user, which allows you to handle all these different situations in your application and minimalise the amount of user interaction that's required.