Excel Query - Merging Multiple Spreadsheets

[h***@oddkarma.dot.com]

I'm working on generating a weekly report, which has updated financial
figures. It compares the most recent week's profitability and other stats
to the week prior.

The nice thing is that 90% of the report can be generated automatically
via a database tool. But the rest of the data comes from other excel files
that are not tied into the database tool.

Ideally this is what would happen:

1) Database generates first excel file. This is
the "mater" file.
2) Second external excel file is somehow imported into the first file.
3) Third external excel file is somehow imported into the file.

I think it's possible for me to format at least one of the external files
so that the headers and formats match the master excel file. I don't think
I can do it for the other external file though.

So what I'd appreciate are some suggestions or tips on things I can do to
make my life easier.

At the end of the day, I want one excel file that has data from three
separate excel files, and more importantly, this needs to be done in a
process that's very easy to do. I need something set up so that a
non-technical person can do it.

Any help would be greatly appreciated.

[Sidne Gail Ward]

Post by h***@oddkarma.dot.com
1) Database generates first excel file. This is
the "mater" file.
2) Second external excel file is somehow imported into the first file.
3) Third external excel file is somehow imported into the file.

Will creating links to the cells in the other workbooks solver your
problem? If so, just look at Excel help under "create a link to another
cell, workbook, or program".

Post by h***@oddkarma.dot.com
I think it's possible for me to format at least one of the external files
so that the headers and formats match the master excel file. I don't think
I can do it for the other external file though.

With the links it shouldn't matter if the headers and formats match
(although you can't have one cell with, for example, date formats and
another linked cell with number formats and have it work).

Post by h***@oddkarma.dot.com
So what I'd appreciate are some suggestions or tips on things I can do to
make my life easier.
At the end of the day, I want one excel file that has data from three
separate excel files, and more importantly, this needs to be done in a
process that's very easy to do. I need something set up so that a
non-technical person can do it.
Any help would be greatly appreciated.

Depending on exactly what you need it to do, I would guess it would be
possible to use Visual Basic for Applications with Excel to automate the
entire copy and paste routine. The macro could even be tied to a button on
a worksheet that would activate the macro. Or it could be set to run
automatically when the workbook is opened, etc.

Back from SE Asia and still SARS-free....

[h***@oddkarma.com]

Post by Sidne Gail Ward
Will creating links to the cells in the other workbooks solver your
problem? If so, just look at Excel help under "create a link to another
cell, workbook, or program".

I thought of that. But the # of rows in the input workbooks will change.
So we may have sales data for 50 areas this week, but 20 the next.
Creating links isn't going to work unless they are somehow dynamic.

Post by Sidne Gail Ward

Post by h***@oddkarma.dot.com
Any help would be greatly appreciated.

Depending on exactly what you need it to do, I would guess it would be
possible to use Visual Basic for Applications with Excel to automate the
entire copy and paste routine. The macro could even be tied to a button on
a worksheet that would activate the macro. Or it could be set to run
automatically when the workbook is opened, etc.

Yeah but that sounds like it'll hurt. ANd break.

[Sidne Gail Ward]

Post by h***@oddkarma.com

Post by Sidne Gail Ward
Will creating links to the cells in the other workbooks solver your
problem? If so, just look at Excel help under "create a link to another
cell, workbook, or program".

I thought of that. But the # of rows in the input workbooks will change.
So we may have sales data for 50 areas this week, but 20 the next.
Creating links isn't going to work unless they are somehow dynamic.

No, just creating links won't work if the number of rows/cells will
change.

Post by h***@oddkarma.com

Post by Sidne Gail Ward

Post by h***@oddkarma.dot.com
Any help would be greatly appreciated.

Depending on exactly what you need it to do, I would guess it would be
possible to use Visual Basic for Applications with Excel to automate the
entire copy and paste routine. The macro could even be tied to a button on
a worksheet that would activate the macro. Or it could be set to run
automatically when the workbook is opened, etc.

Yeah but that sounds like it'll hurt. ANd break.

It'll hurt if you don't know VBA. But it shouldn't break.