Changing URL Style Within VBA

Wishv Prajapati

July 23, 2014

All Post

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