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

DonationCoder.com Forum

DonationCoder.com Software

Coding Snacks

Finished Programs (Moderator: skwire)

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

Pages: [1] Go Down

« previous next »

Reply | New Topic | Print

Author

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

nkormanik

Participant

Posts: 207

Give some DonationCredits to this forum member

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

« on: April 09, 2013, 02:26:48 AM »

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


	Logged

nkormanik

Participant

Posts: 207

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

« Reply #1 on: April 09, 2013, 03:11:02 AM »

Found basically the same question here:

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


	Logged

skwire

Moderator

Posts: 3,448

Another Coding Snack request? Om nom nom...

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

« Reply #2 on: April 09, 2013, 10:27:30 AM »

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


	Logged

[ may-june 2013 ad experiment; click here to learn more about donationcoder.com ]

nkormanik

Participant

Posts: 207

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

« Reply #3 on: April 09, 2013, 08:18:50 PM »

Max number of columns would be 250.

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


	Logged

skwire

Moderator

Posts: 3,448

Another Coding Snack request? Om nom nom...

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

« Reply #4 on: April 09, 2013, 11:03:50 PM »

Quote from: nkormanik on April 09, 2013, 08:18:50 PM

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.


	Logged

nkormanik

Participant

Posts: 207

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

« Reply #5 on: April 10, 2013, 12:01:14 AM »

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.