Previous Topic Next Topic

Picture directory

Closed
SL_ice
Posts
1
Registration date
Tuesday September 11, 2018
Status
Member
Last seen
September 11, 2018
 - Sep 11, 2018 at 11:05 AM
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
 - Sep 11, 2018 at 11:47 AM
Same kind of scenario where I need to pull in a picture from a server folder into my spreadsheet from an ID number for a person.

Sheet 1 has my raw data...first name (A2), last name (B2), id number (C2), location code (D2), birth date (E2) in a list format down to row 130

Sheet 2 I am trying to format the data to have the picture with the sheet 1 data listed underneath and the picture pulled in using the id number.

Is there an easy way to repeat the macro code to pull this picture in since I'm not just going in sequential order down in rows.

I have no idea if I'm making any sense or not.

I want to pull in the picture based off of A3 data (which is also the file name of the picture) and have the picture placed in A1. And I want to repeat this process for D1, G1, J1, A6, D6, G6, J6...so on and so forth down my page to J176. Do I need a separate script for each picture I want to pull in or is there an easier way?

1 reply

Answer 1 / 1
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
 509
Updated on Sep 11, 2018 at 11:49 AM
Hi SL_ice,

You start that you want to pull a picture from the ID number in column C and you end with that you want to pull in a picture from the name in column A. I went with the latter.

Check out the code below: 
Sub RunMe()
Dim xRow, xCol As Integer
Dim PictPath As String

xRow = 1
xCol = 1
PictPath = "C:\MyPictures\"

Sheets("Housing Roster").Select

For Each cell In Range("A3:A130")
    With Sheets("Picture Directory Test")
        .Select
        .Cells(xRow, xCol).Select
        .Pictures.Insert(PictPath & cell.Value & ".jpg").Select
    End With
    
    If xCol = 10 Then
        xCol = 1
        xRow = xRow + 5
    Else
        xCol = xCol + 3
    End If
Next cell

End Sub


Change code line 7 to match your picture directory.
Also check code line 15 to see if it matches your picture extention (.jpg).

Is this to your liking?

Best regards,
Trowa
0