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?
-
Don't worry about that ;-) I don't think that it is possible to do that except if you use something like a loader. But IMHO that make no sense to automatically enter the password. In this case you can also remove the password.rekire– rekire2012-07-04 12:34:37 +00:00Commented Jul 4, 2012 at 12:34
-
1When you double click the file then how will the macro feed in the password from VBA? Or do you want to launch a file and then feed in the password via the macro?Siddharth Rout– Siddharth Rout2012-07-04 13:00:43 +00:00Commented Jul 4, 2012 at 13:00
-
Or are you referring to the VBA Password?Siddharth Rout– Siddharth Rout2012-07-04 13:08:09 +00:00Commented Jul 4, 2012 at 13:08
-
"When you double click the file then how will the macro feed in the password from VBA?" This is exactly what I want to know.iceagle– iceagle2012-07-04 14:11:14 +00:00Commented Jul 4, 2012 at 14:11
-
1If the password is entered for the user, it effectively is the same as using a blank password. I agree with @Siddharth Rout explaining how a macro can not run before the password dialog.datatoo– datatoo2012-07-04 16:08:41 +00:00Commented Jul 4, 2012 at 16:08
Add a comment
|
4 Answers
@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
Comments
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.
Comments
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"
Comments
As per my knowledge, you can Unlock the Excel Workbook/ Sheet password using the below codes, however you could not unprotect the VBA project password.
Workbooks("Book1").Unprotect Password:="password"
Worksheets("Sheet1").Unprotect "Password"
