Solved

Enter Password for Excel file to open

Posted on 2014-02-24
14
2,787 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to fix this UDF to handle blank cells? 8 44
Vlookup Help 3 27
Aggregate data in an EXCEL file. 12 38
Excel Index/Match issue 4 11
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

737 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