This article is for people with visual impairments who use a screen reader program with the Office products and is part of the Office Accessibility content set. For more general help, see Microsoft Support home.
Use Access with your keyboard and a screen reader to import an Excel worksheet to Access or link from an Access desktop database to an Excel worksheet. We have tested it with Narrator, JAWS, and NVDA, but it might work with other screen readers as long as they follow common accessibility standards and techniques.
Notes:
-
New Microsoft 365 features are released gradually to Microsoft 365 subscribers, so your app might not have these features yet. To learn how you can get new features faster, join the Office Insider program.
-
To learn more about screen readers, go to How screen readers work with Microsoft Office.
In this topic
Prepare the Excel worksheet for import or linking
Before bringing data from an Excel worksheet to Access, take a moment to review the worksheet and decide how you want to import data from it.
-
Decide whether you want to link to or import the whole worksheet or only a portion of it. If you want to bring only a portion of a worksheet to Access, you can define a named range that includes only the cells you want to bring. For instructions, go to Use a screen reader to name a cell or data range in Excel.
-
Make sure that the Excel worksheet is in flat file format so that the wizard can easily handle the data in it. For example, fix possible error values, remove unnecessary blank columns and rows, and check that each column contains the same data type in every row. For more detailed information on how to prepare for import or linking, refer to the Prepare the worksheet, Prepare the Excel data, and Troubleshoot missing or incorrect values sections in Import or link to data in an Excel workbook.
Prepare the destination Access database for import
Before you import data from an Excel worksheet, take a moment to check that the destination Access database is ready for the import and decide how you want to store the imported data.
-
Make sure that the destination Access database is not read-only, and that you have permissions to make changes to the database.
-
Decide whether you want to store the data in a new or existing table:
-
If you choose to store the data in a new table, Access creates a table and adds the imported data to this table. If a table with the specified name already exists, Access overwrites the contents of the table with the imported data.
-
If you choose to add the data to an existing table, the rows in the Excel worksheet are appended to the specified table.
-
-
If you decide to append the Excel data to an existing Access table, make sure that the structure and field settings in the source data in Excel match the ones in the destination table in Access. To open the Access table in the Design View for inspection, press Alt+H, W, D. Check the table against the checklist in the Prepare the destination database section, step 2, in Import or link to data in an Excel workbook.
Import the data
When you import data, Access creates a copy of the data in a new or existing table without changing the source Excel worksheet.
Note: You may need a colleague to help you in step 9 to select the field you want to edit.
-
Close the source Excel workbook, if it is open.
-
Open the destination Access database where the imported data will be stored.
Tip: If you want to create a new, blank database, press Alt+F, N, L.
-
To open the Get External Data - Excel Spreadsheet dialog box, do one of the following depending on the Access version you're using:
-
In the Microsoft 365 subscription version or Access 2019, press Alt+X, N, 1, then F, and X.
-
In Access 2016 or Access 2013, press Alt+X to go to the External Data tab. Press the Tab key until you reach the Import & Link group, press the Right arrow key until you hear "Excel," and press Enter.
The Get External Data - Excel Spreadsheet dialog box opens.
-
-
In the Get External Data - Excel Spreadsheet dialog box, do one of the following to specify the Excel file that contains the data that you want to import:
-
To move the focus to the File name text field, press F, and then paste or type the path to the Excel file.
-
To browse for the file, press R. The File Open dialog box opens. Navigate to the file you want, and when on the file, press Enter to select it.
-
-
To specify how you want to store the imported data, do one of the following:
-
If you want to store the data in a new table, press I to move to and select the Import the source data into a new table in the current database option. You will be prompted to name this table later.
-
If you want to append the data to an existing table, press A to move to and select the Append a copy of the records to the table option. To select the table, press the Tab key once. The focus moves to the tables list. Press Alt+Down arrow key to expand the list, press the Up or Down arrow key until you hear the table you want, and press Enter to select it. This option is not available if the database has no tables.
-
-
Press the Tab key until you reach the OK button, and press Enter. The Import Spreadsheet Wizard starts.
Tip: If your screen reader doesn't automatically read the wizard windows, press the SR key+W.
-
If the source Excel workbook has only one worksheet and no ranges, you can proceed to the next step. If the workbook contains more than one worksheet or named range, do one of the following to select the worksheet or range to import:
-
To select a worksheet, press Alt+W, press the Tab key once, press the Down arrow key until you hear the worksheet you want, and press Enter to select it.
-
To select a named range, press Alt+R, press the Tab key once, press the Down arrow key until you hear the range you want, and press Enter to select it.
The next wizard window opens.
-
-
If the first row of the source worksheet or range contains the field names, press I to move to and select the First Row Contains Column Headings option. Press Enter to move the focus to the next wizard window.
If you are appending data to an existing table, you can proceed to step 11.
-
In the new wizard window, you can specify information about each field that you're importing, if needed. The first field in the worksheet or range is selected. You can do the following:
-
To change the name of the current field, press Alt+M and type the new name.
-
To change the data type of the field, press Alt+T. The focus moves to the Data Type list. To expand the list, press Alt+Down arrow key. Press the Down arrow key until you hear the value you want, and then press the Tab key once.
-
To change whether the field is indexed or not, press Alt+I. You hear the current value. To expand the list, press Alt+Down arrow key. Press the Down arrow key until you hear the value you want, and then press the Tab key once.
-
If you don't want to import the current field, press Alt+S to move to and select the Do not import field option.
To select another field, press F6 until you hear "Pane," and ask a colleague to help you select the field you want. Modify the fields as described above. Once you're done, press Enter to move to the next wizard window.
-
-
Add a primary key for the new table. The primary key identifies the records in your table so you can retrieve data more quickly. Do one of the following:
-
To let Access add the primary key, press A.
-
To choose your own primary key, press C. To define the key to be used, press the Tab key once. The focus moves to the key list. To expand the list, press Alt+Down arrow key. Press the Up or Down arrow key until you hear the key your want.
-
If you don't want to use primary keys, press O.
To move the last wizard window, press Enter.
-
-
The final wizard window opens. The focus is in the Import to Table box, and you hear the current name of the destination table. If you want to change the table name, type a new name, and press Enter to finalize the import.
Note: If you hear an alert that the table already exists, and you want to overwrite the existing table with the imported data, press Y. If you want to rename the table, press N. Press Alt+I to move the focus back to the Import to Table box, type another table name, and press Enter.
-
If the import was completely or partially successful, Access displays the import status. Access also prompts you to save the import steps to quickly repeat them without the import wizard. To select this option, press Alt+V. To name the import steps, press Alt+A, and type a name for the import operation. To save the import steps, press Alt+S.
If the import fails, you hear "An error occurred trying to import file," followed by the file name. Press Enter to exit the alert message.
Create a link to data in Excel
Use the Link Spreadsheet Wizard to create a link from an Access database to data in Excel, so you can use the querying and reporting tools in Access without having to maintain a copy of the Excel data in your database.
When you link to an Excel worksheet or a named range, Access creates a new table that is linked to the source cells. Any changes that you make to the source cells in Excel appear in the linked table in Access. However, you cannot edit the contents of the corresponding table in Access. If you want to add, edit, or delete data, you must make the changes in the source Excel file.
A database can contain multiple linked tables.
Start the Link Spreadsheet Wizard
In Access, there are two ways to select the Excel workbook to which you want to link, and start the Link Spreadsheet Wizard. You can either open the Excel workbook in Access or use the Get External Data - Excel Spreadsheet dialog box.
Open the Excel workbook in Access
-
Open the Access database in which you want to create the link.
-
Press Alt+F, O to go to the Open menu.
-
To browse for the workbook, press O. The Windows Open dialog box opens.
-
To change the file type, press the Tab key until you hear "Files of type, Microsoft Access," and press Alt+Down arrow key. Press the Up arrow key until you hear "All files," and press Enter.
-
Browse to the workbook you want, and then press Enter to select it and start the Link Spreadsheet Wizard.
Use the Get External Data - Excel Spreadsheet dialog box
-
Open the Access database in which you want to create the link.
Tip: If you don't want to link to an existing database, press Alt+F, N, L to create a new, blank database.
-
To open the Get External Data - Excel Spreadsheet dialog box, do one of the following depending on the Access version you're using:
-
In the Microsoft 365 subscription version or Access 2019, press Alt+X, N, 1, then F, and X.
-
In Access 2016 or Access 2013, press Alt+X to go to the External Data tab. Press the Tab key until you reach the Import & Link group, press the Right arrow key until you hear "Excel," and press Enter.
The Get External Data - Excel Spreadsheet dialog box opens.
-
-
In the Get External Data - Excel Spreadsheet dialog box, do one of the following to specify the Excel file that you want to create the link to:
-
To move the focus to the File name text field, press F, and then paste or type the path to the Excel file.
-
To browse for the file, press R. The File Open dialog box opens. Navigate to the file you want, and when on the file, press Enter to select it.
-
-
Press Alt+L to move to and select the Link to the data source by creating a linked table option, and press Enter. The Link Spreadsheet Wizard starts.
Use the Link Spreadsheet Wizard to create a link to Excel data
The Link Spreadsheet Wizard windows guide you through the linking process. Just select the options you need, and have the Excel data linked to your database in no time.
Tip: If your screen reader doesn't automatically read the wizard windows, press the SR key+W.
-
If the source Excel workbook has only one worksheet and no ranges, you can proceed to the next step. If the workbook contains more than one worksheet or named range, do one of the following to select the worksheet or range:
-
To select a worksheet, press Alt+W, press the Tab key once, press the Down arrow key until you hear the worksheet you want, and press Enter to select it.
-
To select a range, press Alt+R, press the Tab key once, press the Down arrow key until you hear the range you want, and press Enter to select it.
The next wizard window opens.
-
-
If the first row of the source worksheet or range contains the field names, press I to move to and select the First Row Contains Column Headings option. Press Enter to move the focus to the next wizard window.
-
The final wizard window opens. The focus is in the Linked Table Name box, and you hear the current name of the linked table. If you want to change the table name, type a new table name, and press Enter to finalize the import.
Note: If you hear an alert that the table already exists, and you want to overwrite the existing table with the imported data, press Y. If you want to rename the destination table, press N. Press Alt+I to move the focus back to the Import to Table box, type another table name, and press Enter.
-
If the linking was successful, you hear "Finished linking table to file," followed by the Excel file name. Press Enter to exit the notification. Navigate to the linked table and review its content. For troubleshooting info, go to Troubleshoot #Num! and other incorrect values in a linked table in Import or link to data in an Excel workbook.
If the linking failed, check the Excel source file again against the checklist in the Prepare the destination database section, step 2, in Import or link to data in an Excel workbook.
See also
Use a screen reader to create tables in Access desktop databases
Use a screen reader to create a query in Access desktop databases
Use a screen reader to create a form in Access desktop databases
Use a screen reader to export an Access table to a text file
Technical support for customers with disabilities
Microsoft wants to provide the best possible experience for all our customers. If you have a disability or questions related to accessibility, please contact the Microsoft Disability Answer Desk for technical assistance. The Disability Answer Desk support team is trained in using many popular assistive technologies and can offer assistance in English, Spanish, French, and American Sign Language. Please go to the Microsoft Disability Answer Desk site to find out the contact details for your region.
If you are a government, commercial, or enterprise user, please contact the enterprise Disability Answer Desk.
No comments:
Post a Comment