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

 

[Georgiboy]

Have you tried the new IMAGE function in Excel 365?

I am not sure if you have it but you write it as a formula in a cell, tyr typing =IMAGE( in your excel to see if you have it. It allows you to place an image in a cell from a web link.

For example the below should load an image into a cell:

=IMAGE("https://slp-statics.astockcdn.net/static_assets/staging/23summer/home/EMEA/curated-collections/card-2.jpg?width=580&format=webp")

 

[sjorsp99]

Have you tried the new IMAGE function in Excel 365?

I am not sure if you have it but you write it as a formula in a cell, tyr typing =IMAGE( in your excel to see if you have it. It allows you to place an image in a cell from a web link.

For example the below should load an image into a cell:

=IMAGE("https://slp-statics.astockcdn.net/static_assets/staging/23summer/home/EMEA/curated-collections/card-2.jpg?width=580&format=webp")

Thank you for helping me out.

But i tried the new image formula but it won't show a picture.
Could it be something to do with permissions?

 

[sjorsp99]

Thank you for helping me out.

But i tried the new image formula but it won't show a picture.
Could it be something to do with permissions?

It worked
My link wasn't correct.
I will now try to automate this process.

 

[sjorsp99]

Have you tried the new IMAGE function in Excel 365?

I am not sure if you have it but you write it as a formula in a cell, tyr typing =IMAGE( in your excel to see if you have it. It allows you to place an image in a cell from a web link.

For example the below should load an image into a cell:

=IMAGE("https://slp-statics.astockcdn.net/static_assets/staging/23summer/home/EMEA/curated-collections/card-2.jpg?width=580&format=webp")

Do you also know how i can get the file names in a sharepoint folder?

 

[Georgiboy]

No sorry, Sharepoint is blocked on the server I work on. I would imagine you would right click on the file and it would be in those options. That is how i get the links on OneDrive.

When you are in the folder, is there an 'export to excel' option in the ribbon?

 

[sjorsp99]

No sorry, Sharepoint is blocked on the server I work on. I would imagine you would right click on the file and it would be in those options. That is how i get the links on OneDrive.

When you are in the folder, is there an 'export to excel' option in the ribbon?

Yes the option is there.

it will load a query into excel but it downloaded a web query file.

If this is automated I can easily insert the pictures. Do you have any experience with that?

 

[Georgiboy]

The automation of the images on the spreadsheet would depend of the layout/ design of the workbook.

As for the image function, you don't have to write the link in the formula, the link could be in another cell. You would replace the link part of the formula with a cell reference.

 

[sjorsp99]

The automation of the images on the spreadsheet would depend of the layout/ design of the workbook.

As for the image function, you don't have to write the link in the formula, the link could be in another cell. You would replace the link part of the formula with a cell reference.

I have multiple pictures in the folder and the goal is to insert the pictures in one click to the worksheet.
In the picture below is how i want to insert the pictures

 

[Georgiboy]

Maybe the below will help:
Image Example.xlsx