Solved

Enter Password for Excel file to open

Posted on 2014-02-24
14
2,932 Views
Last Modified: 2016-08-19
I'm using Automate9 software to open an excel macro file to run every day at a specific time. The problem I'm running into is that the file is password protected so when Automate9 runs to open the file it's stopping at the password prompt to open the file. Is there a way to load the password to open the file using a command line? I really don't want to have to take the password out if I don't have to.
0
Comment
Question by:Lawrence Salvucci
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 22

Expert Comment

by:Dreamboat
ID: 39884341
Just a thought since I don't have any idea how to run that from a command line. Have Automate9 (whatever that is) open ANOTHER Excel file that has an autorun macro that opens your file (and passes the password on) and then immediately closes itself, leaving your Excel file open. Batch files are Windows, not Excel, so I'm no help at all with that.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 39884349
That's a good idea. How would I write the macro in this other file to load the password and open my file?
0
 
LVL 22

Assisted Solution

by:Dreamboat
Dreamboat earned 200 total points
ID: 39884384
Well, I was working on it, but can't get the code to cooperate. I think because I'm opening the workbook programmatically. Anyway, this works to open it, but I cannot get rid of the error.

Open a new workbook and save it as macro-enabled (XLSM) file wherever you like.

Hit Alt+F11. Double-click THIS WORKBOOK at the left.

Paste the following into the window at right.

Private Sub Workbook_Open()
  
      Workbooks.Open Filename:="C:\test\PasswordProtected.xlsx", Password:="Password"

End Sub

Open in new window


At least someone will come along and tell you the right code and all you'll need to do is change the code.

Of course, change the path and filename to your path filename, and change the password to your password.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Expert Comment

by:Harry Lee
ID: 39884527
lsalvucci,

Dreamboat's solution does work but the down side is you have to pretty much lower your VBA security to minimum or even disabled.

The reason for that is if your Automate9 is opening a file with Open Event, Excel is going to pop up security alert to ask to user to confirm opening the file.

First try this to see if this is what you want.

Download the autoopen.xlsb

Open it in Excel. (Be prepare that it will have error, especially the first 2 times you open it.).

Click Open if security prompt shows because I have macro in the file.

It will pop-up error message. Click Debug.

In the VBA Editor, you will see the highlighted line with a file path in it. Update the file path to what you need and the password for opening the file. Save the AutoOpen file to where you want it to store.

(Do you use Automate9 just yet.

Go to the saved location and open it again. You should see security prompt again. Click whatever the option to enable the macro to run.

Now, the real file (From now on I call it Your File) you want to open should be opened.

Close Your File.

Now go back into VBA editor to edit the macro on the AutoOpen.xlsb. (Alt-F11).

You will see a comment line at the very end like
'  ThisWB.Close Savechanges:=False

Open in new window


Before you are 100% satisfied with the AutoOpen, don't remove the ' to enable this line. Or at least save another copy before removing the '.
Once it's removed, it's extremely hard to deal with the file. Since it keeps closing itself.


In the future, if you need to modify the AutoOpen macro, you have to hold the Shift key when opening the file to prevent the Open Event to work.

The macro in the file is
Private Sub Workbook_Open()
Dim ThisWB As Workbook
Set ThisWB = ActiveWorkbook
Workbooks.Open Filename:="C:\Documents and Settings\My Documents\Book2.xls", Password:="YourPasswordHere"
'  ThisWB.Close Savechanges:=False
End Sub

Open in new window

AutoOpen.xlsb
0
 
LVL 22

Expert Comment

by:Dreamboat
ID: 39884609
Harry Lee... that was my difficulty, trying to get the workbook to close after opening the other. Arghhh. It just kept telling me the code execution was interrupted. Hope this works for the OP. Thanks. :)
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 39885182
Harry Lee....I did as you instructed but every time I run the macro it opens up my real file to the password prompt but doesn't put the password in the box or get past that step.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 39885208
Harry Lee....ok for some reason it will work on files that are in "xls" or "xlsx" format but not to open password protected files that are in "xlsm" formatting. Is there something special that needs to be done to open this type of file with your code?
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39886237
Isalvucci,

I will quickly run a test an let you know the result asap.
0
 
LVL 12

Accepted Solution

by:
Harry Lee earned 300 total points
ID: 39886261
lsalvucci,

I have made a small change to the code. Can you please change the file path and password in this newly uploaded file and test it again?

I just tested it an it work fine. Please be aware that I have change the way the file path and password is configured. Instead of putting the info in the File.Open line, I have put it up top for easy changing.

Private Sub Workbook_Open()
Dim ThisWB As Workbook, FileToOpen As String, PW As String
Set ThisWB = ActiveWorkbook
'Configure File Path here
FileToOpen = "C:\Users\hlee\Desktop\File To Open.xlsm"
'Configure File Password here
PW = "123456789"

Workbooks.Open Filename:=FileToOpen, Password:=PW
'  ThisWB.Close Savechanges:=False
End Sub

Open in new window

AutoOpen.xlsb
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39886341
Dreamboat,

I think you were just closing the file without the Savechanges:=False.

That way, it will keep asking to save the file.
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39886377
lsalvucci,

Just another thought. Is your FileToBeOpened contains links to other password protected files?

If it does, that will be your problem. If that's the case, we need to put more lines in the vba to open all the linked password protected files.
0
 
LVL 1

Author Closing Comment

by:Lawrence Salvucci
ID: 39886466
Thank you very much for all your help!
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39886577
lsalvucci, you are welcome!
0
 

Expert Comment

by:Celia Alves
ID: 41763371
Hello, all! How should I change Harry Lee's code so that instead of having the password inserted in the code, Excel would have a box asking for the password and then would continue to  opening the file?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question