Solved

Enter Password for Excel file to open

Posted on 2014-02-24
14
2,540 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
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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 using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

815 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

9 Experts available now in Live!

Get 1:1 Help Now