4

I have an Excel 2013 workbook where I have created multiple tabs with each tab showing the results from a different view from an external database.

I did this by clicking on DATA->OTHER DATA SOURCES where I then setup a connection and selected all of the different views I wanted to see in the workbook. Once I selected the views the results were automatically populated in a new tab in the worksheet.

Now, I want to add another view as a tab in the workbook using the same connection, but cannot find out how to do this and the only way I can do it at the moment is to add the table using a different connection.

How can I do this?

pnuts
  • 58,317
  • 11
  • 87
  • 139
user3165854
  • 1,505
  • 8
  • 48
  • 100
  • Where did you "select all of the different views". When I set up a connection, I can select one view or no views, but not more than one. – Dick Kusleika Oct 09 '15 at 18:02
  • When you enter the connection details it asks to "Select Database and Table" and if you untick the field called "Connect to a specific table" above the tables and views it will ask you to name the connection profile and then when you click finish it asks you to enter the connection details again and then it opens a screen called "Select Table" and it has a tick box to "Enable selection of multiple tables" and then I select them from there – user3165854 Oct 11 '15 at 19:56
  • Interesting.I don't have that tick box. I'll write an answer about what I think I know about Connections. – Dick Kusleika Oct 12 '15 at 12:36

2 Answers2

1

My understanding is that you can't use a connection twice. The connection is tied to the specific SQL statement or table/view name. If you create a connection on one page, and you create a new query on another page using Data - Get External Data - Existing Connections, Excel copies the existing connection rather than running two external data queries through it.

Excel's Connection object has a file associated with it, usually in My Documents\My Data Sources. Even if you select a specific table, that file only contains the connection to the database, not the table/view. So Connections don't appear to be table specific, but they contain other objects and tie them to a specific table.

Every Connection object has an ODBCConnection property and an OLEDBConnection property that point to their respective objects. These objects have a CommandText property for the table name or SQL statement. There's only one of these per Connection, which is why I'm pretty sure that you can't use one Connection for two external data queries.

Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
1

My method is:

  1. (With your Excel Workbook open) Select Data Tab > Queries & Connections, the "Queries & Connections" pane opens to the right.

  2. Right-Click any of the existing queries

  3. Select Edit, opening Power Query

  4. Select Home tab > Recent Sources (in the New Query group)

  5. Select the source you want to pull more data from

The Navigator populates - the screen that displays when you first connect to a data source. From here you can select one or more tables from the source and add them to your data model.

I know its a late answer - but I hope this helps!

Kyle Deer
  • 91
  • 8