In Excel 2007, I have a MS-Query QueryTable (Excel to Excel) in a worksheet that I would like to replace with an OLEDB Query (Excel to Access). When I delete the reference to the MS-Query QueryTable (via the Ribbon or VBA) the External Data ribbon is still greyed out and will not allow a new query to be added in it's place without completely removing the Excel 2007 Table.
To Delete the Query I am using either...
Ribbon: Table Tools / Design / External Data Table / Unlink
The existing Excel 2007 Table based on the query has over 2,000 references to it from other worksheets in the workbook (Index/Match lookups, etc...). Is there a way to make the change to OLEDB without removing the existing Excel Table and breaking the formulas that reference it?