Updating SQL statement for Pivot Data Source

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.

May 26th, 2014 11:01am

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

Free Windows Admin Tool Kit Click here and download it now
May 28th, 2014 3:09am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics