Exporting: consolidating and exporting data from multiple Ex

    I am totally stumped...


    I have hundreds of excel (XP) workbooks that contain identical fields and information types. For example, let's say in each and every workbook cell A2 contains the text name; cell B4 contains a dollar amount; cell D6 contains a date;etc. I need to consolidate all of the data from all of these workbooks into a single Access table/db. This table/db would have fields for the text name, the dollar amount and the date and each row would contain the specific data from each workbook. Additionally, I need this table to update itself automatically when new workbooks are created.


    Am I dreaming? Can this even be done? I don't even know in which direction to begin my search for tools/commands/formulae. Any help you could provide would be greatly appreciated!!!

    Moved this to Access Forum.


    Quote


    I have hundreds of excel (XP) workbooks that contain identical fields and information types. For example, let's say in each and every workbook cell A2 contains the text name; cell B4 contains a dollar amount; cell D6 contains a date;etc. I need to consolidate all of the data from all of these workbooks into a single Access table/db. This table/db would have fields for the text name, the dollar amount and the date


    This is definitely something that could be achieved. Your best bet would be to design your Access Db and then use either the transfer spreadsheet option in Access to import your data or write some VBA to export from your files... you could write a procedure that would act on, say, all the workbooks in a directory if indeed they are all identical in structure....


    Can I ask a simple (if stupid) question though? Why have you "hundreds of workbooks" - and why will you continue to create them & need to carry on this procedure (OK 2 Questions :wink2: ) .... once the DB is built & populated, why not cut out the Excel & just enter the future data directly into Access... ?


    Hope this helps...


    PS - why not register here on Ozgrid - much nicer to help a real person :biggrin:


    Will

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login