Enter Password for Excel file to open

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.
LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
 
Harry LeeConnect With a Mentor Commented:
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
 
Anne TroyEast Coast ManagerCommented:
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
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
That's a good idea. How would I write the macro in this other file to load the password and open my file?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Anne TroyConnect With a Mentor East Coast ManagerCommented:
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
 
Harry LeeCommented:
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
 
Anne TroyEast Coast ManagerCommented:
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
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
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
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
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
 
Harry LeeCommented:
Isalvucci,

I will quickly run a test an let you know the result asap.
0
 
Harry LeeCommented:
Dreamboat,

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

That way, it will keep asking to save the file.
0
 
Harry LeeCommented:
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
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thank you very much for all your help!
0
 
Harry LeeCommented:
lsalvucci, you are welcome!
0
 
Celia AlvesCommented:
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
All Courses

From novice to tech pro — start learning today.