Excel has its own method of importing external data from a variety of sources, including data in the SQL Server. This method can be embedded directly in the workbook or stored in a separate file, the Office Data Connection (ODC) file. When this .odc file is created, it can be reused over and over for a quick connection between your GP and Excel that is refreshable.
Let's create an .odc file to bring in our vendors using these steps:
In Excel 2016, go to Data | From Other Sources | From SQL Server:
The Data Connection window will open. Enter the name of your server and your GP log in credentials. Click on Next.
Select the database you want to report on and then select the view called
Vendors. Click on Finish:
The Import Data window will open, providing you with the option of either importing the data into an Excel Table, a PivotTable Report/PivotChart, or just creating the connection between SQL and Excel. Let's leave the option marked as Table and click on OK. The data will then import into the spreadsheet:
We'll find the location of the .odc file we created earlier, with the spreadsheet still open. Follow these steps:
Open the Connection Properties window by going to Data | Connections | Properties…
Click on the Definition tab, and the Connection File path will be displayed. For me, our file and path is
C:\Users\Belinda Allen\Documents\My Data Sources\cherry TWO Vendors.odc. As you can see, the actual.odcname is a combination of your server, your database, and the table/view you used for the connection:
One of the great features of using an .odc file is that once it is created, it can be used over and over again. Let's reuse the connection file we just created and follow these steps:
Open a blank Excel workbook. Go to Click Data | Existing Connections and scroll to find the connection you just created. For us, it is cherry TWO Vendors. Click on Open:
The Import Data window will open, asking how you want to view your data: Table, PivotTable Report/PivotChart, or if you just want to create a connection.
You'll then be prompted for your SQL login, which is the same as your GP login.