{"id":4004,"date":"2020-01-27T19:46:39","date_gmt":"2020-01-27T19:46:39","guid":{"rendered":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs\/?post_type=chapter&#038;p=4004"},"modified":"2024-05-17T02:04:20","modified_gmt":"2024-05-17T02:04:20","slug":"excel-table-from-access-data","status":"publish","type":"chapter","link":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/chapter\/excel-table-from-access-data\/","title":{"raw":"Excel Table from Access Data","rendered":"Excel Table from Access Data"},"content":{"raw":"<div class=\"textbox learning-objectives\">\r\n<h3>Learning Outcomes<\/h3>\r\n<ul>\r\n \t<li>Create Excel table from Access Data<\/li>\r\n<\/ul>\r\n<\/div>\r\nThere are times when Access contains data you want to analyze in an Excel spreadsheet. This export may take place once or more frequently depending on the situation. For example, let\u2019s say at work you have a monthly report for your department head which pulls monthly customer contact data from Access into an Excel table. Although Access can create reports, your department manager is more comfortable with Excel and wants the data reported in that program. We\u2019ll walk through setting this scenario up and show you the integration capabilities of Access.\r\n<h2>Exporting Data to Excel<\/h2>\r\nOpen the Access file to be exported and follow these steps to export the customer contact information into Excel.\r\n<h3>Step 1<\/h3>\r\nBefore exporting data from Access look through the data to identify any errors (e.g. #Error, #Num!, #Name?, etc.). Fix any errors\u2014otherwise errors might multiply as incorrect information may be placed in incorrect Excel cells in a worksheet. Now that the data is ready go to the next step.\r\n<h3>Step 2<\/h3>\r\nSelect the <strong>External Data<\/strong> tab, <strong>Excel<\/strong> button and an <strong>Export \u2013 Excel Spreadsheet<\/strong> command window opens.\r\n\r\n<img class=\"alignnone wp-image-4023 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27193723\/Module_15_Access_ExportExcel_1.png\" alt=\"Access screenshot of customer database table and the External Data, Excel button highlighted.\" width=\"1055\" height=\"657\" \/>\r\n<h3>Step 3<\/h3>\r\nFill out the information asked for in the window:\r\n<ol style=\"list-style-type: lower-alpha;\">\r\n \t<li style=\"font-weight: 400;\"><strong>File name:<\/strong> name for the exported excel file.<\/li>\r\n \t<li style=\"font-weight: 400;\"><strong>File format:<\/strong> older Excel versions are available as well as the current *.xlsx extension default.<\/li>\r\n \t<li style=\"font-weight: 400;\"><strong>Specify export options:<\/strong> Decide whether to export the data format and layout or not.\r\n<ol style=\"list-style-type: lower-roman;\">\r\n \t<li style=\"font-weight: 400;\">If exporting the format, the next box\/choice becomes available whether to open the destination file after the export is complete or not.<\/li>\r\n<\/ol>\r\n<\/li>\r\n \t<li style=\"font-weight: 400;\">Click the <strong>OK<\/strong> button.<\/li>\r\n<\/ol>\r\n<img class=\"alignnone wp-image-4024 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27193808\/Module_15_Access_ExportExcel_1.1.png\" alt=\"Access screenshot of export to excel spreadsheet wizard window open.\" width=\"743\" height=\"545\" \/>\r\n<div class=\"textbox exercises\">\r\n<h3>Note: To Format or Not To Format?<\/h3>\r\nHow should you determine whether to export data with or without formatting?\r\n<ul>\r\n \t<li><strong>With Formatting:<\/strong> Exporting a table or query with formatting allows the export wizard to use the format property settings, have hyperlink fields exported as hyperlinks and have it use the format settings. With rich text fields text is exported without formatting.<\/li>\r\n \t<li><strong>Without Formatting:<\/strong> Exporting a table or query without format means all records and fields are exported with formatting ignored. Only lookup ID values are exported, and hyperlinks are exported as regular text.<\/li>\r\n<\/ul>\r\n<\/div>\r\n<h3>Step 4<\/h3>\r\nOne more window opens indicating the export is successful and asks if these same export steps should be saved. This option is used if you are frequently exporting this same data often. In this case we leave the box unchecked.\r\n\r\n<img class=\"alignnone wp-image-4025 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27194337\/Module_15_Access_ExportExcel_1.2.png\" alt=\"Access screenshot of export to excel spreadsheet wizard window open to select save export steps.\" width=\"747\" height=\"579\" \/>\r\n<h3>Step 5<\/h3>\r\nChoose the Excel workbook and file format. If you selected the open the file when done exporting an Excel worksheet will open with the exported Access data. Now you can use the data to do anything in Excel you wish to do.\r\n\r\n<img class=\"alignnone wp-image-4026 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27194357\/Module_15_Access_ExportExcel_1.3.png\" alt=\"Excel spreadsheet screenshot of exported table from Access.\" width=\"1076\" height=\"721\" \/>\r\n<div class=\"textbox tryit\">\r\n<h3>PRactice Questions<\/h3>\r\nhttps:\/\/assess.lumenlearning.com\/practice\/6283c1a9-7287-4ef7-ae1b-2c55b56e872e\r\n\r\nhttps:\/\/assess.lumenlearning.com\/practice\/dbb9d45f-c70a-4794-abc3-6b28d0f07b68\r\n\r\n<\/div>\r\nThis example was using contact information data in Access, but all kinds of data can be exported from Access to Excel. If an error slipped through, you can either fix it in Excel or return to Access and fix the error there and re-export the data into an Excel worksheet.\r\n<div class=\"textbox exercises\">\r\n<h3>Note: Export Possibilities<\/h3>\r\nWhat is and isn\u2019t possible to export to Excel:\r\n<ul>\r\n \t<li style=\"font-weight: 400;\">There are a few types that can be exported to Excel; forms, tables, reports, queries or even a few selected records from a datasheet.<\/li>\r\n \t<li style=\"font-weight: 400;\">Things that cannot be exported to Excel are modules or macros and if there are subreports, subdatasheet or subforum in the data they are not exported and must repeat the export for each of them if you want them exported to Excel.<\/li>\r\n \t<li style=\"font-weight: 400;\">Only one database object can be exported in a single export, but multiple worksheets can be merged in Excel to cobble together one table or report from the individual exports.<\/li>\r\n<\/ul>\r\n<\/div>\r\nNow that you have walked through exporting a table from Access to Excel, watch this short video to see additional ways to export tables, queries and reports from Access to Excel.\r\n\r\n<iframe src=\"\/\/plugin.3playmedia.com\/show?mf=4369415&amp;p3sdk_version=1.10.1&amp;p=20361&amp;pt=375&amp;video_id=Mn4zwUMi7yc&amp;video_target=tpm-plugin-5zljd817-Mn4zwUMi7yc\" width=\"800px\" height=\"450px\" frameborder=\"0\" marginwidth=\"0px\" marginheight=\"0px\"><\/iframe>\r\n\r\nYou can also view a transcript for the video <a href=\"https:\/\/course-building.s3-us-west-2.amazonaws.com\/ComputerApps\/Transcripts\/ExportingTablesQueriesandReports_Transcript.txt\" target=\"_blank\" rel=\"noopener\">\u201cExporting Tables, Queries and Reports\u201d here (opens in new window<\/a>).","rendered":"<div class=\"textbox learning-objectives\">\n<h3>Learning Outcomes<\/h3>\n<ul>\n<li>Create Excel table from Access Data<\/li>\n<\/ul>\n<\/div>\n<p>There are times when Access contains data you want to analyze in an Excel spreadsheet. This export may take place once or more frequently depending on the situation. For example, let\u2019s say at work you have a monthly report for your department head which pulls monthly customer contact data from Access into an Excel table. Although Access can create reports, your department manager is more comfortable with Excel and wants the data reported in that program. We\u2019ll walk through setting this scenario up and show you the integration capabilities of Access.<\/p>\n<h2>Exporting Data to Excel<\/h2>\n<p>Open the Access file to be exported and follow these steps to export the customer contact information into Excel.<\/p>\n<h3>Step 1<\/h3>\n<p>Before exporting data from Access look through the data to identify any errors (e.g. #Error, #Num!, #Name?, etc.). Fix any errors\u2014otherwise errors might multiply as incorrect information may be placed in incorrect Excel cells in a worksheet. Now that the data is ready go to the next step.<\/p>\n<h3>Step 2<\/h3>\n<p>Select the <strong>External Data<\/strong> tab, <strong>Excel<\/strong> button and an <strong>Export \u2013 Excel Spreadsheet<\/strong> command window opens.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-4023 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27193723\/Module_15_Access_ExportExcel_1.png\" alt=\"Access screenshot of customer database table and the External Data, Excel button highlighted.\" width=\"1055\" height=\"657\" \/><\/p>\n<h3>Step 3<\/h3>\n<p>Fill out the information asked for in the window:<\/p>\n<ol style=\"list-style-type: lower-alpha;\">\n<li style=\"font-weight: 400;\"><strong>File name:<\/strong> name for the exported excel file.<\/li>\n<li style=\"font-weight: 400;\"><strong>File format:<\/strong> older Excel versions are available as well as the current *.xlsx extension default.<\/li>\n<li style=\"font-weight: 400;\"><strong>Specify export options:<\/strong> Decide whether to export the data format and layout or not.\n<ol style=\"list-style-type: lower-roman;\">\n<li style=\"font-weight: 400;\">If exporting the format, the next box\/choice becomes available whether to open the destination file after the export is complete or not.<\/li>\n<\/ol>\n<\/li>\n<li style=\"font-weight: 400;\">Click the <strong>OK<\/strong> button.<\/li>\n<\/ol>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-4024 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27193808\/Module_15_Access_ExportExcel_1.1.png\" alt=\"Access screenshot of export to excel spreadsheet wizard window open.\" width=\"743\" height=\"545\" \/><\/p>\n<div class=\"textbox exercises\">\n<h3>Note: To Format or Not To Format?<\/h3>\n<p>How should you determine whether to export data with or without formatting?<\/p>\n<ul>\n<li><strong>With Formatting:<\/strong> Exporting a table or query with formatting allows the export wizard to use the format property settings, have hyperlink fields exported as hyperlinks and have it use the format settings. With rich text fields text is exported without formatting.<\/li>\n<li><strong>Without Formatting:<\/strong> Exporting a table or query without format means all records and fields are exported with formatting ignored. Only lookup ID values are exported, and hyperlinks are exported as regular text.<\/li>\n<\/ul>\n<\/div>\n<h3>Step 4<\/h3>\n<p>One more window opens indicating the export is successful and asks if these same export steps should be saved. This option is used if you are frequently exporting this same data often. In this case we leave the box unchecked.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-4025 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27194337\/Module_15_Access_ExportExcel_1.2.png\" alt=\"Access screenshot of export to excel spreadsheet wizard window open to select save export steps.\" width=\"747\" height=\"579\" \/><\/p>\n<h3>Step 5<\/h3>\n<p>Choose the Excel workbook and file format. If you selected the open the file when done exporting an Excel worksheet will open with the exported Access data. Now you can use the data to do anything in Excel you wish to do.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-4026 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2020\/01\/27194357\/Module_15_Access_ExportExcel_1.3.png\" alt=\"Excel spreadsheet screenshot of exported table from Access.\" width=\"1076\" height=\"721\" \/><\/p>\n<div class=\"textbox tryit\">\n<h3>PRactice Questions<\/h3>\n<p>\t<iframe id=\"assessment_practice_6283c1a9-7287-4ef7-ae1b-2c55b56e872e\" class=\"resizable\" src=\"https:\/\/assess.lumenlearning.com\/practice\/6283c1a9-7287-4ef7-ae1b-2c55b56e872e?iframe_resize_id=assessment_practice_id_6283c1a9-7287-4ef7-ae1b-2c55b56e872e\" frameborder=\"0\" style=\"border:none;width:100%;height:100%;min-height:300px;\"><br \/>\n\t<\/iframe><\/p>\n<p>\t<iframe id=\"assessment_practice_dbb9d45f-c70a-4794-abc3-6b28d0f07b68\" class=\"resizable\" src=\"https:\/\/assess.lumenlearning.com\/practice\/dbb9d45f-c70a-4794-abc3-6b28d0f07b68?iframe_resize_id=assessment_practice_id_dbb9d45f-c70a-4794-abc3-6b28d0f07b68\" frameborder=\"0\" style=\"border:none;width:100%;height:100%;min-height:300px;\"><br \/>\n\t<\/iframe><\/p>\n<\/div>\n<p>This example was using contact information data in Access, but all kinds of data can be exported from Access to Excel. If an error slipped through, you can either fix it in Excel or return to Access and fix the error there and re-export the data into an Excel worksheet.<\/p>\n<div class=\"textbox exercises\">\n<h3>Note: Export Possibilities<\/h3>\n<p>What is and isn\u2019t possible to export to Excel:<\/p>\n<ul>\n<li style=\"font-weight: 400;\">There are a few types that can be exported to Excel; forms, tables, reports, queries or even a few selected records from a datasheet.<\/li>\n<li style=\"font-weight: 400;\">Things that cannot be exported to Excel are modules or macros and if there are subreports, subdatasheet or subforum in the data they are not exported and must repeat the export for each of them if you want them exported to Excel.<\/li>\n<li style=\"font-weight: 400;\">Only one database object can be exported in a single export, but multiple worksheets can be merged in Excel to cobble together one table or report from the individual exports.<\/li>\n<\/ul>\n<\/div>\n<p>Now that you have walked through exporting a table from Access to Excel, watch this short video to see additional ways to export tables, queries and reports from Access to Excel.<\/p>\n<p><iframe loading=\"lazy\" src=\"\/\/plugin.3playmedia.com\/show?mf=4369415&amp;p3sdk_version=1.10.1&amp;p=20361&amp;pt=375&amp;video_id=Mn4zwUMi7yc&amp;video_target=tpm-plugin-5zljd817-Mn4zwUMi7yc\" width=\"800px\" height=\"450px\" frameborder=\"0\" marginwidth=\"0px\" marginheight=\"0px\"><\/iframe><\/p>\n<p>You can also view a transcript for the video <a href=\"https:\/\/course-building.s3-us-west-2.amazonaws.com\/ComputerApps\/Transcripts\/ExportingTablesQueriesandReports_Transcript.txt\" target=\"_blank\" rel=\"noopener\">\u201cExporting Tables, Queries and Reports\u201d here (opens in new window<\/a>).<\/p>\n\n\t\t\t <section class=\"citations-section\" role=\"contentinfo\">\n\t\t\t <h3>Candela Citations<\/h3>\n\t\t\t\t\t <div>\n\t\t\t\t\t\t <div id=\"citation-list-4004\">\n\t\t\t\t\t\t\t <div class=\"licensing\"><div class=\"license-attribution-dropdown-subheading\">CC licensed content, Original<\/div><ul class=\"citation-list\"><li>Excel Table from Access Data. <strong>Authored by<\/strong>: Sherri Pendleton. <strong>Provided by<\/strong>: Lumen Learning. <strong>License<\/strong>: <em><a target=\"_blank\" rel=\"license\" href=\"https:\/\/creativecommons.org\/licenses\/by\/4.0\/\">CC BY: Attribution<\/a><\/em><\/li><\/ul><div class=\"license-attribution-dropdown-subheading\">All rights reserved content<\/div><ul class=\"citation-list\"><li>Exporting Tables, Queries and Reports from Access to Excel in Access 2016. <strong>Authored by<\/strong>: Sali Kaceli. <strong>Located at<\/strong>: <a target=\"_blank\" href=\"https:\/\/www.youtube.com\/watch?v=Mn4zwUMi7yc\">https:\/\/www.youtube.com\/watch?v=Mn4zwUMi7yc<\/a>. <strong>License<\/strong>: <em>All Rights Reserved<\/em>. <strong>License Terms<\/strong>: Standard YouTube License<\/li><\/ul><\/div>\n\t\t\t\t\t\t <\/div>\n\t\t\t\t\t <\/div>\n\t\t\t <\/section>","protected":false},"author":17,"menu_order":12,"template":"","meta":{"_candela_citation":"[{\"type\":\"original\",\"description\":\"Excel Table from Access Data\",\"author\":\"Sherri Pendleton\",\"organization\":\"Lumen Learning\",\"url\":\"\",\"project\":\"\",\"license\":\"cc-by\",\"license_terms\":\"\"},{\"type\":\"copyrighted_video\",\"description\":\"Exporting Tables, Queries and Reports from Access to Excel in Access 2016\",\"author\":\"Sali Kaceli\",\"organization\":\"\",\"url\":\"https:\/\/www.youtube.com\/watch?v=Mn4zwUMi7yc\",\"project\":\"\",\"license\":\"arr\",\"license_terms\":\"Standard YouTube License\"}]","CANDELA_OUTCOMES_GUID":"54ae2d4a-9b29-4bed-b6c4-9618d0d6493d, 8b07c1c3-e9e4-46fd-85e5-dd3b6ce445f5","pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":[],"pb_section_license":""},"chapter-type":[],"contributor":[],"license":[],"class_list":["post-4004","chapter","type-chapter","status-publish","hentry"],"part":1965,"_links":{"self":[{"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/wp-json\/pressbooks\/v2\/chapters\/4004","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/wp-json\/wp\/v2\/users\/17"}],"version-history":[{"count":7,"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/wp-json\/pressbooks\/v2\/chapters\/4004\/revisions"}],"predecessor-version":[{"id":6007,"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/wp-json\/pressbooks\/v2\/chapters\/4004\/revisions\/6007"}],"part":[{"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/wp-json\/pressbooks\/v2\/parts\/1965"}],"metadata":[{"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/wp-json\/pressbooks\/v2\/chapters\/4004\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/wp-json\/wp\/v2\/media?parent=4004"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/wp-json\/pressbooks\/v2\/chapter-type?post=4004"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/wp-json\/wp\/v2\/contributor?post=4004"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/wm-computerapplicationsmgrs-2\/wp-json\/wp\/v2\/license?post=4004"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}