topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Thursday March 28, 2024, 12:31 pm
  • Proudly celebrating 15+ years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

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

nkormanik

  • Participant
  • Joined in 2010
  • *
  • Posts: 552
    • View Profile
    • Donate to Member
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

  • Participant
  • Joined in 2010
  • *
  • Posts: 552
    • View Profile
    • Donate to Member
Found basically the same question here:

http://stackoverflow...-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

  • Global Moderator
  • Joined in 2005
  • *****
  • Posts: 5,286
    • View Profile
    • Donate to Member
Hi, Nick.  Any idea on the maximum number of columns you will be dealing with?

nkormanik

  • Participant
  • Joined in 2010
  • *
  • Posts: 552
    • View Profile
    • Donate to Member
Max number of columns would be 250.

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


skwire

  • Global Moderator
  • Joined in 2005
  • *****
  • Posts: 5,286
    • View Profile
    • Donate to Member
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

  • Participant
  • Joined in 2010
  • *
  • Posts: 552
    • View Profile
    • Donate to Member
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

  • Global Moderator
  • Joined in 2005
  • *****
  • Posts: 5,286
    • View Profile
    • Donate to Member
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.  =]