VBA-Photos to be imported from Sharepoint Site rather than local drive

[CainyUK]

Hi all,

Hoping someone can help me figure out a solution - ive checked a few posts and cant seem to get the right bits for what i need to figure this out myself

I currently have this VBA code that works perfectly, however, if others are wanting to use this report, as they dont have access to my local folder, they cant run the report.

Current code

Sub InsertPictures()

    
    Const fPath = "C:\Users\Me\Site\folder1\folder2\folder3\Photos"
    Dim cel As Range, picPath As String, tp As String, typ As String
    Dim pRng As Range
    Dim filename As String
    

        tp= Range("a2").Value
        typ= Range("a3").Value

    
    For Each cel In Range("c4:k4,c7:k7,c10:k10,c13:k13,c16:k16,c19:k19,c22:k22,c25:k25,c28:k28,c31:k31,c34:k34,c37:k37,c40:k40,c43:k43,c46:k46")

      
        picPath = fPath & "\" & tp& "\" & typ & "\" & cel.Value & ".jpg"
        If Not Dir(picPath, vbDirectory) = vbNullString Then
            With ActiveSheet.Pictures.Insert(picPath)
                Set pRng = cel.MergeArea.Offset(1, 0)
                With .ShapeRange
                    .LockAspectRatio = msoFalse
                    .Left = pRng.Left
                    .Top = pRng.Top
                    .Width = pRng.MergeArea.Width
                    .Height = pRng.MergeArea.Height
                End With
            End With
        End If
    Next cel
    


End Sub

Ive used Sharepoint.source (iirc) text before on different projects ( maybe that was in power query im not sure), but cant seem to get this to work as it generates the file path from cells within the report to help navigate to the desired photo and import.

i understand that i need to change the "\" to "/" which i had done - maybe its because my tp+typ strings dont cater for the spaces in a URL that are changed to %20?

a solution would be appreciated

 

[Micron]

i understand that i need to change the "\" to "/"

I don't think so, because you'd be trying to use url characters in a file path string and they won't work. I believe you need to use the UNC path when doing so in code, and then spaces should not be a problem. UNC path starts with \\ and server name (in this case, the Sharepoint server). So like
\\ServerNameHere\folder1\folder2\folder3\etc.\fileNameHere.jpg

If you use File Explorer and map a drive letter to the SP server you can get its name. You may need to ask your IT person for help with that, assuming they won't balk at letting you interact with the SP server through vba code.