Open an Excel file saved with password for modify

Question

I would like to open an excel file that is saved with a password for modify with VBScript. My current code VBS code is below, which works, but it keeps popping up with boax asking for a password. How can i open the excel spreadsheet with excel prompting me for a password?

Option Explicit

On Error Resume Next

ExcelMacroExample

Sub ExcelMacroExample() 

  Dim xlApp 
  Dim xlBook 

  Set xlApp = CreateObject("Excel.Application")
  Set xlBook = xlApp.Workbooks.Open("C:\Users\jasons\Documents\TestFile.xlsm",,,,"yep123") 
  xlApp.Visible = True
  xlApp.Run "Refresh_data_ss"
  xlApp.Save
  xlApp.Quit 

  Set xlBook = Nothing 
  Set xlApp = Nothing 

End Sub

There seems to be two passwords that Workbooks.open could ask for. One for open and one for write-access to a write-protected book. Could this have something to do with it? https://msdn.microsoft.com/en-us/library/office/ff194819.aspx — Mats Lind, Aug 05 '16 at 12:58

score 6 · Accepted Answer · answered Aug 05 '16 at 13:01

Now I see, there is a password for modify on your file but not for open. Open password is the fifth parameter to Workbooks.Open, and modify is the sixth. SO you need to have the following instead (one more comma):

 Set xlBook = xlApp.Workbooks.Open("C:\Users\jasons\Documents\TestFile.xlsm",,,,,"yep123")

score 1 · Answer 2 · answered Aug 05 '16 at 12:57

1

You can get the password from an InputBox

myPass = InputBox("Write the password: ")
Set xlBook = xlApp.Workbooks.Open("C:\Users\jasons\Documents\TestFile.xlsm",,,, myPass)

answered Aug 05 '16 at 12:57

Trimax

2,413
7
35
59

Open an Excel file saved with password for modify

2 Answers2