Open files in a folder and format

[Halley yenn]

Hi Team,

I have 10k excels in a folder where I have to open each excel and Lock Columns : Col C , Col D, Col E, Col F, Col G, Col H, Col I , Col J , Col K, Col L, Col M, Col N and apply password say "GRUG" and make Col A and Col B editable or Unlocked.

The no of rows in each column will be around 20k rows and I recieve 10k files on weekly basis.

Any macro to open each excel and format accordingly will be appreciated.

Thanks
H

 

[gordsky]

Hi Team,

I have 10k excels in a folder where I have to open each excel and Lock Columns : Col C , Col D, Col E, Col F, Col G, Col H, Col I , Col J , Col K, Col L, Col M, Col N and apply password say "GRUG" and make Col A and Col B editable or Unlocked.

The no of rows in each column will be around 20k rows and I recieve 10k files on weekly basis.

Any macro to open each excel and format accordingly will be appreciated.

Thanks
H

is this for all sheets in the workbook or just one sheet?

 

[Halley yenn]

is this for all sheets in the workbook or just one sheet?

Hi,

These are different workbooks. like 10k workbooks in a folder

 

[gordsky]

Hi,

These are different workbooks. like 10k workbooks in a folder

yes i know there are 10k workbooks but is it for every worksheetsheet in the workbook or for just 1 sheet in each workbook

 

[Halley yenn]

yes i know there are 10k workbooks but is it for every worksheetsheet in the workbook or for just 1 sheet in each workbook

just one sheet in each workbook

 

[gordsky]

The below may work for you IF the following is correct
1) there is only 1 sheet in the workbook
2) in ColA-ColN there are no merged cells
3) the worksheets are not already password protected

I suspect it will be very slow with the number of files

Option Explicit
Option Compare Text
Sub CellLocking()

  Dim FSO As Object, MyFolder As Object
  Dim fname As String, fpath As String, pwrd As String
  Dim wb As Variant
  Dim ws As Worksheet
   
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
       
  pwrd = ("GRUG")
  fpath = "enter you folderpath here" '  change as required
 
  Set FSO = CreateObject("scripting.FileSystemObject")
  Set MyFolder = FSO.GetFolder(fpath)
 
  For Each wb In MyFolder.Files
   
     If wb.name Like "*.xl*" Then
        Set wb = Workbooks.Open(fpath & "\" & wb.name)
        For Each ws In wb.Worksheets
         
           ws.Range("A:B").Locked = False
           ws.Range("C:N").Locked = True
           ws.Protect (pwrd)
         
        Next ws
           
     End If
     wb.Save
  Next wb
     
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Halley yenn]

The below may work for you IF the following is correct
1) there is only 1 sheet in the workbook
2) in ColA-ColN there are no merged cells
3) the worksheets are not already password protected

I suspect it will be very slow with the number of files

Option Explicit
Option Compare Text
Sub CellLocking()

  Dim FSO As Object, MyFolder As Object
  Dim fname As String, fpath As String, pwrd As String
  Dim wb As Variant
  Dim ws As Worksheet
  
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
      
  pwrd = ("GRUG")
  fpath = "enter you folderpath here" '  change as required
 
  Set FSO = CreateObject("scripting.FileSystemObject")
  Set MyFolder = FSO.GetFolder(fpath)
 
  For Each wb In MyFolder.Files
  
     If wb.name Like "*.xl*" Then
        Set wb = Workbooks.Open(fpath & "\" & wb.name)
        For Each ws In wb.Worksheets
        
           ws.Range("A:B").Locked = False
           ws.Range("C:N").Locked = True
           ws.Protect (pwrd)
        
        Next ws
          
     End If
     wb.Save
  Next wb
    
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

Hey ,
Thanks for the code , as you said it is too slow but working well.

KR,
H

 

[gordsky]

Hey ,
Thanks for the code , as you said it is too slow but working well.

KR,
H

if it does what you ask then please show as complete by clicking the tick next to solution

 

[Halley yenn]

if it does what you ask then please show as complete by clicking the tick next to solution

Hi,
Is there any way to add dropdown list from 1 to 5 in column B for the same code above??