Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
385 views
in Technique[技术] by (71.8m points)

c# - Excel interop prevent showing password dialog

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.

enter image description here

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...