Creating a Tracker in Excel for Employess on/off jobsite

[BMilleRock]

I currently have an excel workbook and sheet that allows me to use simple functions like (If(ISNA(VLOOKUP)) where I only have to enter an employees' badge # and the rest of their information populates to the right of their badge # (to include the time of the inputted badge #, meaning the time they entered the jobsite that day). The only thing I cannot figure out is

1) how do I attach their photo IDs to their rows in the master sheet of all the employees with all their information and

2) how do I use my simple function, or a function such as LOOKUP() to take an input, such as badge# (123456) and return their photo in a column like the rest of their information??


Thank you!

 

[Perpa]

BMilleRock,
Welcome to the Forum!
You haven't indicated which columns have the Employee Name or Badge Number. Also you haven't indicated where the picture files exist (folder name) or how they are named. And we also need to know where the pictures are to be placed.

However, the following link posted by tonyyy on this Forum sometime back, provides a macro solution:

'Insert multiple images from a folder based on names in another column'
https://www.mrexcel.com/forum/excel...es-once-folder-based-name-another-column.html

You will need to change the column D:D refererence to the column you use for picture names, (list of Employee names or badge numbers, or a new column Picture Names). And the column location where the picture would be placed in the same row as the picture name. In the example it was column '2', or "B".
As tonyyy stated:
'The "names" in Column D have to match the images in your folder - including any file extension.
So if the picture is named 231.jpg, the name in Column D must also be named 231.jpg.'

The code in post 8 of that link looks to be a good match. You would need to amend the code as noted above, then copy and paste that amended code into a standard module ('Alt + F11' to open the VB Editor window).
To run the code press 'Alt + F8', then select the module name 'InsertPicsr1' and press run.
Try this on a copy of your workbook first so you don't lose any data. Let us know if this is helpful.
Perpa

 

[BMilleRock]

All was very helpful and I tried my best to get it to work, however, nothing is happening still. I have 15 columns with information on employees (715 total). My 14th column is labeled Photo ID. I put the exact name of the employee's photo that is saved to my desktop in a folder. I inserted the code you directed me to and I ran the macro. Nothing happened and no picture occurred, but no errors popped up with the code.

All of the above is on my master database sheet, but I have two additional sheets (IN SHEET/OUT SHEET) to track when employees enter and leave the job site.
Using IF(ISNA(VLOOKUP)) I pull all their information and time stamp them just by entering their badge # in the first column and row on the IN/OUT SHEETs.
Is there a way to have the photo transfer with all their information if I can ever get the photo to link to the original master sheet? Or do I have to run another macro on the IN/OUT SHEETs in order to have the photo appear in addition to all the employee info/time stamp that populates from the simple If/ISNA/VLOOKUP code?

Thank You.

BMilleRock,
Welcome to the Forum!
You haven't indicated which columns have the Employee Name or Badge Number. Also you haven't indicated where the picture files exist (folder name) or how they are named. And we also need to know where the pictures are to be placed.

However, the following link posted by tonyyy on this Forum sometime back, provides a macro solution:

'Insert multiple images from a folder based on names in another column'
https://www.mrexcel.com/forum/excel...es-once-folder-based-name-another-column.html

You will need to change the column D:D refererence to the column you use for picture names, (list of Employee names or badge numbers, or a new column Picture Names). And the column location where the picture would be placed in the same row as the picture name. In the example it was column '2', or "B".
As tonyyy stated:
'The "names" in Column D have to match the images in your folder - including any file extension.
So if the picture is named 231.jpg, the name in Column D must also be named 231.jpg.'

The code in post 8 of that link looks to be a good match. You would need to amend the code as noted above, then copy and paste that amended code into a standard module ('Alt + F11' to open the VB Editor window).
To run the code press 'Alt + F8', then select the module name 'InsertPicsr1' and press run.
Try this on a copy of your workbook first so you don't lose any data. Let us know if this is helpful.
Perpa

 

[Perpa]

BMilleRock,
It didn't sound as though you amended the code for the Photo ID (filenames) to be located in column 15, "O"?

Also, what is the complete pathname to the folder on your desktop where the pictures are located? Right click on the folder and select 'Properties' and the complete filename should be shown in the window slots that opens. Provide us a copy of that path.

What column are you putting the photo filenames in, not the photos, but the list of photo file names?
Maybe furnish a small sample list of all 15 columns with any sensitive info changed so we can see what that looks like.

If I understand correctly, you have two worksheets where you want to display the photo, the 'IN SHEET' or the 'OUT SHEET'. So you are looking at the 'IN SHEET' or the 'OUT SHEET' when you enter '... their badge # in the first column'. Correct ?

The code must be amended at least on the 4 lines shown with red font below (and probably others) depending on the answers to the above questions. Let's see where your responses take us.
Perpa

 Sub InsertPicsr1()
Dim fPath As String, fName As String
Dim r As Range, rng As Range
Dim shpPic As Shape


Application.ScreenUpdating = False
fPath = [COLOR=#ff0000]"C:\Temp\"[/COLOR]
Set rng = Range("[COLOR=#ff0000]D2:D[/COLOR]" & Cells(Rows.Count, [COLOR=#ff0000]4[/COLOR]).End(xlUp).Row)
For Each r In rng
    On Error GoTo errHandler
    If r.Value <> "" Then
        Set shpPic = ActiveSheet.Shapes.AddPicture(Filename:=fPath & r.Value & ".jpg", linktofile:=msoFalse, _
            savewithdocument:=msoTrue, Left:=Cells(r.Row, [COLOR=#ff0000]2[/COLOR]).Left, Top:=Cells(r.Row, [COLOR=#ff0000]2[/COLOR]).Top, Width:=-1, Height:=-1)
        With shpPic
            .LockAspectRatio = msoTrue
            If .Width > Columns([COLOR=#ff0000]2[/COLOR]).Width Then .Width = Columns([COLOR=#ff0000]2[/COLOR]).Width
            Rows(r.Row).RowHeight = .Height
        End With
    End If
errHandler:
If Err.Number <> 0 Then
    Debug.Print Err.Number & ", " & Err.Description & ", " & r.Value
    On Error GoTo -1
End If
Next r
Application.ScreenUpdating = True
End Sub

 

[BMilleRock]

Master Database:
Column 1: ID NUMBER example DAMCIV170244
Column 2: Full Name of Employee
Column 3: Document Number
Column 4: Company
Column 5: Nationality
Column 6: DOB
Column 7: Issued ID
Column 8: Expiration of ID
Column 9: Location
Column 10: Privileges
Column 11: SEEKED Y/N
Column 12: Barcode on ID
Column 13: Interviewed Y/N
Column 14: Picture ID example DAMCIV170244.jpg (which is exactly what the picture is saved in on my desktop in the folder)

So in the Code you presented me, "D2:D" would be changed to "N2:N" and Rows.Count, 712 (because I have 712 employees)

I'm not sure what the other rows, columns further in the code would be changed to.

Then on the IN/OUT Sheet I have an excel function that allows me to just ender their ID number (column 1) and it will populate all the information from columns 2 - 13 in addition to a time stamp. If I can get the code to work on the master database sheet, will it also populate their photo in column 14 on the In/Out sheets?

 

[Michael M]

MAke sure this line is the correct location of the photos

fPath = "C:\Temp\"

If you step through the code using F8, when the yellow code line gets to this line

Set shpPic = ActiveSheet.Shapes.AddPicture(Filename:=fPath & r.Value & ".jpg", linktofile:=msoFalse, _
            savewithdocument:=msoTrue, Left:=Cells(r.Row, 2).Left, Top:=Cells(r.Row, 2).Top, Width:=-1, Height:=-1)

hover your mouse over shpPic at the start of the line and it should give you the correct directory and file name !
If it doesn't, I'm guessing your filepath OR filename are incorrect

 

[BMilleRock]

Sub InsertPicsr1()
Dim fPath As String, fName As String
Dim r As Range, rng As Range
Dim shpPic As Shape

Application.ScreenUpdating = False
fPath = "C:\Users\Bridgette\Desktop\IRAQ\DATABASE PHOTOS"
Set rng = Range("N2:N" & Cells(Rows.Count, 712).End(xlUp).Row)
For Each r In rng
On Error GoTo errHandler
If r.Value <> "" Then
Set shpPic = ActiveSheet.Shapes.AddPicture(Filename:=fPath & r.Value & ".jpg", linktofile:=msoFalse, _
savewithdocument:=msoTrue, Left:=Cells(r.Row, 2).Left, Top:=Cells(r.Row, 2).Top, Width:=-1, Height:=-1)
With shpPic
.LockAspectRatio = msoTrue
If .Width > Columns(16).Width Then .Width = Columns(16).Width
Rows(r.Row).RowHeight = .Height
End With
End If
errHandler:
If Err.Number <> 0 Then
Debug.Print Err.Number & ", " & Err.Description & ", " & r.Value
On Error GoTo -1
End If
Next r
Application.ScreenUpdating = True
End Sub

 

[BMilleRock]

I found the error! I didnt have \ after fPath!

It's working now!!

So for the follow on question, will this code allow me to transfer the photos to the other sheets just by using a VLOOKUP function? (my IN/OUT Sheets as I have stated above in comments)

 

[BMilleRock]

Also, is there any way I can get the photo to pop up in column 16 and actually attach to column 16 instead of popping up in colum 2, row 2?

 

[BMilleRock]

Correction * still not producing the picture. Did not find error/fix it.