vba to open closed files remove filter and unhide all hiden colum of opencallsht in folder

[breadwinner]

Dear Experts

I have more than 300 xlsb files in target folder all 300 xl files contains sheet Name "Opencall" In data range met filter and unhide some columns

i want remove filter and unhide all hidden columns in OPencall sheet in every workbooks through run the vba...

this is target path "C:\Users\mani\Desktop\OPENCALLS_PERF\Consolidattion"

find the attachment...

[LJMetzger]

Hi breadwinner,

Try the attached file.

Lewis

Code follows:

Option Explicit

Sub ProcessXlsbFiles()

  Dim sPath As String
  Dim sPathAndFileName As String
  Dim sFileName As String
  Dim iCount As Long
  Dim iRow As Long
  
  'Clear the output range
  ThisWorkbook.Sheets("Sheet1").Rows("21:65000").Clear

  'Get the folder name
  sPath = Trim(ThisWorkbook.Sheets("Sheet1").Range("D3").Text)
  If Len(sPath) = 0 Then
    Debug.Print "There is no Folder Name specified in 'Sheet1' Cell 'D3'."
  End If
  
  'Make sure the path has a trailing backslash
  If Right(sPath, 1) <> "\" Then
    sPath = sPath & "\"
  End If
  
  'Define the row before the first output row number
  iRow = 20

  'Create the path and file name for the first file find
  sPathAndFileName = sPath & "*.xlsb"

  'Find the first file
  sFileName = Dir(sPathAndFileName)
  
  'Loop until all matching files have been found
  While Len(sFileName) > 0
    'Debug.Print sPath & sFileName
    
    'Increment the found file count
    iCount = iCount + 1
    
    'Output a message
    iRow = iRow + 1
    ThisWorkbook.Sheets("Sheet1").Cells(iRow, 1) = _
      iCount & " Processing file - " & sFileName
      
    'Scroll down so the user can follow progress
    ActiveWindow.SmallScroll Down:=1
    
    'Process the next file
    Call ProcessOneFile(sPath & sFileName)
    
    'Search for the next matching file
    sFileName = Dir()
  Wend
  
  If iCount = 0 Then
    iRow = iRow + 1
    ThisWorkbook.Sheets("Sheet1").Cells(iRow, 1) = "There were NO .xlsb files to process."
  End If

End Sub

Function ProcessOneFile(sPathAndFileName As String) As Long

  Dim ws As Worksheet
  
  On Error GoTo ERROR_EXIT:

  'Open the file
  Workbooks.Open FileName:=sPathAndFileName
  
  'Set the worksheet object
  Set ws = ActiveWorkbook.Sheets("OPencall")
  
  'Turn AutoFilter Off on the specified sheet
  ws.Cells.AutoFilter
  
  'Unhide all columns in the specified sheet
  ws.Cells.EntireColumn.Hidden = False
  
  'Save the file and inhibit 'Do you really want to messages'
  Application.DisplayAlerts = False
  ActiveWorkbook.Close SaveChanges:=True
  Application.DisplayAlerts = True

  Exit Function
  
ERROR_EXIT:

  'Set failure error return
  ProcessOneFile = 1
  ActiveWorkbook.Close SaveChanges:=False

End Function

[breadwinner]

Perfect Sir, Brilliant Code...Thanks alot. Solved

[dgenerationhussain]

Sir I am new to the VBA programming.

I want to use same code but some customization required. If You can help me I will be very thank full to you.

In the above macro.
1. Go to specific folder
2. open all the Xl files
3. Unhide & remove filter of all the sheet in the workbook.
4. Save & Close the file.