You can link Excel to a SQL Server database so that you can read data from SQL Server tables and view them in Excel worksheets. You will have a read-only link in Excel. You are importing SQL Server into Excel. When the database tables change, you just need to click the Refresh button in Excel to update the data. This makes it mush easier to view the data and copy it. Since you are in Excel, you can filter the rows and you can search for a specific value or values in a column.
Set up a Link
- Open an Excel file
- Click the Data tab in Excel
- Click Get External Data From Other Sources
- Choose From SQL Server, which is the first one.
- Enter the Server Name
- Select your Log on credentials, click Next
- From the drop-down, select the database you need (RBCxml)
- Click on the table you want to view. You can repeat this entire process for each table you want.
- Save Connection and Finish
- Import Data: leave it set to Table and click the New Worksheet radio button and click OK
- It creates a new worksheet. Rename your worksheet to reflect the table name.
- In the Data tab, click Refresh All.
Here are a few specific details about step 9, Save Connection and Finish.
- File Name: leave it but click Browse and choose a Location
- Description: enter a description
- Friendly Name: remove the server name, keep the rest
- Click Finish
- To get the server name, open SQL Server Management Studio (SSMS) and click the New Query button and Execute SELECT @@servername
- If Windows authentication does not work, you will need to get the username and password from your SQL Server Administrator.