• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

Append all Excel files with multiple worksheets(tabs) into 1 Excel file or txt file.

I have about 50 excel files with 4 sometimes 5 or 6 worksheets or tabs with about 10-20 Columns.  
e.g.
- SPI, ECR, WCR, AOT worksheets in every excel file.
- The first row contains the field name.
- The name of the excel file is a date or week of a date. e.g. wk40.xls ( week 40)

I want to append All Tabs from the 50 excel files into 1 excel file, but i want to create 2 new columns that will contain the Week and Type(the name of the Worksheet).
e.g.  week   Type  Column1   column2   column3 etc..
       40     SPI    51.88    01/01/02    33
       40     SPI    22.48    01/05/02    15
       40     SPI    48.15    01/03/02     4
       40     ECR    18.14    05/04/02    77
       40     ECR     9.99    02/05/02    25
       40     ECR    71.04    09/11/02     1
etc...

I want to populate the week column with the name of the file and Type with the name of the worksheet or Tab in Excel and i want to append all these into 1 master excel file.  All the files are in the same folder.  how can i do this programmatically?

Thanks in Advance,
Alex
0
abenitez77
Asked:
abenitez77
1 Solution
 
Wowbagger_TIPCommented:
Hmm... your question has quite a few parts to it.  Basically what you need to do is create an Excel application object and 2 Excel workbook objects and use one to open the destination workbook and the other to open each source workbook in turn and read in the data and write it back out to the destination workbook.  Do all the sheets have the same number of columns?  Do the columns always have the same headers?  Are they always in the same order?
0
 
abenitez77Author Commented:
For the most part for what i've seen they all have the same columns and headers and in the same order. YES
0
 
Wowbagger_TIPCommented:
Is this a one-time thing, or will it need to be done often?  I'm working out a solution in my head at the moment, but part of it will be the selection of the sheets to append, and how I do that will depend on how often it will be used.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
abenitez77Author Commented:
Good question, i'm not sure but i want to keep it incase we get more of these.  I can think of other occassions when we will get and we have in the past, lots of excel files and we need to append them to 1.  I guess i can always modify it to work for me later on something similar.  By the way i'm finding that there are some files that have worksheets with extra fields or less fields... but it is only in a few.
0
 
Wowbagger_TIPCommented:
Hmm... it becomes difficult to automate it when the sheets are different.  Are the ones with fewer or more fields still using the same headers as the others?  If the fields aren't always the same and in the same order in every sheet, then it becomes necessary to know exactly what is in each column on any given sheet.  The only way to do this would be to go by the column headers.  So, I would need a list of all possible column headers and then I would have to search each sheet for each header and put the data from that column into the proper column on the destination sheet.
0
 
abenitez77Author Commented:
- not in file but want to create these 2:
week
type

- this is what i found so far:
scac
car name
pro no
ship date
paid amt
pcfz
acct no
corp code
origin
dest 1
dest 2
desc 1
desc 3
desc 4
desc 2
orct
orst
ozip
dect
dest
dzip
0
 
abenitez77Author Commented:
- not in file but want to create these 2:
week
type

- this is what i found so far:
scac
car name
pro no
ship date
paid amt
pcfz
acct no
corp code
origin
dest 1
dest 2
desc 1
desc 3
desc 4
desc 2
orct
orst
ozip
dect
dest
dzip
0
 
abenitez77Author Commented:
- not in file but want to create these 2:
week
type

- this is what i found so far:
scac
car name
pro no
ship date
paid amt
pcfz
acct no
corp code
origin
dest 1
dest 2
desc 1
desc 3
desc 4
desc 2
orct
orst
ozip
dect
dest
dzip
0
 
Wowbagger_TIPCommented:
Ok, I'll work on a solution for it.  How soon do you need it?  Got some other spreadsheet code to work on at the moment, so I might not be able to get to this until tonight or tomorrow.  Oh, and if you find any other column headers, then let me know so I can add them.
0
 
abenitez77Author Commented:
haven't found any new columns.  Would have been nice to have it by tonight, but tommorow will still be just as good.  Thanks for all your efforts.
0
 
Wowbagger_TIPCommented:
Ok, I think I'll write it so that it will ignore any columns that aren't in your list.  Less chance of errors that way.  I'll get to it as soon as I can.
0
 
abenitez77Author Commented:
any progress?
0
 
Wowbagger_TIPCommented:
well, I've run into a big problem that I haven't figured out yet.  I normally use a custom activex control for opening a file dialog.  I don't know if that's an option for you though.  It would have to be installed and registered on each computer that you want to run this code on.  The alternative is to use the win32 api directly, which is what I've been attempting to get working.  It works in VB6, but I haven't gotten it to work in an Excel code module yet.
0
 
abenitez77Author Commented:
I could run it from a *.vbs file, p.s. i will most like only run this from 1 or 2 pc's.
0
 
Wowbagger_TIPCommented:
Well, the problem is that VB Script will not let you declare a public user-defined type in a module, and you can't pass a private type as a parameter to a public procedure (win32 api function call).  So that seems to be a dead end.  If you're willing to install an activex control on those PCs, then I can send you the control (it's a free control, so no licensing issues) and instructions for installing it.  I'll just rework the code to use the control instead.
0
 
abenitez77Author Commented:
ok, i'll install the activex control.  That seems to be our easiest option.
0
 
Wowbagger_TIPCommented:
Ok, I'll get it reworked to use the control and then debug it and send it to you when it's fixed.
0
 
Wowbagger_TIPCommented:
Sorry if it seemed like I'd abandoned this.  I had some major computer problems and ended up having to reformat and reinstall everything on my system and try to salvage some things that I needed for work.  I do have a working VBA function for you now (or at least it works with the test files I set up).  If you still want it, then I can email you the control you'll need, along with the excel file containing the code.  Sorry about the delay. :(
0
 
abenitez77Author Commented:
No problem, i would still like that app if you can email to me: alex.benitez@prgx.com

Thanks for all your hard work,
alex
0
 
CleanupPingCommented:
abenitez77:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
DanRollinsCommented:
abenitez77, an EE Moderator will handle this for you.
Moderator, my recommended disposition is:

    Refund points and save as a 0-pt PAQ.

DanRollins -- EE database cleanup volunteer
0
 
YensidModCommented:
This question is PAQed and 50 points refunded.

YensidMod
Community Support Moderator
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now