Solved

Enter Password for Excel file to open

Posted on 2014-02-24
14
2,361 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
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 22

Expert Comment

by:Dreamboat
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 12

Expert Comment

by:Harry Lee
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 12

Expert Comment

by:Harry Lee
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you very much for all your help!
0
 
LVL 12

Expert Comment

by:Harry Lee
Comment Utility
lsalvucci, you are welcome!
0
 

Expert Comment

by:Celia Alves
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now