Connecting to Excel In Access

 Connecting to Excel In Access

Access allows users to connect to various data sources, such as SQL, other databases, XML files, and SharePoint, as well as Excel files. In order to ensure a proper importing and/or linking, the data needs to have headers for each column of information. Also, there can be no entire columns or rows of information missing, and no additional information, such as worksheet titles, on the worksheet being used for the connection. The worksheet should look similar to this example:Connecting to Excel in Access - Worksheet

After you have created your database (or utilize an existing database), setup the connection between Access and Excel following these steps:

  1. Click the External Data tab, go to the Import & Link group, and click the ExcelConnect to Excel in Access - Database
  2. Once the dialog box appears, browse and select the file in which you keep the desired data.Connect to Excel in Access - File
  3. We are going to select Link to the data source by creating a linked table for our example.Connect to Excel in Access - Linked Table
  4. Click OK to take you to the next step.
  5. Next, select the worksheet that has the information you want linked. If you have specific Named Ranges you want to use, then you can also choose them during this step. (To learn more about Named Ranges, check out our Excel Advanced training course.)Connect to Excel in Access - Named Ranges
  6. Ensure your data is listed in the next section, then click the Next button at the bottom of the dialog box.Connecting to Excel with Access - Data
  7. Make sure the First Row Column Headers radio box is checked, so Access knows what to name the columns, then click Next.Connect to Excel in Access - Column Headings
  8. Type in the name to be used for your new table. Coect to Excel in Access - Name
  9. Click Finish.
  10. Click OK, when the confirmation dialog box appears.Connecting to Excel with Access - Confirm
  11. Double-click your new table in the Navigation Pane to the left, when it appears, and your new table will populate.Connecting to Excel with Acess - Navigation Pane

This link is always active when the database is open. Any changes made to the Excel workbook will be immediately visible in your new table.

Get more ways to connect your data with an Access class from Mission Critical Training.