VBA List All Headers in the worksheet for each file in a folder

[lager1001]

I have a folder with thousands of files. Some of these files have misnamed columns and I need to weed them out. Rather than opening them one by one I'd like to have a VBA script that will loop through all the files and grab the file name and then list the headers in the columns to the right of the file name, for each file.

Any ideas?

 

[mumps]

I believe that the macro would have to open each file, pull the headers and then close each file. Do you want to pull the headers from the first sheet in each file? What is the full path to the folder containing the files? What is the file extension (xlsx, xlsm)? Are those files the only files in that folder?

 

[DanteAmor]

Assuming that the headings are, on the first sheet of each workbook, in row 1 and start in cell A1, try this:

Change "C:\trabajo\" for your folder

Sub GetHeadears()
  Dim wb As Workbook
  Dim sh As Worksheet
  Dim sPath As String
  Dim sFile As Variant
  Dim i As Long, lc As Long
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  sPath = "C:\trabajo\"
  sFile = Dir(sPath & "*.xls*")
  Set sh = Sheets("Sheet1")
  sh.Cells.ClearContents
  i = 2
  
  Do While sFile <> ""
    Set wb = Workbooks.Open(sPath & sFile)
    lc = wb.Sheets(1).Cells(1, Columns.Count).End(1).Column
    sh.Range("A" & i).Value = sFile
    sh.Range("B" & i).Resize(1, lc).Value = wb.Sheets(1).Range("A1").Resize(1, lc).Value
    i = i + 1
    wb.Close False
    sFile = Dir()
  Loop
End Sub