{"id":353,"date":"2023-12-22T08:24:16","date_gmt":"2023-12-22T08:24:16","guid":{"rendered":"https:\/\/keithratner.live\/optionexplicit\/?page_id=353"},"modified":"2023-12-27T07:18:52","modified_gmt":"2023-12-27T07:18:52","slug":"code","status":"publish","type":"page","link":"https:\/\/keithratner.live\/optionexplicit\/code\/","title":{"rendered":"Code"},"content":{"rendered":"\n<div class=\"wp-block-query alignfull is-layout-flow wp-block-query-is-layout-flow\"><ul class=\"wp-block-post-template is-layout-flow wp-block-post-template-is-layout-flow\"><li class=\"wp-block-post post-965 post type-post status-publish format-standard has-post-thumbnail hentry category-articles category-code category-snippets category-vba\">\n<h2 class=\"has-link-color wp-elements-cd71dd8ec89fc068cec02e1d47b4491f wp-block-post-title\"><a href=\"https:\/\/keithratner.live\/optionexplicit\/export-vba-modules-for-version-control\/\" target=\"_self\" >Export VBA Modules for Version Control updated September 2024<\/a><\/h2>\n\n<div class=\"entry-content wp-block-post-content is-layout-flow wp-block-post-content-is-layout-flow\"><p>Add a Module in your VBA Project. Name it &#8220;Exports&#8221; and paste the following code into the module in its entirety. When your project is ready for export (and subsequent version control), click on the &#8220;Macros&#8221; button in the Developer ribbon, select &#8220;ExportVBAModules&#8221; and click &#8220;Run.&#8221;<br \/>\nOption Explicit<\/p>\n<p>&#8216; Define a constant for the export location<br \/>\nConst EXPORT_PATH As String = &#8220;F:\\VBAProjects\\&#8221;<\/p>\n<p>Sub ExportVBAModules()<br \/>\n    Dim VBProj As VBIDE.VBProject<br \/>\n    Dim VBComp As VBIDE.VBComponent<br \/>\n    Dim objFSO As Object<br \/>\n    Dim objFile As Object<br \/>\n    Dim strPath As String<br \/>\n    Dim strFile As String<br \/>\n    Dim LineNum As Long<br \/>\n    Dim modCode As String<br \/>\n    Dim wbName As String<\/p>\n<p>    &#8216; Get the name of the workbook without extension<br \/>\n    wbName = Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, &#8220;.&#8221;) &#8211; 1)<\/p>\n<p>    &#8216; Create a new folder in the specified location with workbook name and timestamp<br \/>\n    strPath = EXPORT_PATH &#038; wbName &#038; &#8220;_VBAExport_&#8221; &#038; Format(Now, &#8220;yyyymmdd_hhmmss&#8221;)<\/p>\n<p>    &#8216; Check if the EXPORT_PATH exists, if not, create it<br \/>\n    Set objFSO = CreateObject(&#8220;Scripting.FileSystemObject&#8221;)<br \/>\n    If Not objFSO.FolderExists(EXPORT_PATH) Then<br \/>\n        objFSO.CreateFolder EXPORT_PATH<br \/>\n    End If<\/p>\n<p>    &#8216; Create the export folder<br \/>\n    MkDir strPath<\/p>\n<p>    &#8216; Reference Microsoft Visual Basic for Applications Extensibility 5.3 library<br \/>\n    Set VBProj = ThisWorkbook.VBProject<\/p>\n<p>    For Each VBComp In VBProj.VBComponents<br \/>\n        Select Case VBComp.Type<br \/>\n            Case vbext_ct_ClassModule<br \/>\n                strFile = strPath &#038; &#8220;\\&#8221; &#038; VBComp.Name &#038; &#8220;.cls&#8221;<br \/>\n            Case vbext_ct_MSForm<br \/>\n                strFile = strPath &#038; &#8220;\\&#8221; &#038; VBComp.Name &#038; &#8220;.frm&#8221;<br \/>\n            Case vbext_ct_StdModule<br \/>\n                strFile = strPath &#038; &#8220;\\&#8221; &#038; VBComp.Name &#038; &#8220;.bas&#8221;<br \/>\n            Case vbext_ct_Document<br \/>\n                &#8216; This is a worksheet or workbook object.<br \/>\n                &#8216; Don&#8217;t export these unless you want to.<br \/>\n                strFile = strPath &#038; &#8220;\\&#8221; &#038; VBComp.Name &#038; &#8220;.cls&#8221;<br \/>\n            Case Else<br \/>\n                strFile = strPath &#038; &#8220;\\&#8221; &#038; VBComp.Name &#038; &#8220;.txt&#8221;<br \/>\n        End Select<\/p>\n<p>        If VBComp.CodeModule.CountOfLines > 0 Then<br \/>\n            modCode = VBComp.CodeModule.Lines(1, VBComp.CodeModule.CountOfLines)<br \/>\n            Set objFile = objFSO.CreateTextFile(strFile)<br \/>\n            objFile.Write modCode<br \/>\n            objFile.Close<br \/>\n        End If<br \/>\n    Next VBComp<\/p>\n<p>    MsgBox &#8220;Modules exported to &#8221; &#038; strPath<br \/>\nEnd Sub<\/p>\n<\/div>\n<\/li><li class=\"wp-block-post post-563 post type-post status-publish format-standard hentry category-articles category-code category-distribution category-snippets category-vba\">\n<h2 class=\"has-link-color wp-elements-cd71dd8ec89fc068cec02e1d47b4491f wp-block-post-title\"><a href=\"https:\/\/keithratner.live\/optionexplicit\/update-access-vba-saved-imports-exports\/\" target=\"_self\" >Update Access VBA Saved Imports Exports: A Step-by-Step Guide<\/a><\/h2>\n\n<div class=\"entry-content wp-block-post-content is-layout-flow wp-block-post-content-is-layout-flow\">\n<figure class=\"wp-block-image size-full\"><img data-attachment-id=\"567\" data-permalink=\"https:\/\/keithratner.live\/optionexplicit\/update-access-vba-saved-imports-exports\/screenshot-2024-05-03-133800\/\" data-orig-file=\"https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2024\/05\/Screenshot-2024-05-03-133800.png?fit=512%2C448&amp;ssl=1\" data-orig-size=\"512,448\" data-comments-opened=\"1\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"Screenshot-2024-05-03-133800\" data-image-description=\"\" data-image-caption=\"\" data-medium-file=\"https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2024\/05\/Screenshot-2024-05-03-133800.png?fit=300%2C263&amp;ssl=1\" data-large-file=\"https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2024\/05\/Screenshot-2024-05-03-133800.png?fit=512%2C448&amp;ssl=1\" data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" width=\"512\" height=\"448\" data-attachment-id=\"567\" data-permalink=\"https:\/\/keithratner.live\/optionexplicit\/update-access-vba-saved-imports-exports\/screenshot-2024-05-03-133800\/\" data-orig-file=\"https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2024\/05\/Screenshot-2024-05-03-133800.png?fit=512%2C448&amp;ssl=1\" data-orig-size=\"512,448\" data-comments-opened=\"1\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"Screenshot-2024-05-03-133800\" data-image-description=\"\" data-image-caption=\"\" data-medium-file=\"https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2024\/05\/Screenshot-2024-05-03-133800.png?fit=300%2C263&amp;ssl=1\" data-large-file=\"https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2024\/05\/Screenshot-2024-05-03-133800.png?fit=512%2C448&amp;ssl=1\" src=\"https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2024\/05\/Screenshot-2024-05-03-133800.png?resize=512%2C448&#038;ssl=1\" alt=\"Updating Access VBA saved imports exports\" class=\"wp-image-567\" srcset=\"https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2024\/05\/Screenshot-2024-05-03-133800.png?w=512&amp;ssl=1 512w, https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2024\/05\/Screenshot-2024-05-03-133800.png?resize=300%2C263&amp;ssl=1 300w, https:\/\/i0.wp.com\/keithratner.live\/optionexplicit\/wp-content\/uploads\/sites\/29\/2024\/05\/Screenshot-2024-05-03-133800.png?resize=133%2C116&amp;ssl=1 133w\" sizes=\"auto, (max-width: 512px) 100vw, 512px\" \/><\/figure>\n\n\n\n<p>Updating Access VBA saved imports exports is essential when dealing with external data sources. This step-by-step guide will show you how to update Access VBA saved imports exports by dynamically changing the file path within your import\/export specifications.<\/p>\n\n\n\n<p>In this article, we&#8217;ll walk through a powerful VBA script that allows you to update Access VBA saved imports exports easily. This technique is also applicable to <a target=\"_blank\" href=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/excel\" rel=\"noreferrer noopener\">Microsoft Excel<\/a>, making it a versatile solution for managing external data sources across <a target=\"_blank\" href=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\" rel=\"noreferrer noopener\">Microsoft Office<\/a> applications.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Prerequisites<\/h2>\n\n\n\n<p>Before using the script, ensure you have the necessary references set up in your VBA project:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Open the Visual Basic Editor (VBE) in Access (Alt+F11 or Database Tools tab > Visual Basic).<\/li>\n\n\n\n<li>Go to Tools > References.<\/li>\n\n\n\n<li>Check &#8220;Microsoft XML, v6.0&#8221; in the References dialog box.<\/li>\n\n\n\n<li>Click OK to close the dialog box.<\/li>\n<\/ol>\n\n\n\n<h2 class=\"wp-block-heading\">Why Update Access VBA Saved Imports Exports?<\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Flexibility<\/strong>: Easily update file paths when external data source locations change.<\/li>\n\n\n\n<li><strong>Time-saving<\/strong>: Avoid recreating import export specifications from scratch.<\/li>\n\n\n\n<li><strong>Automation<\/strong>:Incorporate the script into your VBA modules for automated updates.<\/li>\n\n\n\n<li><strong>Compatibility<\/strong>: Works seamlessly with both Access and Excel.<\/li>\n\n\n\n<li><strong>Efficiency<\/strong>: Edit XML specifications directly, bypassing Access UI limitations.<\/li>\n<\/ol>\n\n\n\n<ol class=\"wp-block-list\"><\/ol>\n\n\n\n<h2 class=\"wp-block-heading\">The VBA Script to Update Access VBA Saved Imports Exports<\/h2>\n\n\n\n<p>Here&#8217;s the <code>UpdateImportSpecPathXML<\/code> script to update Access VBA saved imports exports:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Public Sub UpdateImportSpecPathXML(ByVal strSpecName As String, ByVal strNewPath As String)<br>    <br>    Dim xmlString As String<br>    Dim xmlDoc As Object<br>    Dim xmlNode As Object<br>    Dim newPath As String<br>    Dim projectPath As String<br>    <br>    ' Get the project path<br>    'projectPath = CurrentProject.Path<br>    <br>    ' Define the new path<br>    'newPath = projectPath &amp; \"\\NWEDAILY.txt\"<br>    <br>    ' Get the XML string representation of the import\/export specification<br>    xmlString = CurrentProject.ImportExportSpecifications(strSpecName).XML<br>    <br>    ' Create a new XML document object<br>    Set xmlDoc = CreateObject(\"MSXML2.DOMDocument\")<br>    <br>    ' Load the XML string<br>    xmlDoc.LoadXML xmlString<br>    <br>    ' Set the namespace for the document<br>    xmlDoc.SetProperty \"SelectionNamespaces\", \"xmlns:ns='urn:www.microsoft.com\/office\/access\/imexspec'\"<br>    <br>    ' Select the ImportExportSpecification node<br>    Set xmlNode = xmlDoc.SelectSingleNode(\"\/\/ns:ImportExportSpecification\")<br>    <br>    ' Update the Path attribute<br>    xmlNode.Attributes.getNamedItem(\"Path\").Text = strNewPath<br>    <br>    ' Save the updated XML back to the specification<br>    CurrentProject.ImportExportSpecifications(strSpecName).XML = xmlDoc.XML<br>    <br>    'MsgBox \"Path attribute updated successfully.\"<br>    <br>End Sub<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">How the Script Works<\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Retrieves the XML string of the specified import\/export specification.<\/li>\n\n\n\n<li>Creates a new XML document object and loads the XML string.<\/li>\n\n\n\n<li>Sets the namespace for proper XML manipulation.<\/li>\n\n\n\n<li>Selects the <code>ImportExportSpecification<\/code> node.<\/li>\n\n\n\n<li>Updates the <code>Path<\/code> attribute with the new file path.<\/li>\n\n\n\n<li>Saves the updated XML back to the import\/export specification.<\/li>\n<\/ol>\n\n\n\n<p>To update your saved imports exports, simply call the script with the specification name and new file path.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step-by-Step Guide to Update Access VBA Saved Imports Exports<\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Open your Access database and press Alt+F11 to open the Visual Basic Editor.<\/li>\n\n\n\n<li>In the VBE, go to Tools > References and check &#8220;Microsoft XML, v6.0&#8221;.<\/li>\n\n\n\n<li>Create a new module and paste the <code>UpdateImportSpecPathXML<\/code> script. <\/li>\n\n\n\n<li>Call the script with the specification name and new file path to update your Access VBA saved imports exports.<\/li>\n<\/ol>\n\n\n\n<p>By following these steps, you can easily update Access VBA saved imports exports and streamline your data management tasks.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p>Updating Access VBA saved imports exports is a powerful technique for managing external data sources efficiently. By using the provided script and following the step-by-step guide, you can dynamically update file paths within your saved specifications, saving time and effort.<\/p>\n\n\n\n<p>Implement this solution in your Access projects and simplify your data management workflows. If you have any questions or suggestions, please leave a comment below. Happy coding!<\/p>\n<\/div>\n<\/li><li class=\"wp-block-post post-216 post type-post status-publish format-standard hentry category-code category-distribution category-snippets category-vba\">\n<h2 class=\"has-link-color wp-elements-cd71dd8ec89fc068cec02e1d47b4491f wp-block-post-title\"><a href=\"https:\/\/keithratner.live\/optionexplicit\/returnname\/\" target=\"_self\" >ReturnName<\/a><\/h2>\n\n<div class=\"entry-content wp-block-post-content is-layout-flow wp-block-post-content-is-layout-flow\"><pre>Function ReturnName(ByVal num As Integer) As String\n  ReturnName = Split(Cells(, num).Address, \"$\")(1)\nEnd Function<\/pre>\n<\/div>\n<\/li><li class=\"wp-block-post post-214 post type-post status-publish format-standard hentry category-code category-distribution category-snippets category-vba\">\n<h2 class=\"has-link-color wp-elements-cd71dd8ec89fc068cec02e1d47b4491f wp-block-post-title\"><a href=\"https:\/\/keithratner.live\/optionexplicit\/deleteinitialcolumnsfromworksheet\/\" target=\"_self\" >DeleteInitialColumnsFromWorksheet<\/a><\/h2>\n\n<div class=\"entry-content wp-block-post-content is-layout-flow wp-block-post-content-is-layout-flow\"><p>Note: This function depends on <a href=\"https:\/\/keithratner.live\/optionexplicit\/returnname\/\" rel=\"noopener noreferrer\" target=\"_blank\">ReturnName<\/a><\/p>\n<pre>Sub DeleteInitialColumnsFromWorksheet( _\r\n    ws As Worksheet, _\r\n    NumberOfColumns As Integer _\r\n)\r\n    With ws\r\n        .Columns( _\r\n            ReturnName( _\r\n                1 _\r\n            ) & _\r\n            \":\" & _\r\n            ReturnName( _\r\n                NumberOfColumns _\r\n            ) _\r\n        ).Delete _\r\n            Shift:=xlToLeft\r\n    End With\r\nEnd Sub<\/pre>\n<\/div>\n<\/li><li class=\"wp-block-post post-190 post type-post status-publish format-standard hentry category-code category-distribution category-vba\">\n<h2 class=\"has-link-color wp-elements-cd71dd8ec89fc068cec02e1d47b4491f wp-block-post-title\"><a href=\"https:\/\/keithratner.live\/optionexplicit\/deletebottomrowsfromworksheet\/\" target=\"_self\" >DeleteBottomRowsFromWorksheet<\/a><\/h2>\n\n<div class=\"entry-content wp-block-post-content is-layout-flow wp-block-post-content-is-layout-flow\"><p>Note: This method depends on <a href=\"https:\/\/keithratner.live\/optionexplicit\/getlastusedrownumberinworksheet\/\" rel=\"noopener noreferrer\" target=\"_blank\">GetLastUsedRowNumberInWorksheet<\/a><\/p>\n<pre>Sub DeleteBottomRowsFromWorksheet( _\r\n    ws As Worksheet _\r\n)\r\n    Dim _\r\n        strStartingEmptyRow As String, _\r\n        rngStartingRow As Range, _\r\n        rngEmptyRows As Range, _\r\n        lngLastRow As Long\r\n    lngLastRow = _\r\n        GetLastUsedRowNumberInWorksheet( _\r\n            ws _\r\n        )\r\n    If lngLastRow = _\r\n        -1 Then\r\n        Exit Sub\r\n    End If\r\n    strStartingEmptyRow = _\r\n        CStr( _\r\n            lngLastRow + 1 _\r\n        )\r\n    Set rngStartingRow = _\r\n        ws.Rows( _\r\n            strStartingEmptyRow & _\r\n            \":\" & _\r\n            strStartingEmptyRow _\r\n        )\r\n    Set rngEmptyRows = _\r\n        Range( _\r\n            rngStartingRow, _\r\n            rngStartingRow.End( _\r\n                xlDown _\r\n            ) _\r\n        )\r\n    rngEmptyRows.Delete _\r\n        shift:=xlUp\r\nEnd Sub<\/pre>\n<\/div>\n<\/li><li class=\"wp-block-post post-188 post type-post status-publish format-standard hentry category-code category-distribution category-vba\">\n<h2 class=\"has-link-color wp-elements-cd71dd8ec89fc068cec02e1d47b4491f wp-block-post-title\"><a href=\"https:\/\/keithratner.live\/optionexplicit\/getlastusedcolumnnumberinworksheet\/\" target=\"_self\" >GetLastUsedColumnNumberInWorksheet<\/a><\/h2>\n\n<div class=\"entry-content wp-block-post-content is-layout-flow wp-block-post-content-is-layout-flow\"><pre>Function GetLastUsedColumnNumberInWorksheet( _\r\n    ws As Worksheet _\r\n) As Long\r\n    Dim _\r\n        rng As Range, _\r\n        rngResults As Range\r\n    Set rng = _\r\n        ws.Cells\r\n    Set rngResults = _\r\n        rng.Find( _\r\n            what:=\"*\", _\r\n            After:=rng.Cells(1), _\r\n            Lookat:=xlPart, _\r\n            LookIn:=xlFormulas, _\r\n            SearchOrder:=xlByColumns, _\r\n            SearchDirection:=xlPrevious, _\r\n            MatchCase:=False _\r\n        )\r\n    If _\r\n        rngResults Is Nothing _\r\n        Then\r\n        GetLastUsedColumnNumberInWorksheet = _\r\n            -1\r\n    Else\r\n        GetLastUsedColumnNumberInWorksheet = _\r\n            rngResults.Column\r\n    End If\r\n    Exit Function\r\nErrorHandler:\r\n    GetLastUsedColumnNumberInWorksheet = _\r\n        -1\r\nEnd Function<\/pre>\n<\/div>\n<\/li><li class=\"wp-block-post post-186 post type-post status-publish format-standard hentry category-code category-distribution category-vba\">\n<h2 class=\"has-link-color wp-elements-cd71dd8ec89fc068cec02e1d47b4491f wp-block-post-title\"><a href=\"https:\/\/keithratner.live\/optionexplicit\/getlastusedrownumberinworksheet\/\" target=\"_self\" >GetLastUsedRowNumberInWorksheet<\/a><\/h2>\n\n<div class=\"entry-content wp-block-post-content is-layout-flow wp-block-post-content-is-layout-flow\"><pre>Function GetLastUsedRowNumberInWorksheet( _\r\n    ws As Worksheet _\r\n) As Long\r\n    Dim _\r\n        rng As Range, _\r\n        rngResults As Range\r\n    Set rng = _\r\n        ws.Cells\r\n    Set rngResults = _\r\n        rng.Find( _\r\n            what:=\"*\", _\r\n            After:=rng.Cells(1), _\r\n            Lookat:=xlPart, _\r\n            LookIn:=xlFormulas, _\r\n            SearchOrder:=xlByRows, _\r\n            SearchDirection:=xlPrevious, _\r\n            MatchCase:=False _\r\n        )\r\n    If _\r\n        rngResults Is Nothing _\r\n        Then\r\n        GetLastUsedRowNumberInWorksheet = _\r\n            -1\r\n    Else\r\n        GetLastUsedRowNumberInWorksheet = _\r\n            rngResults.Row\r\n    End If\r\n    Exit Function\r\nErrorHandler:\r\n    GetLastUsedRowNumberInWorksheet = _\r\n        -1\r\nEnd Function<\/pre>\n<\/div>\n<\/li><li class=\"wp-block-post post-184 post type-post status-publish format-standard hentry category-code category-distribution category-vba\">\n<h2 class=\"has-link-color wp-elements-cd71dd8ec89fc068cec02e1d47b4491f wp-block-post-title\"><a href=\"https:\/\/keithratner.live\/optionexplicit\/deleteallconnectionsfromworkbookoftype\/\" target=\"_self\" >DeleteAllConnectionsFromWorkbookOfType<\/a><\/h2>\n\n<div class=\"entry-content wp-block-post-content is-layout-flow wp-block-post-content-is-layout-flow\"><pre>Public Function DeleteAllConnectionsFromWorkbookOfType( _\r\n    wb As Workbook, _\r\n    xlconnType As XlConnectionType _\r\n)\r\n    Dim _\r\n        wbcn As WorkbookConnection\r\n    For Each wbcn In wb.Connections\r\n        If wbcn.Type = xlconnType Then\r\n            wbcn.Delete\r\n        End If\r\n    Next wbcn\r\nEnd Function<\/pre>\n<\/div>\n<\/li><li class=\"wp-block-post post-182 post type-post status-publish format-standard hentry category-code category-distribution category-vba\">\n<h2 class=\"has-link-color wp-elements-cd71dd8ec89fc068cec02e1d47b4491f wp-block-post-title\"><a href=\"https:\/\/keithratner.live\/optionexplicit\/deleteallslicersfromworkbook\/\" target=\"_self\" >DeleteAllSlicersFromWorkbook<\/a><\/h2>\n\n<div class=\"entry-content wp-block-post-content is-layout-flow wp-block-post-content-is-layout-flow\"><pre>Public Function DeleteAllSlicersFromWorkbook( _\r\n    wb As Workbook _\r\n)\r\n    Dim _\r\n        ws As Worksheet, _\r\n        shp As Shape\r\n    For Each ws In wb.Sheets\r\n        For Each shp In ws.Shapes\r\n            If shp.Type = msoSlicer Then shp.Delete\r\n        Next shp\r\n    Next ws\r\nEnd Function<\/pre>\n<\/div>\n<\/li><li class=\"wp-block-post post-179 post type-post status-publish format-standard hentry category-code category-distribution category-vba\">\n<h2 class=\"has-link-color wp-elements-cd71dd8ec89fc068cec02e1d47b4491f wp-block-post-title\"><a href=\"https:\/\/keithratner.live\/optionexplicit\/getfileextension\/\" target=\"_self\" >GetFileExtension<\/a><\/h2>\n\n<div class=\"entry-content wp-block-post-content is-layout-flow wp-block-post-content-is-layout-flow\"><pre>Public Function GetFileExtension( _\r\n    sFullFileNameWithPath As String _\r\n) As String\r\n    Dim fs As Object\r\n    Set fs = CreateObject(\"Scripting.FileSystemObject\")\r\n    GetFileExtension = _\r\n        fs.GetExtensionName( _\r\n            sFullFileNameWithPath _\r\n        )\r\n    Set fs = Nothing\r\nEnd Function<\/pre>\n<\/div>\n<\/li><\/ul>\n\n<nav class=\"wp-block-query-pagination is-content-justification-center is-layout-flex wp-container-core-query-pagination-is-layout-a89b3969 wp-block-query-pagination-is-layout-flex\" aria-label=\"Pagination\">\n\n\n<div class=\"wp-block-query-pagination-numbers\"><span aria-current=\"page\" class=\"page-numbers current\">1<\/span>\n<a class=\"page-numbers\" href=\"?query-15-page=2\">2<\/a>\n<a class=\"page-numbers\" href=\"?query-15-page=3\">3<\/a><\/div>\n\n<a href=\"\/optionexplicit\/wp-json\/wp\/v2\/pages\/353?query-15-page=2\" class=\"wp-block-query-pagination-next\">Next Page<\/a>\n<\/nav>\n\n<\/div>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"inline_featured_image":false,"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"footnotes":""},"class_list":["post-353","page","type-page","status-publish","hentry"],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/PgsIIA-5H","jetpack-related-posts":[{"id":248,"url":"https:\/\/keithratner.live\/optionexplicit\/","url_meta":{"origin":353,"position":0},"title":"About Me","author":"Keith","date":"February 17, 2023","format":false,"excerpt":"As a VBA (Visual Basic for Applications) Developer, I have a strong background in software development and a deep understanding of Microsoft Office applications such as Excel, Word, PowerPoint, and Access. I am skilled in creating custom VBA programming solutions to automate repetitive tasks, improve workflow, and increase efficiency in\u2026","rel":"","context":"Similar post","block_context":{"text":"Similar post","link":""},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/pages\/353","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/comments?post=353"}],"version-history":[{"count":8,"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/pages\/353\/revisions"}],"predecessor-version":[{"id":365,"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/pages\/353\/revisions\/365"}],"wp:attachment":[{"href":"https:\/\/keithratner.live\/optionexplicit\/wp-json\/wp\/v2\/media?parent=353"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}