Adding the same header row to multiple spreadsheets

[longtalker]

Hello everyone

I have several data files (each in XLS format) that are the output of an experiment. Unfortunately, the files were created (in Matlab) without header rows, so it's hard to keep track of what each of the 10+ columns mean. I would therefore like to add a header row (the same header row) to each one of these spreadsheets, but I cannot figure out a way to do this automatically. I have hundreds of data files, and opening each one of them, pasting the header row and saving would take a lot of time.

Can anyone suggest a way to do this? Anticipated thanks for any replies!

Also, I really need to be able to do this quite soon, therefore apologies for cross-posting this on another Excel forum as well (will mark both as solved)

 

[Patrao]

Try this macro it should do the trick. Use shift or CRTL + MouseClick to choose multiple files.

Sub Headers()
Dim C As Long
  Dim DstWks1 As Worksheet
  Dim DstWks2 As Worksheet
  Dim SrcWkb As Workbook
  Dim wkbname As Variant
  Dim xlsFiles As Variant
  
    
   'Get the workbooks to open
    xlsFiles = Application.GetOpenFilename(FileFilter:="Excel files (*.xls), *.xls", MultiSelect:=True)
     Application.AskToUpdateLinks = False
     If VarType(xlsFiles) = vbBoolean Then Exit Sub
      
     'Loop through each workbook and copy the data to this workbook
      For Each wkbname In xlsFiles
        Set SrcWkb = Workbooks.Open(Filename:=wkbname, ReadOnly:=False)
                              
           For i = 1 To Sheets.Count
Sheets(1).Range("A1").Select
'Insert row above active cell
ActiveCell.EntireRow.Insert

    Range("A1").Value = "Column name"
    Range("B1").Value = "Column name"
    Range("C1").Value = "Column name"
    Range("D1").Value = "Column name"
    Range("E1").Value = "Column name"
    Range("F1").Value = "Column name"
    Range("G1").Value = "Column name"
    Range("H1").Value = "Column name"
    Range("I1").Value = "Column name"
    Range("J1").Value = "Column name"
Next
       
           
       SrcWkb.Close savechanges:=True
        'SrcWkb.Close
  
      Next wkbname
End Sub

Cheers
Patrao

 

[longtalker]

Many thanks, this worked great!

 

[NeoVader]

Hi Patrao

I have similar situation as Longtalker.

The difference is that instead of *.xls files, I have multiple *.con files.

Can the macro can be adapted to insert a header in the *.con files as well?

Also, my understanding is that by using Ctrl or Shift + Mouseclick to select the files, this provides the input to the macro to know which files to open. Please confirm.

Look forward to hearing from you soon.

Regards

NeoVader