In this blog post we are specifically going to be looking at Linking and Exporting snapshot data to and from Microsoft Excel. NB you can also partially link to an Excel spreadsheet, but the connection in this case in not completely live - you cannot change data within the Access Database which is then reflected in Excel, although data changed within Excel is reflected within Access.
Exporting Access Data to Excel
Let's begin by going through the procedure of Exporting Access data to Excel. This is a procedure you might go through to export an Access Query or Table to Excel in order to perform further analysis. We shall be using the Access database from my last blog post which you can download from here.
- Open the database containing the table to be exported.
- Click the name of the table in the NAVIGATION PANE so that it is highlighted in orange. Alternatively you could actually open the table.
- Click the EXCEL Icon located in the EXPORT GROUP of the EXTERNAL DATA Ribbon.
- When the EXPORT dialogue box opens, click the BROWSE button and select a location and filename for the newly exported excel spreadsheet. Then click the EXPORT DATA WITH FORMATTING AND LAYOUT and the OPEN THE DESTINATION FILE AFTER THE EXPORT OPERATION IS COMPLETE check box's.
Figure 1: The EXPORT dialogue box for exporting to Microsoft Excel.
- Then click the OK button below. This completes the export process. The finished result can be seen in the screen shot below:
|Figure 2: Table data exported from Access into Excel.|
Now that we have exported our Access Table into Excel, lets have a go at Importing data back into a new Access Table from an Excel spreadsheet. This is a procedure Access Developers often make when they are convert an Excel spreadsheet to Access. For sake of convenience, we are going to Import data from the same Excel Spreadsheet which was created when we did our original Export Procedure.
- Open the Access database into which we are going to Import the data. You can use the same database as before, because we are going to import the data into a new table.
- Click the IMPORT EXCEL SPREADSHEET Icon located in the IMPORT GROUP of the EXTERNAL DATA Ribbon.
- This opens the GET EXTERNAL DATA dialogue box for Microsoft Excel. Click the BROWSE button and search for the spreadsheet file to Import.
- You then have three options. You can Import the data into a new table, append the data into an existing table, or create a link. We are going to Import the data into a new table, so click the top check box which says IMPORT THE SOURCE DATA INTO A NEW TABLE IN THE CURRENT DATABASE. Then click OK.
Figure 3: The GET EXTERNAL DATA dialogue box for Importing to Excel.
- This opens the first page of the IMPORT SPREADSHEET WIZARD (see screenshot below). Our spreadsheet contains column headings which can be used as field names, so click the Check Box which says FIRST ROW CONTAINS COLUMN HEADINGS.
Figure 4: The first page of the IMPORT SPREADSHEET WIZARD.
- Click the NEXT button for the second page of the Import Spreadsheet Wizard. This is where we are able to specify information about each of the fields we are importing - information such as DATA TYPE, INDEX, and FIELD NAME. Click the ID column heading so that it is highlighted (if it is not already). Then change the INDEX to YES(NO DUPLICATES) and the DATA TYPE to LONG INTEGER. This is so we can use the ID field as the table's primary key.
Figure 4: Setting the FIELD OPTIONS in the IMPORT SPREADSHEET WIZARD.
- Click NEXT to move to the third page of the Import Spreadsheet Wizard. This is where we explicitly define the Primary Key for the table. We already have a suitable column to use as the Primary Key, so we do not need Access to do this for us (the default option). As such, click the middle Option Box where it says CHOOSE MY OWN PRIMARY KEY. Then select the ID field from the drop down list (if it is not already selected).
Figure 5: Defining the Primary Key.
- Click NEXT to bring up the final page of the Import Spreadsheet Wizard. This asks you to enter the name of the table. Change the default name to tblExcelImport (a new table name that does not already exist) and then click the FINISH button.
Figure 6: Selecting the new Table Name for the Imported Data.