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 |
Want to talk?
Drop us a line. We are here to answer your questions 24*7.