Excel VBA - Automatically Input Password

问题:

I want to write an Excel VBA macro to input the password automatically for the user. That way, the user does not need to input the password manually every time they double click on the file. Is this possible? If so, how can I do this?

回答1:

As mentioned in my comment, the functionality that you seek, in my opinion cannot be done. Let me explain it.

To auto run a VBA code in an Excel file you need to place the code either in the Workbook_Open() in the ThisWorkbook code area or in Auto_Open() in a module.

Now these two Subs execute only after the password has been entered in a password protected file i.e after the workbook has opened. So there is no way this can be run before the password is fed to the password dialog box or before the Workbook is opened.

I am sure your boss is a sensible guy and will understand if you can explain it nicely to him :)

You might also want to see this link which explains more in details about running the macro automatically.

Topic: Running a macro when Excel starts

Link : http://office.microsoft.com/en-us/excel-help/running-a-macro-when-excel-starts-HA001034628.aspx

Quote from the above link

If you want to automatically perform certain actions whenever you start Microsoft Excel, you can record or write a macro that will run whenever you open a workbook. There are two ways to do this:

Record a macro and save it using the name Auto_Open. Write the macro as a Microsoft Visual Basic® for Applications (VBA) procedure for the Open event of a workbook.

回答2:

There is no command-line parameter to pass in the password. But what you can do is have a "opener" spreadsheet that takes a spreadsheet name and password as parameters and using VBA opens the password-protected spreadsheet.

Look at his link:

https://superuser.com/questions/438842/excel-workbook-desktop-shortcut-with-auto-password

That only shows you how to get the command-line parameters - once you have those the you can use:

Workbooks.Open "filename", , , , "Password"

回答3:

@iceagle, I do not have enough reputation to "comment" on @(Siddharth Routh), but I regret the comments and the answer for saying it is meaningless/useless/can not be done.
For instance if you have a whole set of excels that you want to alter but are all password protected. If now you run them all via a loop you have to enter the password everytime again (seems stupid when you have 1000+ files no?) A better option: encode the macro of VBS, which has the password in it and can therefor change all the encoded files! Provide the user with this password and now you still have 1000 encoded files only you do not have to enter it everytime.

Now for the answer, if people come upon this, is given correctly by @DJ. You can put the password into the command line of the workbook opener like this:

Set wb = Workbooks.Open(Filename:="myfile.xls", Password:="password")

I hope people that come across this do not take the accepted answer as given but look further.

Kind regards, Pieter