merge two excel sheets like "union all" query in msquery

[pioniereelfico]

Hi guys, I'm new of the world of small office mc guiver's use of excel. So I think you can help me, and I'll do my best to solve other's problem.
In this two words you can understand I'm not english/american (or other country spoking this language) but I'm italian, hope is not a problem. I'm currently working for a medium italian farm and my boss break me balls with his request of data connection, data analysis etc.. Moreover, there are no moneys for do the basic data elaboration with small software we buy (asking money here is more diffucult than have sex).

Here's the problem:
we have two sheets in excel, with equal data format (same columns name). Those data are extracted from 2 database server (one here in Orzinuovi, the other near Pavia). Those data are financial data, so my boss need to see a global pivot of those data and maybe refresh it. I remember there's no money, elsewhere I'll do a *serious*vpn connection and connect two database.
What we need is a sum of data in sheet1 (Brescia) and sheet2 (Pavia) and put that data in sheet3 (Consolidato). I' used to work with msquery and UNION ALL operator, but if you change location of the file, or name or sometimes, it breaks, and I must looose my break for those breaks and re-do the connection.

The question is:
Is there any macro to merge two sheets that can be called clicking a button, fix the query and update data in sheet3?

Simple schema for rapid understand:

this should be refreshed in quick time

MSQL--->sheet1,
.......................|--->MSQUERY--> sheet3---> pivot
MSQL--->sheet2'


thank you very much for the help

 

[Fazza]

Ciao,

As you would like, the pivot table can pull the data from both sheets using UNION ALL.

Google should find examples.

Basically, ALT-D-P to start the pivot table wizard and take the external data option at the first step. From memory - I might be wrong - maybe best done from a new workbook; then at the end, you can move the final worksheet containing the pivot table into your source data file if you like.

If you get a message about no visible tables, acknowledge it then via 'options' select 'system tables'. You should now be able to select one worksheet as the data source. Follow the wizard to the end & take the option to edit in MS Query. Via the 'SQL' button manually create the SQL of form,

SELECT *
FROM [YourSheet1Name$]
UNION ALL
SELECT *
FROM [YourSheet2Name$]

When done in MS Query exit via the 'open door' icon & complete the pivot table.

I'll google for some links

regards

 

[Fazza]

http://www.mrexcel.com/forum/showthread.php?t=315768 should help

I'm not familiar with the details, though expect http://www.contextures.com/xlPivot08.html will be helpful: there is loads of great info at Debra's excellent site.

regards

 

[Fazza]

PS The link to the old Mr Excel thread will be for Excel 2003. If you're using the code, modify to suit newer versions. IIRC, will be the connection string only. F

 

[pioniereelfico]

Hi Fazza, thanks for fastest reply of the west
The code work really good, it was simple, clear, fast, but it deleted my 2 MSSQL database connection (sheet1 and sheet2 in the upper graph)

 

[Fazza]

If you can't modify it to suit your set up, maybe use the manual approach (my first post).

 

[OldManExcellor]

Basically, ALT-D-P to start the pivot table wizard and take the external data option at the first step.

Why isn't there a button for this in the ribbon? I had the same problem as the original poster and was scratching my head. I tried to find ways to add data from multiple source to a pivot table but was unsuccessful. Without google it would have been "impossible" to find out about the Wizard? Shouldn't there be a button for it in the pivottable drop down menu?

I'm asking because I can't understand how much Excel sucks at times (I'm still new to serious Excel).

 

[Fazza]

Why isn't there a button for this in the ribbon?

I know nothing about the ribbon, sorry. I use Excel 2003. I find it easier to refer to ALT-D-P (as it covers all versions AFAIK).

I guess that in Excel versions with ribbons the pivot table wizard will be found at the first step of creating a pivot table (from the ribbon). Maybe try Excel help for pivot table wizard.