?
Solved

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

Posted on 2003-03-12
22
Medium Priority
?
450 Views
Last Modified: 2010-07-27
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
Comment
Question by:abenitez77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
22 Comments
 
LVL 1

Expert Comment

by:Wowbagger_TIP
ID: 8121992
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
 

Author Comment

by:abenitez77
ID: 8122148
For the most part for what i've seen they all have the same columns and headers and in the same order. YES
0
 
LVL 1

Expert Comment

by:Wowbagger_TIP
ID: 8122349
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
Independent Software Vendors: 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!

 

Author Comment

by:abenitez77
ID: 8122503
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
 
LVL 1

Expert Comment

by:Wowbagger_TIP
ID: 8122555
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
 

Author Comment

by:abenitez77
ID: 8122696
- 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
 

Author Comment

by:abenitez77
ID: 8122873
- 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
 

Author Comment

by:abenitez77
ID: 8122912
- 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
 
LVL 1

Expert Comment

by:Wowbagger_TIP
ID: 8122956
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
 

Author Comment

by:abenitez77
ID: 8123054
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
 
LVL 1

Expert Comment

by:Wowbagger_TIP
ID: 8123120
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
 

Author Comment

by:abenitez77
ID: 8137938
any progress?
0
 
LVL 1

Expert Comment

by:Wowbagger_TIP
ID: 8138916
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
 

Author Comment

by:abenitez77
ID: 8139117
I could run it from a *.vbs file, p.s. i will most like only run this from 1 or 2 pc's.
0
 
LVL 1

Expert Comment

by:Wowbagger_TIP
ID: 8139197
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
 

Author Comment

by:abenitez77
ID: 8139303
ok, i'll install the activex control.  That seems to be our easiest option.
0
 
LVL 1

Expert Comment

by:Wowbagger_TIP
ID: 8139322
Ok, I'll get it reworked to use the control and then debug it and send it to you when it's fixed.
0
 
LVL 1

Expert Comment

by:Wowbagger_TIP
ID: 8177432
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
 

Author Comment

by:abenitez77
ID: 8177528
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
 

Expert Comment

by:CleanupPing
ID: 9056391
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
 
LVL 49

Expert Comment

by:DanRollins
ID: 9664546
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
 

Accepted Solution

by:
YensidMod earned 0 total points
ID: 9756355
This question is PAQed and 50 points refunded.

YensidMod
Community Support Moderator
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month10 days, 18 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question