{"id":1237,"date":"2014-07-23T10:42:45","date_gmt":"2014-07-23T10:42:45","guid":{"rendered":"https:\/\/poiseddevelopers.com\/reality-tech\/?p=1237"},"modified":"2024-05-13T10:41:16","modified_gmt":"2024-05-13T10:41:16","slug":"changing-url-style-within-vba","status":"publish","type":"post","link":"https:\/\/poiseddevelopers.com\/reality-tech\/changing-url-style-within-vba\/","title":{"rendered":"Changing URL Style Within VBA"},"content":{"rendered":"<p>I recently encountered an issue when a user within a file-select popup navigated to SharePoint via an http: reference, causing the code to fail. It turns out, the code worked great with UNC notation.<\/p>\n<p>The routine below prompts for Excel files, and changes any reference to http: and corrects the \/ orientation to and then saves it into the active cell:<\/p>\n<pre lang=\"php\">Sub browseWorkbooks()\r\nDim FileSpec As String\r\n \r\nFileSpec = Application.GetOpenFilename(\"All Excel Files (*.xl*;*.xlsx;*.xlsm;*.xlsb;*.xlam;*.xltx;*.xltm;*.xls;*.xla;*.xlt;*.xlm;*.xlw),*.xl*;*.xlsx;*.xlsm;*.xlsb;*.xlam;*.xltx;*.xltm;*.xls;*.xla;*.xlt;*.xlm;*.xlw\", , \"Select source workbook\")\r\n     If FileSpec &lt;&gt; \"False\" Then\r\n        'Change to UNC\r\n        FileSpec = Replace(FileSpec, \"http:\", \"\")\r\n        FileSpec = Replace(FileSpec, \"\/\", \"\")\r\n        ActiveCell = FileSpec           \r\n     End If\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>I recently encountered an issue when a user within a file-select popup navigated to SharePoint via an http: reference, causing the code to fail. It turns out, the code worked great with UNC notation. The routine below prompts for Excel files, and changes any reference to http: and corrects the \/ orientation to and then [&hellip;]<\/p>\n","protected":false},"author":4,"featured_media":1239,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[48],"tags":[],"class_list":["post-1237","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-vba"],"acf":[],"_links":{"self":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/1237","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/comments?post=1237"}],"version-history":[{"count":4,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/1237\/revisions"}],"predecessor-version":[{"id":3951,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/1237\/revisions\/3951"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/media\/1239"}],"wp:attachment":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/media?parent=1237"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/categories?post=1237"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/tags?post=1237"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}