Changing URL style within VBA

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
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

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