{"id":814,"date":"2014-07-21T23:02:13","date_gmt":"2014-07-21T23:02:13","guid":{"rendered":"http:\/\/elbsolutions.com\/projects\/?p=814"},"modified":"2022-02-03T11:25:01","modified_gmt":"2022-02-03T17:25:01","slug":"exporting-ms-access-excel-using-vba-within-excel-ms-access-db-table-stacker","status":"publish","type":"post","link":"https:\/\/elbsolutions.com\/projects\/exporting-ms-access-excel-using-vba-within-excel-ms-access-db-table-stacker\/","title":{"rendered":"Exporting MS Access to Excel using VBA from within Excel &#8211; MS Access dB table stacker"},"content":{"rendered":"<p>Recently, I made an excel spreadsheet stacker where, given a list of excel workbook paths, it would stack them all into one. Then I thought, this would be beyond great to stack Bentley Spec tables on top of one another so we can do a search and troubleshoot quicker.<\/p>\n<p>In short, open up the SpecTables table of each, export those tables that we find to Excel sheets, then use my stacker and stack them all on top of one another. Since the stacker removes any need to worry about column order &#8211; AND since it tells you what columns are &#8220;lost&#8221; we can run it many times until everything is under one umbrella.<\/p>\n<p>Here are the links that helped me export from MS Access to Excel sheets but using Excel as the engine which holds the VBA that makes this all work<\/p>\n<ul>\n<li>http:\/\/stackoverflow.com\/questions\/1849580\/export-ms-access-tables-through-vba-to-an-excel-spreadsheet-in-same-directory\n<ul>\n<li>here was the heart of the macro tip from this guy<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<pre class=\"brush: vb; title: ; notranslate\" title=\"\">\r\n\r\nDim outputFileName As String\r\noutputFileName = CurrentProject.Path &amp; &quot;\\Export_&quot; &amp; Format(Date, &quot;yyyyMMdd&quot;) &amp; &quot;.xls&quot;\r\nDoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, &quot;Table1&quot;, outputFileName , True\r\nDoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, &quot;Table2&quot;, outputFileName\r\n\r\n<\/pre>\n<ul>\n<li>http:\/\/www.mrexcel.com\/forum\/excel-questions\/643188-excel-run-access-docmd-transferspreadsheet-into-activeworkbook.html<\/li>\n<\/ul>\n<p>I had to add the MS Access reference (I am using both version 14 or MS Office 2010) and the reference for DAO 3.6 through the &#8220;Tools-Add Reference&#8221; menu in Excel&#8217;s VBA editor.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Recently, I made an excel spreadsheet stacker where, given a list of excel workbook paths, it would stack them all into one. Then I thought, this would be beyond great to stack Bentley Spec tables on top of one another so we can do a search and troubleshoot quicker. In short, open up the SpecTables [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-814","post","type-post","status-publish","format-standard","hentry","category-general"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.7 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Exporting MS Access to Excel using VBA from within Excel - MS Access dB table stacker - ELB Solutions.com Inc.<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/elbsolutions.com\/projects\/exporting-ms-access-excel-using-vba-within-excel-ms-access-db-table-stacker\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Exporting MS Access to Excel using VBA from within Excel - MS Access dB table stacker - ELB Solutions.com Inc.\" \/>\n<meta property=\"og:description\" content=\"Recently, I made an excel spreadsheet stacker where, given a list of excel workbook paths, it would stack them all into one. Then I thought, this would be beyond great to stack Bentley Spec tables on top of one another so we can do a search and troubleshoot quicker. In short, open up the SpecTables [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/elbsolutions.com\/projects\/exporting-ms-access-excel-using-vba-within-excel-ms-access-db-table-stacker\/\" \/>\n<meta property=\"og:site_name\" content=\"ELB Solutions.com Inc.\" \/>\n<meta property=\"article:published_time\" content=\"2014-07-21T23:02:13+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-02-03T17:25:01+00:00\" \/>\n<meta name=\"author\" content=\"Etienne Bley\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Etienne Bley\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/exporting-ms-access-excel-using-vba-within-excel-ms-access-db-table-stacker\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/exporting-ms-access-excel-using-vba-within-excel-ms-access-db-table-stacker\\\/\"},\"author\":{\"name\":\"Etienne Bley\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#\\\/schema\\\/person\\\/51e717c68f4f5917c63baf88f0896c39\"},\"headline\":\"Exporting MS Access to Excel using VBA from within Excel &#8211; MS Access dB table stacker\",\"datePublished\":\"2014-07-21T23:02:13+00:00\",\"dateModified\":\"2022-02-03T17:25:01+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/exporting-ms-access-excel-using-vba-within-excel-ms-access-db-table-stacker\\\/\"},\"wordCount\":253,\"articleSection\":[\"General\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/exporting-ms-access-excel-using-vba-within-excel-ms-access-db-table-stacker\\\/\",\"url\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/exporting-ms-access-excel-using-vba-within-excel-ms-access-db-table-stacker\\\/\",\"name\":\"Exporting MS Access to Excel using VBA from within Excel - MS Access dB table stacker - ELB Solutions.com Inc.\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#website\"},\"datePublished\":\"2014-07-21T23:02:13+00:00\",\"dateModified\":\"2022-02-03T17:25:01+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#\\\/schema\\\/person\\\/51e717c68f4f5917c63baf88f0896c39\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/exporting-ms-access-excel-using-vba-within-excel-ms-access-db-table-stacker\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/exporting-ms-access-excel-using-vba-within-excel-ms-access-db-table-stacker\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/exporting-ms-access-excel-using-vba-within-excel-ms-access-db-table-stacker\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Exporting MS Access to Excel using VBA from within Excel &#8211; MS Access dB table stacker\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#website\",\"url\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/\",\"name\":\"ELB Solutions.com Inc.\",\"description\":\"Bringing all your IT Pieces together\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#\\\/schema\\\/person\\\/51e717c68f4f5917c63baf88f0896c39\",\"name\":\"Etienne Bley\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g\",\"caption\":\"Etienne Bley\"},\"url\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/author\\\/etienne-bley\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Exporting MS Access to Excel using VBA from within Excel - MS Access dB table stacker - ELB Solutions.com Inc.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/elbsolutions.com\/projects\/exporting-ms-access-excel-using-vba-within-excel-ms-access-db-table-stacker\/","og_locale":"en_US","og_type":"article","og_title":"Exporting MS Access to Excel using VBA from within Excel - MS Access dB table stacker - ELB Solutions.com Inc.","og_description":"Recently, I made an excel spreadsheet stacker where, given a list of excel workbook paths, it would stack them all into one. Then I thought, this would be beyond great to stack Bentley Spec tables on top of one another so we can do a search and troubleshoot quicker. In short, open up the SpecTables [&hellip;]","og_url":"https:\/\/elbsolutions.com\/projects\/exporting-ms-access-excel-using-vba-within-excel-ms-access-db-table-stacker\/","og_site_name":"ELB Solutions.com Inc.","article_published_time":"2014-07-21T23:02:13+00:00","article_modified_time":"2022-02-03T17:25:01+00:00","author":"Etienne Bley","twitter_misc":{"Written by":"Etienne Bley","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/elbsolutions.com\/projects\/exporting-ms-access-excel-using-vba-within-excel-ms-access-db-table-stacker\/#article","isPartOf":{"@id":"https:\/\/elbsolutions.com\/projects\/exporting-ms-access-excel-using-vba-within-excel-ms-access-db-table-stacker\/"},"author":{"name":"Etienne Bley","@id":"https:\/\/elbsolutions.com\/projects\/#\/schema\/person\/51e717c68f4f5917c63baf88f0896c39"},"headline":"Exporting MS Access to Excel using VBA from within Excel &#8211; MS Access dB table stacker","datePublished":"2014-07-21T23:02:13+00:00","dateModified":"2022-02-03T17:25:01+00:00","mainEntityOfPage":{"@id":"https:\/\/elbsolutions.com\/projects\/exporting-ms-access-excel-using-vba-within-excel-ms-access-db-table-stacker\/"},"wordCount":253,"articleSection":["General"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/elbsolutions.com\/projects\/exporting-ms-access-excel-using-vba-within-excel-ms-access-db-table-stacker\/","url":"https:\/\/elbsolutions.com\/projects\/exporting-ms-access-excel-using-vba-within-excel-ms-access-db-table-stacker\/","name":"Exporting MS Access to Excel using VBA from within Excel - MS Access dB table stacker - ELB Solutions.com Inc.","isPartOf":{"@id":"https:\/\/elbsolutions.com\/projects\/#website"},"datePublished":"2014-07-21T23:02:13+00:00","dateModified":"2022-02-03T17:25:01+00:00","author":{"@id":"https:\/\/elbsolutions.com\/projects\/#\/schema\/person\/51e717c68f4f5917c63baf88f0896c39"},"breadcrumb":{"@id":"https:\/\/elbsolutions.com\/projects\/exporting-ms-access-excel-using-vba-within-excel-ms-access-db-table-stacker\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/elbsolutions.com\/projects\/exporting-ms-access-excel-using-vba-within-excel-ms-access-db-table-stacker\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/elbsolutions.com\/projects\/exporting-ms-access-excel-using-vba-within-excel-ms-access-db-table-stacker\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/elbsolutions.com\/projects\/"},{"@type":"ListItem","position":2,"name":"Exporting MS Access to Excel using VBA from within Excel &#8211; MS Access dB table stacker"}]},{"@type":"WebSite","@id":"https:\/\/elbsolutions.com\/projects\/#website","url":"https:\/\/elbsolutions.com\/projects\/","name":"ELB Solutions.com Inc.","description":"Bringing all your IT Pieces together","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/elbsolutions.com\/projects\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/elbsolutions.com\/projects\/#\/schema\/person\/51e717c68f4f5917c63baf88f0896c39","name":"Etienne Bley","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g","caption":"Etienne Bley"},"url":"https:\/\/elbsolutions.com\/projects\/author\/etienne-bley\/"}]}},"_links":{"self":[{"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/posts\/814","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/comments?post=814"}],"version-history":[{"count":1,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/posts\/814\/revisions"}],"predecessor-version":[{"id":815,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/posts\/814\/revisions\/815"}],"wp:attachment":[{"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/media?parent=814"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/categories?post=814"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/tags?post=814"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}