Open password protected excel file from vba without the password prompt

1
votes

I have to open an excel file every day and refresh the content to get the newest sales-data. I'd like to automate this with windows task scheduling. Since the excel-file is password protected, i read that the best way is to create another excel file with an workbook_open-macro that opens the desired excel-file. The problem though is, that the code i found still prompts the user to enter the password. I can just hit enter and it'll open then, but why is there still the prompt for it? I am using Excel 365, is there some workaround for this or am i doing something wrong?

I tried it with ReadOnly:=False and without, nothing changed. If i set ReadOnly:=True, it works, but then i can't save the file after the changes.

This is the code i'm using:

Sub Workbook_Open()
  Application.DisplayAlerts = False
  Workbooks.Open Filename:="Path\file.xlsx", Password:="*****", ReadOnly:=False
  Application.DisplayAlerts = True
  ThisWorkbook.Close SaveChanges:=False
End Sub
2
vbaexcelpassword-protection
While most people would hate the idea of using it, if this is going to be run while no one is actively on the system, using sendkeys might not be so horrible. – Rdster
If you don't enter the password isn't the file still read only? Could you do a save-as instead when opening as read only? – tjb1
You are right @tjb1, it's read only if i just hit enter, didn't notice that. And no, if i do a save-as, how am i supposed to open the new file? On the other hand, i could save the file elsewhere without a passoword, update that one and then save it as password protected over the new one! Didn't think about it that way, thanks for the idea! – Thorka Mae
It will run on my machine, which won't be used by that time, so i'll check that option with sendkeys, too @Rdster, didn't know you could do that with vba. – Thorka Mae
Is the workbook password protected or write reserved? If the workbook is write reserved the right command should be Workbooks.Open Filename:="Path\file.xlsx", WriteResPassword:="*****", ReadOnly:=False – Vincent G

2 Answers

3
votes

Is the workbook password protected or write reserved?

Write reserved files can only be opened in read only without the right password, while password protected can't be opened at all.

If the workbook is write reserved the right command should be:

Workbooks.Open Filename:="Path\file.xlsx", WriteResPassword:="*****", ReadOnly:=False
0
votes

try this:

  Application.DisplayAlerts = False
  Workbooks.Open "path\file.xlsx", , , , "password"
  Application.DisplayAlerts = True
  ThisWorkbook.Close SaveChanges:=False