How do you open a protected Excel File from Access and disable protection?

[Heather Thurber]

I am using the following code in Access 2007 to open an excel spreadsheet when as part of an on click function:

Dim objXLApp As Object
Dim objXLBook As Object
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("K:\TP_DEV\Development_Hea ther\Payoff_Calculator.xlsm")
objXLApp.Application.Visible = True

objXLBook.ActiveSheet.Range("C9") = Me.curUPB
objXLBook.ActiveSheet.Range("C11") = Me.lngInterest_Rate

I need to know the code to Unprotect this spreadsheet so that I can pass in the data then reactivate protection. There is a password that will be used to unprotect the excel sheet.

[ADezii]

@Heather Thurber
Just subscribing, will return shortly.

[ADezii]

@Heather Thurber
Try:

Dim objXLApp As Object
Dim objXLBook As Excel.Workbook
 
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("K:\TP_DEV\Development_Hea ther\Payoff_Calculator.xlsm")
 
objXLApp.Application.Visible = True
 
With objXLBook
  .Unprotect Password:="Password Here"
    .ActiveSheet.Range("C9") = Me.curUPB
    .ActiveSheet.Range("C11") = Me.lngInterest_Rate
  .Protect Password:="Password Here"     'Protect Workbook again
End With
 

[Heather Thurber]

@Heather Thurber
Thanks that worked great!

[ADezii]

@Heather Thurber
You are quite welcome.