DONE: Group of Excel files in folder, send all first rows to a text file (or other)

[nkormanik]

I need to vertically concatenate around 100 Excel files (xls) -- using a program called "Merge Excel Files."

That program assumes all files are structured the same, same column headers extending across in each file.

I must double-check that, though.

Assume 100 xls files in a single folder.  I'd like to find a way to copy the first row -- the column headers -- of every xls file over into, say, a text file.  Then in a text editor (with word wrap off) I can easily peruse down making sure all column headers are as they should be -- each column in the text file will have only one particular label.

One possible solution would be to use Skwire's ClipTrap.  Load each of the 100 xls files one at a time, highlight the first row, copy.  But this would take some time.

Hoping someone can come up with an easier way.

Thanks.

Nicholas Kormanik

[nkormanik]

Found basically the same question here:

http://stackoverflow.com/questions/13613049/combining-headers-of-multiple-excel-files-into-the-body-of-a-single-excel-file

With the following vba (?) code solution:

Sub FirstRow()
    Application.DisplayAlerts = False
    Dim strFilename As String
    Dim strPath As String
    Dim wbMaster As Workbook
    Dim wsMaster As Worksheet
    Dim wbFiles As Workbook
    Dim i As Integer

    i = 1
    Set wbMaster = ThisWorkbook
    Set wsMaster = wbMaster.Sheets(2)
    strPath = "C:\path\to\your\files\"
    strFilename = Dir(strPath & "*.xls")

    Do While strFilename <> ""
        Set wbFiles = Workbooks.Open(strPath & strFilename, False)
        wbFiles.Sheets(1).Rows(RowIndex:=1).Copy
        wsMaster.Cells(RowIndex:=i, ColumnIndex:=1).PasteSpecial Paste:=xlPasteAll
        wbFiles.Close (False)
        strFilename = Dir
        i = i + 1
    Loop
    Application.DisplayAlerts = True
End Sub

"A couple of things to note:
- You'll want to change the Sheet(x) references to the appropriate values for your needs
- Ensure there is a "\" at the end of strPath when you put in your own path
- I've turned DisplayAlerts to false during the execution to avoid a pop-up on every file asking if you want to clear the clipboard or not."


Unfortunately I don't understand how to use his solution.  Wish he had have given an example, or explained it more extensively.

[skwire]

Hi, Nick.  Any idea on the maximum number of columns you will be dealing with?

[nkormanik]

Max number of columns would be 250.

That large number means it'll be lots of work without some magic.

[skwire]

Max number of columns would be 250.
That large number means it'll be lots of work without some magic.

Please give this a try:  http://skwire.dcmembers.com/apps/snacks/NKExcelTool.zip

Close all open Excel files and make a folder with copies of your working files.  Put the NKExcelTool.exe in the same folder as your copies and run it.  A progress bar should appear and, once it's finished, it should display a message box asking if you'd like to open the text output file.  The first row of each Excel file should be listed along with the source filename in brackets at the end of each line.  That way, if there is a discrepancy, you'll be able to tell which file is the culprit.  This application uses COM for the grunt work which can be somewhat finicky so let me know how it goes.  Thanks.

[nkormanik]

Skwire, magic indeed.  Works like a charm.

And guess what, ALL my Excel xls column headers/labels lined up perfectly in the output text file your program created, exactly the same in each line, all the way down to the last.  So I can now be certain that a vertical concatenation of all the files should be doable.

I thought of asking for the additional way of knowing which file might be in error, should one be, but didn't want to add to the request.  Very glad you anticipated the need and built it in.

Only change I'd make is to rename it.  YOU deserve all the credit, Skwire.

Thanks a million!

You can mark this really tasty snack as DONE.

[skwire]

Thanks a million!

You're very welcome.  I'm glad it worked for you.

You can mark this really tasty snack as DONE.

Will do.  =]