CODEWITHSUNDEEP
vbaexcelvbscript
Jason Samuels
Jason Samuels

Reputation: 971

Open an Excel file saved with password for modify

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

Upvotes: 3

Views: 3444

Answers (2)

Mats Lind
Mats Lind

Reputation: 934

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")

Upvotes: 6

Trimax
Trimax

Reputation: 2473

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)

Upvotes: 1

Related Questions