Collate the data from excel when it is opening

[Rajneesh Rawat]

Hi Team,

I am stuck in a scenario where Superviosor need to collate the data from all team, twice or thrice in a day.

Below is my code which is working fine if the file is closed and we want to collate the data with all type of excel whether user is working on this file or it is close.

I am getting error "Someone else is working on this file. Please try again later"

Is there any other solution of this issue or can you review the below code and assist me what need to be change.

aa = 0
For Each fl In fld.Files

Set akb = Workbooks.Open(fl, ReadOnly:=True)

If aa >= 1 Then
    lrw = wkb.Sheets(Format(Date, "DD MMM")).Cells(Rows.Count, 1).End(xlUp).Row + 1
    akb.Sheets(1).Unprotect "NB123"
    akb.Sheets(1).Range("A1").CurrentRegion.Offset(1).Copy wkb.Sheets(Format(Date, "DD MMM")).Range("A" & lrw)
    aa = aa + 1
   akb.Close False
ElseIf aa = 0 Then
    lrw = wkb.Sheets(Format(Date, "DD MMM")).Cells(Rows.Count, 1).End(xlUp).Row
    'akb.Sheets(1).Activate
    akb.Sheets(1).Unprotect "NB123"
    akb.Sheets(1).Range("A1").CurrentRegion.Copy wkb.Sheets(Format(Date, "DD MMM")).Range("A" & lrw)
    aa = aa + 1
  
  akb.Close False

End If