Write in a protected closed workbook

[gnod]

hi,

this is the code i use to write in a closed workbook written by tstom


Sub Write2ClosedBookSingleCellRange(WorkbookFullName As String, SQL As String, NewValue)
Dim conn As New Connection, rs As New Recordset

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & WorkbookFullName & _
";Extended Properties=""Excel 8.0;HDR=NO;"""
rs.Open SQL, conn, 1, 3
rs.Fields(0).Value = NewValue
rs.Update: rs.Close: conn.Close
End Sub


how can i unprotect if the sheet in the workbook is protected?

:help

[Bob Phillips]

Open it and unprotect it in the code.

[gnod]

is it possible not to open the closed workbook but it can unprotect it via VBA code?

here's the template i use modifying the closed workbook. i need to modify all the excel file under the "For Modify" folder without opening the file but the problem is if the sheet is protected..

thanks..

[Bob Phillips]

Don't think so because the unprotect is an Excel function, not something in ADO.

Why can't you open it? You can write to it, so ...

[gnod]

because i already deploy my annual budget template and i want the user just to click a button to modify the data for mid-year budget without opening the workbook..

thanks for your reply..

[Bob Phillips]

How about a database?

[Bob Phillips]

You can always open the workbook, make it non-visible, update it, and close it again.

This could all be driven from your button and the user would be none the wiser.

[gnod]

How about a database?

i use the excel sheet as a database which i'm trying to modify it and put it in one folder ("For Modify")

[gnod]

You can always open the workbook, make it non-visible, update it, and close it again.

This could all be driven from your button and the user would be none the wiser.

but my mid-yr budget template is almost 4MB. It will slow down the process of opening, non-visible, update and close.. and also the no. of files under the For Modify folder will consist of 30-40 excel files..

can you give me a sample on your process? thanks..

[Bob Phillips]

So why is the workbook protected?

And what process is that?

[figment]

you chould check to see if the files is protected, and only open it if it is.

[gnod]

So why is the workbook protected? the worksheet is protected so that I and the department I belong can modify the data in the worksheet but the user who will do the budget can only view..


And what process is that?
a sample of opening, make non-visible, update, and close..


thanks..

[unmarkedhelicopter]

I agree with Bob this is screaming for a DB solution in Access or some other PROPER DB

[gnod]

I agree with Bob this is screaming for a DB solution in Access or some other PROPER DB

thanks, i will enhance my budget template for the next annual budget but for now i need to think for a workaround because the annual budget template is already distributed and the budget is done.. :(

[Bob Phillips]

a sample of opening, make non-visible, update, and close..




Set oWB = Workbooks.Open("wb_path_and_name")

Windows(oWB.Name).Visible = False

With OWB.Worksheets(1)
.Unprotect
.Range("A1").Value = some_value
'and more
.Protect
End With

oWB.Close savechanges:= False

[sparafucile1]

gnod,

I have a similar post that just was resolved. Form me I unprotected the sheet, did my VBA stuff, then protected it before exiting VBA. I think we have similar problems. Here is what I did in my code:




'Allow VBA to write to protected cells
ActiveSheet.Unprotect "password"

' Do stuff like process a checkbox click command

'Protect the cells again
ActiveSheet.Protect "password"



Obviously in the case above the sheet had been protected with the password "password". Hope this helps...

Jeff

[gnod]

gnod,

I have a similar post that just was resolved. Form me I unprotected the sheet, did my VBA stuff, then protected it before exiting VBA. I think we have similar problems. Here is what I did in my code:




'Allow VBA to write to protected cells
ActiveSheet.Unprotect "password"

' Do stuff like process a checkbox click command

'Protect the cells again
ActiveSheet.Protect "password"



Obviously in the case above the sheet had been protected with the password "password". Hope this helps...

Jeff

but the problem is i need to unprotect the sheet while the workbook is close.. thanks..

[gnod]

oWB.Close savechanges:= False


why is it False?
thanks for the sample..

[Bob Phillips]

That was a loigic error my part, you should save it and close it.