Import several access table data to excel 2007

[ajv015]

Hello,

I am trying to import access data to excel and I using the following path Data>From Access> "Select the access file that I need"> Open> and so the show a window with several tables in the access but I only can select one by one and import they separatelly.
The point is that the window does not show an icon where I can click an select multiple tables to import.

I donīt know if it is a issue on my excel or my tables, but I very upset because my idea is to use this tool to create a dynamic pivot table where I can use the data of all my access table together and have more power of analysis, like a olap cube.

I really appreciate if you could help me with that issue.

Thanks a lot,

Cris

[rscsmith]

Hi Cris

I don't know the structure of your database, but I don't think you can select multiple tables to import at the same time. I believe you would have to do separate imports in to different areas of the spreadsheet. Also, bear in mind that Pivot Tables require unique field names and can only reference one table.

In the past when I have done this I have imported access tables to several different sheets then used formulas adjacent to the 'main' table to align the data using formulas such as SUMIF, VLOOKUP, INDEX, etc. This gave me a large table that I could then use to create my PTs. Excel automatically adds/deletes rows of formulas adjacent to an imported table to match the length of the table.

Another alternative, depending on your data structure and way of working, is to create a query in Access that amalgamates the data in to one large table then import that in to Excel.

Hope some of this helps.

Cheers, Rob.