abenitez77
asked on
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
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
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?
ASKER
For the most part for what i've seen they all have the same columns and headers and in the same order. YES
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.
ASKER
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.
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.
ASKER
- 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
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
ASKER
- 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
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
ASKER
- 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
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
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.
ASKER
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.
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.
ASKER
any progress?
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.
ASKER
I could run it from a *.vbs file, p.s. i will most like only run this from 1 or 2 pc's.
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.
ASKER
ok, i'll install the activex control. That seems to be our easiest option.
Ok, I'll get it reworked to use the control and then debug it and send it to you when it's fixed.
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. :(
ASKER
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
Thanks for all your hard work,
alex
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.
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.
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
Moderator, my recommended disposition is:
Refund points and save as a 0-pt PAQ.
DanRollins -- EE database cleanup volunteer
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.