Insert pictures by vba from an sharepoint folder

[sjorsp99]

Hello,

I’m trying to insert pictures from a SharePoint folder to an Excel worksheet, but I’m having trouble getting the right path. The code is working fine in a local folder; it shows perfectly the pictures I have in the folder. Is it possible to load pictures from a shared folder?

The code I am currently using:

Sub HaalEnPlakFotos()


'Begin met HaalNamen
Dim fso As Object
Dim folder As Object
Dim file As Object
Dim picPath As String
Dim i As Long

picPath = "C:\Users\xxx\OneDrive - xxxx\Documents\Excel"
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder(picPath)

i = 2
For Each file In folder.Files
If Right(file.Name, 4) = ".jpg" Then
Cells(i, "Q").Value = file.Name
i = i + 1
End If
Next file

'Ga verder met PlakFotos
Dim rng As Range
Dim cel As Range
Dim pic As Picture
Dim j As Long

Set rng = Range("Q2:Q" & Cells(Rows.Count, "Q").End(xlUp).row)

i = 2
j = 1
For Each cel In rng
If cel.Value <> "" Then
Set pic = ActiveSheet.Pictures.Insert(picPath & cel.Value)
With pic
.ShapeRange.LockAspectRatio = msoFalse
.Width = 300
.Height = 150
.Top = Cells(i, j).Top
.Left = Cells(i, j).Left
End With
If j = 1 Then
j = 8
Else
j = 1
i = i + 12
End If
End If
Next cel

End Sub

 

[sjorsp99]

Maybe the below will help:
Image Example.xlsx

This will help forsure.

The only thing I need to find out is how to get all the file names in one click to column C directly from sharepoint.

 

[Georgiboy]

I think i read on Google about that export to excel feature, you can export and use filters to get a list of the links.

Not sure if the below link will help:
Export to Excel from SharePoint or Lists - Microsoft Support