Hello all,

I have a pivot table created from multiple sheets using SQL statement. The issue is if I want to add another tab, I have to recreate the pivot. I am wondering if it is possible to edit the source SQL directly. I tried to edit the SQL using MS Query but get "Unrecognized database format..." error. Below is my SQL statement that is visible from Change Data Source->Connection Properties->Definition tab.

SELECT * FROM OpexA25R OpexA25R UNION ALL SELECT * FROM OpexARBS OpexARBS UNION ALL SELECT * FROM OpexA254 OpexA254 UNION ALL SELECT * FROM OpexA272 OpexA272 UNION ALL SELECT * FROM OpexA273 OpexA273 UNION ALL SELECT * FROM OpexF2014 OpexF2014 UNION ALL SELECT * FROM OpexB2014 OpexB2014 UNION ALL SELECT * FROM OpexAPYr OpexAPYr

In other words, can I simply do a VBA statement to update this SQL query to source data from additional tab.

Many thanks for your help.

Yes, you can edit source SQL query directly.
However ,as you said 'I have a pivot table created from multiple sheets using SQL statement' ,I think you just need to change your sql statement by click Connection Properties->Definition tab. Why did you need to use Microsoft Query to change the query?
In addition, for the query SELECT * FROM OpexA25R OpexA25R UNION ALL SELECT * FROM OpexARBS OpexARBS ,if your source data is Excel file worksheets, you should change the table name as [OpexA25R$].

In other words, can I simply do a VBA statement to update this SQL query to source data from additional tab.
I think it is hard to achieve, you can submit a case to MSDN forum:
http://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

Wind Zhang
TechNet Community Support