Modifying code: Macro to update files on SharePoint vs Network Drive

[GTS]

Greetings,

This site has helped me 'get over the hump' a number of times and I'm hoping that will be the case again.
I've now spent several days on this over the past couple of months. I can't seem to quite get it.

I have a template file where one can set up to a few dozen cell values. Eg - Quote #, Company name, Currency exchange rates, etc.
This file is placed in the the folder that holds a dozen or two Estimate files. A macro is run which opens the Estimate files one by one, updates the values, saves, then closes the file.
This all worked beautifully for many years on our servers. We are now on SharePoint. While we can sync to OneDrive and the macro still works (with a tweak), it is not ideal (each user is a different path).

Here is the core part of the code that I'm trying to adapt. The macro builds a list of filenames (with some conditions) via the Dir command.
I know that I somehow have to modify this so that it can work with the URL path of SharePoint instead of the network path.
Also, the Workbooks.Open will have to handle this path as well.
Aside - I was able to adapt another template macro which generates a list of filenames and path from a SharePoint folder. It involved drive mapping. I've made an attempt to do that here, but no luck so far.

strPrj = "*" & Range("PRJ_NO") 'Pick up the Job or Quote number, just to help limit the files this macro will act on.  NOTE - This is a portion of the filename.
    strPath = ThisWorkbook.Path & "\" 'Pick up the Path and add the backslash.
    strFilename = Dir(strPath & strPrj & "*.xls*") 'Restricted to only Excel workbook files.
    
    'If no matches found, then exit the macro.
    If strFilename = "" Then
        MsgBox "No files found matching: " _
            & strPath & strPrj & "*.xls*"
        Exit Sub
    End If
 
    Application.EnableEvents = False
    On Error Resume Next
    While strFilename <> ""
        Set wbkCurr = Workbooks.Open(strPath & strFilename)
        If Not wbkCurr Is Nothing Then 
--- Update values...

I'm hoping someone can help / point me in the right direction.
Thank you.

 

[Alex Blakenburg]

I have been interested in this for a while but haven't actually played with the code.
The link below to stackoverflow is one of the few I have found to address the issue. The code is fairly lengthy but hopefully its just a matter of calling the function.
Although it says OneDrive it covers Sharepoint.

Excel's fullname property with OneDrive

Based on my reading I would think that the version by Alain YARDIM 30 Jan 2021, is the latest iteration.
The previous version by Peter G. Schild 2 Apr 2020 is right at the top and shows you how to call it. I think I might have tried this one at one stage to see if it worked in principle.

Good Luck, I will be following this with interest.

 

[GTS]

Thanks Alex,

Interesting stuff, however it all seems intent on getting the 'drive' path (C:\...) and/or converting an "https://..." path over to a drive path. I've seen similar formulas and macros in my searching. In fact, my first quick workaround was along the lines of what Rod Makin posted and using Environ to get the particular User initials that I needed. (This is for work... multiple people.) But this works on the OneDrive sync of the SharePoint files. And we all have to be using the same 'sync'. My goal was to work directly with the SharePoint files.

I have managed to achieve this, but had to do it in 2 steps. My file with the macro is always stored in the same folder as the files I'm trying to act on. Simply using =cell(filename) in the file got me the "https://..." path (I stripped off the filename) on a worksheet. Another macro I'm using (I've seen on this site and elsewhere) generates the list of filenames from the folder (on the same worksheet). Then, a macro loop using Workbook.Open on the path and the filenames cycles thru the files and does the updating.

I have the feeling that there are caveats to how I'm doing this, but it appears to be working. I was really hoping that it would be as simple as some alternate to the DIR command that recognized URLs, but that does not seem to be the case.

 

[Alex Blakenburg]

I don't know if this help you at all but I had some code which I use to put the Full File name and path to the clipboard.
It didn't work for OneDrive files and was putting in lots of the %20s into the file name part of the string when I used the string to open a OneDrive file.
I just tripped over that simple reversing the "\" to "/" to work.

Sorry I have had this code for a long time and wasn't using Option Explicit at the time, hence no Dim statements.

Sub FullFileName()

    FilePath = ActiveWorkbook.path
    Filename = ActiveWorkbook.Name
    
    ' Reversed slash for it to work as a http path
    FullName = FilePath & "/" & Filename

End Sub

The additional code I had to put it to the clipboard and put out a message was:-

    Set oFullName = New DataObject
    
    oFullName.SetText FullName
    oFullName.PutInClipboard
            
    Msg = FullName & vbCr & vbCr & _
            "File Path & Name stored in Clipboard use Ctrl+V to Paste"
            
    MsgBox prompt:=Msg, Title:="File Path & Name"