Excel Clinic - Adding a Connection to a Table
Apr 24, 2021 | 2:14 pm
#1
Original Poster
FlyerTalk Evangelist
Join Date: Nov 2002
Location: ORD
Posts: 14,771
Excel Clinic - Adding a Connection to a Table
I have an existing table in Excel (not a pivot table, just a regular table) that I've been updating by pasting in the contents of a similar table I download periodically from a reporting site. I now have the ability to access the same data directly from an Oracle database. How do I modify the existing table in Excel to pull data from the db? I know how to make the db connection. But there doesn't seem to be a way to add a connection to an existing table. I don't want to create a new connection and new table, and delete the old one, because it'll invalidate all the formulas throughout the workbook that refer to the existing table.
Apr 24, 2021 | 3:58 pm
#2
Join Date: Nov 1999
Location: ABQ
Programs: SPEBSQSA
Posts: 3,794
I've run into the same issue with connections to SAS datasets (in pivot tables and tables.) Unfortunately, I did not find a way. I had to create a new table and then point the calculations to the new table. You can often do this as a global change in the worksheet(s) with the formulas, but not always. I'll be paying attention to see if someone has a solution.
Apr 26, 2021 | 9:57 am
#3
Original Poster
FlyerTalk Evangelist
Join Date: Nov 2002
Location: ORD
Posts: 14,771
Apr 26, 2021 | 6:31 pm
#5
FlyerTalk Evangelist
Join Date: Jun 2002
Location: n.y.c.
Posts: 14,059
Power Query is the way to go. You can also use your existing table as a PQ data source, then append it to the Oracle data source, before bringing it back into Excel, if it needs to be seamless to the end-user (Power Query can merge/append/join, etc).