How to lock cells for all files in a folder which has sub-folders

Question

I have 100 excel files. They are in sub-folders. Each sub-folder has 10-15 excel files.

I would like to lock cells A1:A10 for all the 100 files in the sub-folders.

I've used VBA.

These are the different paths for example, C:\Users\mmishal001\Desktop\Project PT Attempt 3\DEMO2 for VBA\Director 1\Manager 1 C:\Users\mmishal001\Desktop\Project PT Attempt 3\DEMO2 for VBA\Director 1\Manager 2 C:\Users\mmishal001\Desktop\Project PT Attempt 3\DEMO2 for VBA\Director 2\Manager 3 C:\Users\mmishal001\Desktop\Project PT Attempt 3\DEMO2 for VBA\Director 2\Manager 4

Each of them has 10-15 files.

I've used the below code to write to the files - would be grateful if you could edit the below to lock cells A1:A10 for all the excel files in the above subfolders without having to write the functions again (maybe a loop?)

Sub TextInAll()
Dim my_files As String
Dim folder_path As String
Dim subfolder As String
Dim wb As Workbook
Dim ws As Worksheet
'Assign path to variable
folder_path = "C:\Users\mmishal001\Desktop\Project PT Attempt 3\DEMO2 for 
VBA\Director 1\Manager 1"
'specifying file types or extn.
my_files = Dir(folder_path & "\*.xlsx")
Do While my_files <> vbNullString
    Set wb = Workbooks.Open(folder_path & "\" & my_files)
    Set ws = wb.Sheets(1)
    ws.Range("A1:A5").Value = "mahir"
    wb.Close True
    my_files = Dir()
Loop
MsgBox ("All files are updated")
End Sub

I expect that one the code is run. When I go to any of the 100 files within the sub folders - the cell range A1:A10 is locked in each of the files.

"I've used VBA" - Then why the javascript and java tags? — Andreas

Storax Storax · Accepted Answer · 2019-11-09T11:38:21

I understood it in the following way. Just modify your Do Loop like this

Do While my_files <> vbNullString
    Set wb = Workbooks.Open(folder_path & "\" & my_files)
    Set ws = wb.Sheets(1)
    ws.Range("A1:A5").Value = "mahir"

    ' This is the code to insert
    With ws
        .Cells.Locked = True
        .Range("A1:A10").Locked = False
        .Protect ""  'No password but protected. 
    End With

    wb.Close True
    my_files = Dir()
Loop

Update: Based on the information the post you could do something like that

Sub TextInAll()
    Dim my_files As String
    Dim folder_path As Variant
    Dim subfolder As String
    Dim wb As Workbook
    Dim ws As Worksheet

    Dim vFiles As Variant
    vFiles = Array("C:\Users\mmishal001\Desktop\Project PT Attempt 3\DEMO2 for VBA\Director 1\Manager 1", _
        "C:\Users\mmishal001\Desktop\Project PT Attempt 3\DEMO2 for VBA\Director 1\Manager 2", _
        "C:\Users\mmishal001\Desktop\Project PT Attempt 3\DEMO2 for VBA\Director 2\Manager 3", _
        "C:\Users\mmishal001\Desktop\Project PT Attempt 3\DEMO2 for VBA\Director 2\Manager 4")


    For Each folder_path In vFiles
        'specifying file types or extn.
        my_files = Dir(folder_path & "\*.xlsx")
        Do While my_files <> vbNullString
            Set wb = Workbooks.Open(folder_path & "\" & my_files)
            Set ws = wb.Sheets(1)

            ' This is the code to insert
            With ws
                .Cells.Locked = True
                .Range("A1:A10").Locked = False
                .Protect ""  'No password but protected.
            End With

            ws.Range("A1:A5").Value = "mahir"
            wb.Close True
            my_files = Dir()
        Loop
    Next folder_path
    MsgBox ("All files are updated")
End Sub

How to lock cells for all files in a folder which has sub-folders

1 Answers