Excel interop prevent showing password dialog

Question

I am writing a program to clean excel files from empty rows and columns, i started from my own question Fastest method to remove Empty rows and Columns From Excel Files using Interop and everything is going fine.

The problem is that i want to prevent excel from showing the password dialog when the workbook is password protected and to throw an exception instead of that.

i am using the following code to open excel files using interop:

 m_XlApp = New Excel.Application
 m_XlApp.visible = False
 m_XlApp.DisplayAlerts = False

 Dim m_xlWrkbs As Excel.Workbooks = m_XlApp.Workbooks
 Dim m_xlWrkb As Excel.Workbook
 m_xlWrkb = m_xlWrkbs.Open(strFile)

 m_xlWrkb.DoNotPromptForConvert = true

i tried to pass an empty password as some links suggested

m_xlWrkb = m_xlWrkbs.Open(strFile, Password:="")

Or using

m_xlWrkb.Unprotect("")

but no luck.

any suggestions?

interop has the `HasPassword` property https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.haspassword(VS.80).aspx Then you can thrown an exception by yourself — Esselans, May 24 '17 at 22:51
@Jaxedin i agree with you but this property cannot be accessed before openning the file, so it will not work, the password dialog is shown when `workbook.open()` method is called — Hadi, May 25 '17 at 04:56

Hadi · Accepted Answer · 2017-05-27T14:56:13.953

I found a solution, but i will accept other working answers

Problem

When passing an empty string as password the excel consider it as nothing. So it ask for a password and show the dialog.

Solution

The solution is to pass a single quotation as a password, excel will consider it as empty string. If workbook is not password protected it will open, else it will throw the following exception

The password you supplied is not correct. Verify that the CAPS LOCK key is off and be sure to use the correct capitalization

The code will be:

m_xlWrkb = m_xlWrkbs.Open(strFile, Password:="'")

Note

In microsoft excel, single quotation on the beginning of a value is used to force text formatting.

example; '0 is readed as a text of value 0

score 4 · Answer 2 · edited May 25 '17 at 10:32

Do not use Nothing for method arguments you do not want to supply.

Instead of:

m_xlWrkb = m_xlWrkbs.Open(strFile, Nothing, Nothing, Nothing, "", Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing)

use any of the following:

m_xlWrkb = m_xlWrkbs.Open(strFile, Password:="")

or

m_xlWrkb = m_xlWrkbs.Open(strFile, , , , "", , , , , , , , , , )

or

Dim missing As System.Reflection.Missing = System.Reflection.Missing.Value
m_xlWrkb = m_xlWrkbs.Open(strFile,missing, missing, missing, "", missing, missing, missing, missing, missing, missing, missing, missing, missing, missing)

If the Workbook is password protected, doing this will cause a COMException to be thrown with a message of:

"The password you supplied is not correct. Verify that the CAPS LOCK key is off and be sure to use the correct capitalization."

If the Workbook is not password protected, no exception will be thrown assuming the file is accessible.

You can also define the "missing" object shown above like this:

Dim missing As Object = Type.Missing

Type.Missing and System.Reflection.Missing refer to the same object.

i tried what you provided, but it didn't worked. thx for your reply — Hadi, May 25 '17 at 04:56
I really appreciate your help. I found a solution take a look. It was tricky — Hadi, May 27 '17 at 20:26

score 2 · Answer 3 · answered Jun 01 '17 at 09:32

2

Concerning your solution, are you sure that it will not work with anything, that is not the password? Something like this:

Public Function wb_get_workbook(ByVal sFullName As String) As Workbook

    Dim sFile As String
    Dim wbReturn As Workbook

    sFile = Dir(sFullName)

    On Error Resume Next
        Set wbReturn = Workbooks(sFile)

        If wbReturn Is Nothing Then
            Application.AskToUpdateLinks = False
            Set wbReturn = Workbooks.Open(sFullName, , , , "ThisIsDefinitelyAPasswordThatNooneHasUsed681")
        End If
    On Error GoTo 0

    Set wb_get_workbook = wbReturn

End Function

Will also throw an error, if it is password protected, and if it is not, then it would not care about the password you are providing. I am trying in VBA, but in C# you use the Excel Application object, so it should not be different.

answered Jun 01 '17 at 09:32

Vityata

42,633
8
55
100

I am sure that this is not working using c# and vb.net – Hadi Jun 01 '17 at 17:44
That is strange. Did you give it a try with TryCatch? – Vityata Jun 01 '17 at 18:08
Yes i used a try catch. The only working way was to pass a single quote – Hadi Jun 01 '17 at 20:10
@Yahfoufi - thanks, I was thinking to try it out later today in C#, you have saved me the efforts. But still, should not it be the same in C#, as far as they are using the same Excel object? – Vityata Jun 02 '17 at 11:45
@Yahfoufi - yup, the question seems helpful, but my understanding for the Excel object in C# was a bit different. :) – Vityata Jun 02 '17 at 12:01
@Vityata i removed my old comments, you were right. this will not work if you use `Workbook.Unprotect("blabla")` but it will work when using `Open()` method. But `single quotation` works with both methods – Yahfoufi Jun 02 '17 at 14:18

Excel interop prevent showing password dialog

3 Answers3

Problem

Solution

Linked