{"id":384,"date":"2025-10-25T10:45:25","date_gmt":"2025-10-25T08:45:25","guid":{"rendered":"https:\/\/www.silverice.it\/IpercubeTechWP1\/?page_id=384"},"modified":"2025-10-25T11:26:09","modified_gmt":"2025-10-25T09:26:09","slug":"xlloadexceltable","status":"publish","type":"page","link":"https:\/\/www.silverice.it\/IpercubeTechWP1\/ipercube-utilities\/excel-functions\/xlloadexceltable\/","title":{"rendered":"Data Import\/Export &#8211; Load Excel Sheet to DataTable"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Loads an Excel worksheet into a\u00a0<em>DataTable\u00a0<\/em>using OLEDB.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Validates the provided\u00a0<strong>FilePath<\/strong>\u00a0and checks that the file exists.<\/li>\n\n\n\n<li>Builds a connection string for OLEDB access using the ACE 12.0 provider.<\/li>\n\n\n\n<li>If\u00a0<em>SheetName<strong>\u00a0<\/strong><\/em>is not provided, it calls\u00a0<strong>xlGetFirstSheetName<\/strong>\u00a0to determine the first available worksheet.<\/li>\n\n\n\n<li>Uses an\u00a0<strong>OleDbDataAdapter<\/strong>\u00a0to execute a\u00a0<strong>SELECT<\/strong>\u00a0query and fill a\u00a0<strong>DataSet<\/strong>.<\/li>\n\n\n\n<li>Returns the first table in the\u00a0<strong>DataSet<\/strong>, if available.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Requirements:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The system must have the Microsoft Access Database Engine (ACE OLEDB 12.0) installed.<\/li>\n\n\n\n<li>The Excel file must have headers in the first row (HDR=YES).<\/li>\n<\/ul>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-8f761849 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\" style=\"flex-basis:75%\">\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-8f761849 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column page-snippet is-layout-flow wp-block-column-is-layout-flow\" style=\"flex-basis:100%\">\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-Geist-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-Geist-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">VB<\/span><span role=\"button\" tabindex=\"0\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>    Public Function xlLoadExcelTable(FilePath As String, \n                                     Optional SheetName As String = \"\", \n                                     Optional FieldsToKeep As String = \"*\") As Data.DataTable\n\n        If String.IsNullOrWhiteSpace(FilePath) OrElse Not IO.File.Exists(FilePath) Then Return Nothing\n\n        Try\n            Dim ConnectionString As String = \"Provider=Microsoft.ACE.OLEDB.12.0;\" &amp;\n                                         \"Data Source=\"\"\" &amp; FilePath &amp; \"\"\";\" &amp;\n                                         \"Extended Properties=\"\"Excel 12.0 Xml;HDR=YES;IMEX=1\"\"\"\n\n            Dim wsName As String = If(Not String.IsNullOrWhiteSpace(SheetName), SheetName, xlGetFirstSheetName(FilePath))\n            If String.IsNullOrWhiteSpace(wsName) Then Return Nothing\n\n            Using MyConnection As New OleDb.OleDbConnection(ConnectionString)\n                MyConnection.Open()\n                Using MyDataAdapter As New OleDb.OleDbDataAdapter($\"SELECT {FieldsToKeep} FROM &#91;{wsName}$&#93;\", MyConnection)\n                    Dim DtSet As New DataSet()\n                    MyDataAdapter.Fill(DtSet)\n                    If DtSet.Tables.Count > 0 Then Return DtSet.Tables(0)\n                End Using\n            End Using\n\n        Catch ex As Exception\n            Debug.WriteLine($\"&#91;{NameOf(xlLoadExcelTable)}&#93; Error: {ex.Message}\")\n        End Try\n\n        Return Nothing\n    End Function<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">Public Function <\/span><span style=\"color: #DCDCAA\">xlLoadExcelTable<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">FilePath<\/span><span style=\"color: #D4D4D4\"> As <\/span><span style=\"color: #4EC9B0\">String<\/span><span style=\"color: #D4D4D4\">, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                     Optional SheetName As <\/span><span style=\"color: #4EC9B0\">String<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #CE9178\">&quot;&quot;<\/span><span style=\"color: #D4D4D4\">, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                     Optional FieldsToKeep As <\/span><span style=\"color: #4EC9B0\">String<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #CE9178\">&quot;*&quot;<\/span><span style=\"color: #D4D4D4\">) As <\/span><span style=\"color: #4EC9B0\">Data<\/span><span style=\"color: #D4D4D4\">.DataTable<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #C586C0\">If<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">String<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #DCDCAA\">IsNullOrWhiteSpace<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">FilePath<\/span><span style=\"color: #D4D4D4\">) OrElse Not IO.File.<\/span><span style=\"color: #DCDCAA\">Exists<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">FilePath<\/span><span style=\"color: #D4D4D4\">) <\/span><span style=\"color: #C586C0\">Then<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #C586C0\">Return<\/span><span style=\"color: #569CD6\"> Nothing<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        Try<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #569CD6\">Dim<\/span><span style=\"color: #9CDCFE\"> ConnectionString <\/span><span style=\"color: #D4D4D4\">As <\/span><span style=\"color: #4EC9B0\">String<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #CE9178\">&quot;Provider=Microsoft.ACE.OLEDB.12.0;&quot;<\/span><span style=\"color: #D4D4D4\"> &amp;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                         <\/span><span style=\"color: #CE9178\">&quot;Data Source=&quot;&quot;&quot;<\/span><span style=\"color: #D4D4D4\"> &amp;<\/span><span style=\"color: #9CDCFE\"> FilePath<\/span><span style=\"color: #D4D4D4\"> &amp; <\/span><span style=\"color: #CE9178\">&quot;&quot;&quot;;&quot;<\/span><span style=\"color: #D4D4D4\"> &amp;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                         <\/span><span style=\"color: #CE9178\">&quot;Extended Properties=&quot;&quot;Excel 12.0 Xml;HDR=YES;IMEX=1&quot;&quot;&quot;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #569CD6\">Dim<\/span><span style=\"color: #9CDCFE\"> wsName <\/span><span style=\"color: #D4D4D4\">As <\/span><span style=\"color: #4EC9B0\">String<\/span><span style=\"color: #D4D4D4\"> = <\/span><span style=\"color: #C586C0\">If<\/span><span style=\"color: #D4D4D4\">(Not <\/span><span style=\"color: #DCDCAA\">String<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #DCDCAA\">IsNullOrWhiteSpace<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">SheetName<\/span><span style=\"color: #D4D4D4\">),<\/span><span style=\"color: #9CDCFE\"> SheetName<\/span><span style=\"color: #D4D4D4\">, <\/span><span style=\"color: #DCDCAA\">xlGetFirstSheetName<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">FilePath<\/span><span style=\"color: #D4D4D4\">))<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #C586C0\">If<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #DCDCAA\">String<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #DCDCAA\">IsNullOrWhiteSpace<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">wsName<\/span><span style=\"color: #D4D4D4\">) <\/span><span style=\"color: #C586C0\">Then<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #C586C0\">Return<\/span><span style=\"color: #569CD6\"> Nothing<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            Using MyConnection As<\/span><span style=\"color: #569CD6\"> New <\/span><span style=\"color: #D4D4D4\">OleDb.<\/span><span style=\"color: #DCDCAA\">OleDbConnection<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">ConnectionString<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                MyConnection.<\/span><span style=\"color: #DCDCAA\">Open<\/span><span style=\"color: #D4D4D4\">()<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                Using MyDataAdapter As<\/span><span style=\"color: #569CD6\"> New <\/span><span style=\"color: #D4D4D4\">OleDb.<\/span><span style=\"color: #DCDCAA\">OleDbDataAdapter<\/span><span style=\"color: #D4D4D4\">($<\/span><span style=\"color: #CE9178\">&quot;SELECT {FieldsToKeep} FROM &#91;{wsName}$&#93;&quot;<\/span><span style=\"color: #D4D4D4\">,<\/span><span style=\"color: #9CDCFE\"> MyConnection<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                    <\/span><span style=\"color: #569CD6\">Dim<\/span><span style=\"color: #9CDCFE\"> DtSet <\/span><span style=\"color: #D4D4D4\">As<\/span><span style=\"color: #569CD6\"> New <\/span><span style=\"color: #DCDCAA\">DataSet<\/span><span style=\"color: #D4D4D4\">()<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                    MyDataAdapter.<\/span><span style=\"color: #DCDCAA\">Fill<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">DtSet<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                    <\/span><span style=\"color: #C586C0\">If<\/span><span style=\"color: #D4D4D4\"> DtSet.Tables.<\/span><span style=\"color: #9CDCFE\">Count<\/span><span style=\"color: #D4D4D4\"> &gt; <\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #C586C0\">Then<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #C586C0\">Return<\/span><span style=\"color: #D4D4D4\"> DtSet.<\/span><span style=\"color: #DCDCAA\">Tables<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #B5CEA8\">0<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                <\/span><span style=\"color: #DCDCAA\">End<\/span><span style=\"color: #D4D4D4\"> Using<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #DCDCAA\">End<\/span><span style=\"color: #D4D4D4\"> Using<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        Catch ex As <\/span><span style=\"color: #4EC9B0\">Exception<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            Debug.<\/span><span style=\"color: #DCDCAA\">WriteLine<\/span><span style=\"color: #D4D4D4\">($<\/span><span style=\"color: #CE9178\">&quot;&#91;{NameOf(xlLoadExcelTable)}&#93; Error: {ex.Message}&quot;<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #DCDCAA\">End<\/span><span style=\"color: #D4D4D4\"> Try<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #C586C0\">Return<\/span><span style=\"color: #569CD6\"> Nothing<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">End Function<\/span><\/span><\/code><\/pre><\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Loads an Excel worksheet into a\u00a0DataTable\u00a0using OLEDB. Requirements:<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":349,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-384","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages\/384","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/comments?post=384"}],"version-history":[{"count":3,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages\/384\/revisions"}],"predecessor-version":[{"id":432,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages\/384\/revisions\/432"}],"up":[{"embeddable":true,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages\/349"}],"wp:attachment":[{"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/media?parent=384"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}