PDA

View Full Version : Write in a protected closed workbook



gnod
11-12-2007, 01:59 AM
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

xld
11-12-2007, 02:04 AM
Open it and unprotect it in the code.

gnod
11-12-2007, 04:12 AM
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..

xld
11-12-2007, 05:44 AM
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
11-12-2007, 07:28 AM
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..

xld
11-12-2007, 08:03 AM
How about a database?

xld
11-12-2007, 08:04 AM
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
11-12-2007, 08:39 AM
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
11-12-2007, 08:44 AM
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..

xld
11-12-2007, 09:08 AM
So why is the workbook protected?

And what process is that?

figment
11-12-2007, 09:21 AM
you chould check to see if the files is protected, and only open it if it is.

gnod
11-12-2007, 09:39 AM
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
11-12-2007, 09:58 AM
I agree with Bob this is screaming for a DB solution in Access or some other PROPER DB

gnod
11-12-2007, 10:24 AM
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.. :(

xld
11-12-2007, 11:38 AM
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
11-12-2007, 11:45 AM
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
11-12-2007, 05:36 PM
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
11-12-2007, 05:42 PM
oWB.Close savechanges:= False


why is it False?
thanks for the sample..

xld
11-13-2007, 12:56 AM
That was a loigic error my part, you should save it and close it.