Changing URL style within VBA

41

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 saves it into the active cell:

Sub browseWorkbooks()
Dim FileSpec As String

FileSpec = 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")
     If FileSpec <> "False" Then
        'Change to UNC
        FileSpec = Replace(FileSpec, "http:", "")
        FileSpec = Replace(FileSpec, "/", "")
        ActiveCell = FileSpec           
     End If

Share this entry

Leave a Reply

Your email address will not be published. Required fields are marked *

Table of Contents

Categories

Categories