Lock contents and protect Excel sheet

[CedarTree]

Have an Excel object in Access VBA. I'd like to lock the contents of each worksheet and protect the worksheet with a password. Tried searching for a solution with no luck so far. Thanks!

 

[theDBguy]

Hi. Have you tried the Protect method?

 

[CedarTree]

Sorry let me add some details.
I have this:
gobjBook.Protect Password:="password", DrawingObjects:=True, Contents:=True, AllowSorting:=True, AllowFiltering:=True

gobjBook is defined as Set gobjBook = gobjExcel.Workbooks.Open(sFullFileName2, False, False)

And gobjExcel=CreateObject("Excel.Application")

But on the Protect line, I get Error 1004 (application error).

 

[theDBguy]

Is the Excel file open or closed?

 

[CedarTree]

Still open - I'm using Access to format cells, etc. during this procedure.

 

[theDBguy]

Still open - I'm using Access to format cells, etc. during this procedure.

I mean, is the file physically open? I didn't mean the object you instantiated, but the actual file. I was just guessing if you're trying to manipulate the file by adding password protection to it that maybe it's not going to let you if it's open in another window.

 

[CedarTree]

An instance is open in Access and I can see Excel in Task Manager (I have Excel hidden to the user while it formats all this stuff), but not open otherwise. The same coding is changing other things successfully, for example formatting cells, freeing panes, etc.

 

[theDBguy]

An instance is open in Access and I can see Excel in Task Manager (I have Excel hidden to the user while it formats all this stuff), but not open otherwise. The same coding is changing other things successfully, for example formatting cells, freeing panes, etc.

Right, manipulating an Excel file using automation should be fine. I was just making sure you didn't have the file you're manipulating opened manually first and at the same time using automation as well. Can you post your full code for us to check? Just in case there something else in there causing the error...

 

[CedarTree]

Sure. Thanks for reviewing...

Sub subExportData(pPID As Long)

    Dim sPath1 As String, sPath2 As String, sFileName As String, sFullFileName1 As String, sFullFileName2 As String
    Dim rst As DAO.Recordset, sql As String, iRow As Integer, sRange As String, sFormula As String, iSheet As Integer
    Dim fso As Object, sMainPath As String, sFolder As String, vDateModified As Date
    
    Set fso = VBA.CreateObject("Scripting.FileSystemObject")

    'Save locally first just to speed up the export, then move to LAN
    sPath1 = fnDatabasePath(True, False)
    sPath2 = "\\Blah\" + fnCurrentUser() + "\"
    Call subCreateFolder(sPath2)
    
    sFileName = "Data_" + Format(pPID, "000000000") + "_" + Format(Now(), "YYMMDD_HHNNSS") + ".xlsx"
    sFullFileName1 = sPath1 + sFileName
    sFullFileName2 = sPath2 + sFileName
    Call subDeleteFile(sFullFileName1)
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "usysqryREPORT_Data1", sFullFileName1, True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "usysqryREPORT_Data2", sFullFileName1, True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "usysqryREPORT_Data3", sFullFileName1, True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "usysqryREPORT_Data4", sFullFileName1, True
    Call fso.CopyFile(sFullFileName1, sFullFileName2, True)
    Set fso = Nothing
    Call subDeleteFile(sFullFileName1)
    
    Set gobjExcel = CreateObject("Excel.Application")
    gobjExcel.Visible = False
    Set gobjBook = gobjExcel.Workbooks.Open(sFullFileName2, False, False)
    Set gobjSheet = gobjBook.ActiveSheet
    gobjExcel.ActiveWindow.zoom = 85
    gobjBook.Worksheets(1).Name = "Inputs"
    gobjBook.Worksheets(2).Name = "Data"
    gobjBook.Worksheets(3).Name = "Info"
    gobjBook.Worksheets(4).Name = "Final"
    gobjBook.Protect Password:="password", DrawingObjects:=True, Contents:=True, AllowSorting:=True, AllowFiltering:=True
    For iSheet = 1 To 4
        gobjBook.Worksheets(iSheet).Activate
        Set gobjSheet = gobjBook.ActiveSheet
        gobjSheet.Range("2:2").Select
        gobjExcel.ActiveWindow.FreezePanes = True
        gobjSheet.Range("1:1").Font.Bold = True
        gobjSheet.Range("1:1").WrapText = True
        gobjSheet.Range("1:1").HorizontalAlignment = xlCenter
        gobjSheet.UsedRange.AutoFilter
        gobjSheet.UsedRange.Columns.AutoFit
        gobjSheet.Range("A2").Select
    Next iSheet
    gobjBook.Worksheets(1).Activate
    
    gobjBook.Save
    gobjExcel.Visible = True
    
    Set gobjSheet = Nothing
    Set gobjBook = Nothing
    Set gobjExcel = Nothing

End Sub

 

[CedarTree]

By the way, when I just had this: gobjBook.Protect "password", True, True, it seemed to work. [it didn't give an explicit error]

But when I check the Excel sheet, the cells are indicated as locked. But I can still edit them!

Please note this post has been edited.

 

[theDBguy]

By the way, when I just had this: gobjBook.Protect "password", True, True, it seemed to work.

When I check the Excel sheet, the cells are indicated as locked. But I can still edit them!

What did you eliminate when you only run that line? You might start looking at those. I'd say add them back in one at a time until you find out what's causing the error.

 

[CedarTree]

But again the issue is that the Excel sheet appears to truly be locked. But I can still edit the cells.

 

[theDBguy]

But again the issue is that the Excel sheet appears to truly be locked. But I can still edit the cells.

May I suggest you tackle the issues one at a time. If you manage to get rid of the error, with all your code intact, and you still can edit the cells, then you can move on to figuring out why. I am not saying they're related but what if they are? Maybe after you fix the initial error, the second problem goes away.

 

[theDBguy]

Hi. Just as a test, try out the code posted here to see if you'll have any luck with them instead. Cheers!

 

[CedarTree]

Two things:
1) Turns out you can choose to protect the workbook and/or the worksheets (there's a difference between the two)
2) Using gobjSheet.Protect "password" did not work still, but the code I stole from your suggested site does...

    For Each WrkSht In ActiveWorkbook.Worksheets
        WrkSht.Protect sPwd
    Next WrkSht

 

[theDBguy]

Two things:
1) Turns out you can choose to protect the workbook and/or the worksheets (there's a difference between the two)
2) Using gobjSheet.Protect "password" did not work still, but the code I stole from your suggested site does...

    For Each WrkSht In ActiveWorkbook.Worksheets
        WrkSht.Protect sPwd
    Next WrkSht

Congratulations! Glad to hear you got it sorted out. Good luck with your project.