Excel Multiple Merge Issues

[gestaltphoenix]

Ok, heres the deal...

I have 3 different Excel reports coming from our SAP database that I need to merge into 1 customized report. In other words, with reports A,B, & C, I need to to merge parts of B&C into A. All 3 reports have a particular column (Material Codes) that need be merged with respect to.

To make it (hopefully) a little clearer...
Report A has material codes with descriptions, selling price, etc...report B has repair prices that need to be matched with the material codes from A. Report C has usage data that needs to be associated with the material codes. They all have to be in one big happy spreadsheet to send out.

Considerations...
1.) I need to automate the process (I was thinking to use an access form which allows a user to type in file paths, and then scripting a macro to do the actual merge). Is there a better way to do this in Excel???

2.) The reports come out of SAP in a format that is difficult for Access to import and maintain data integrity (as in the field name for a given column takes up cells A1 and A2, and access can only use one row for field names).

I can record a macro that changes formats in Excel, and then import those files into Access in two seperate steps, but ideally I want it to be a streamlined process. I'm leaving soon and want to have remaining group members be able to preform this whole function after I go (i.e. type in file paths, click "ok" and they're done).

So....I'm open to suggestions...I have some scripting experience, but I'm no guru....

Thanx for taking the time!!!!

 

[Hasit]

If you can get the data from SAP into something that supports ODBC and SQL querys (sorry I know nothing about SAP), then the merging of the data is very very simple, by using MS Query.

Through MS Query you will be able to merge data BEFORE it is extracted and published in Excel, so you can extract data based on a particular reference point - in your case Materials Code.

I have done this with a pretty complex Access database and also a Foxpro one (the Access Db had around 150,000 entries by around 50) and the extraction (and automatic update) took around 20 seconds.

 

[gestaltphoenix]

Hasit...

Many thanx for the quick response...

Pardon my ignorance, but doesn't Access 2000 support SQL queries?

Also, if you have a minute, I'd be interested in a summary of how you designed that access dbase. I'll be working with up to 10,000 different codes, each of which has about 20 pieces of associated data. Anything is a help....)

Thanx again

Justin (gestaltphoenix)

 

[Hasit]

Justin,

Sorry, my point was not that Access doesn't support SQL (it does), but that I did not know to get the data from SAP to (for example) Access.

The structure of the Access database was very very simple. I had a number of tables (actually around 22) and each table had a set of columns. I based the refernce data as the Building ID (the db was a property portfolio).

Hope that helps.

Hasit.