Solved: Hyperlinking photos from directory

[ByteReaper]

Hi there

I hope I am posting this in the correct forum , if not please move to the correct one admin.

I am hoping that you guys can help me as I am having a problem wrapping my head around this.

I have a folder full of photos of assets that we verify out in the field , all of the photos has been renamed to their barcode number , i.e. 43056.

We then read all of these barcodes into a excel sheet "manually" with the relevant description of that asset. What I am trying to do is to have excel in VBA read all the barcodes in the excel worksheet "just one column" and compare that to the files in that folder and then hyperlink that photo to the barcode that corresponds to its number.

The way I want this to work is to have excel have a dialog box open when you click on the button and then you can point excel to where the photos are. Now I have tried many different approaches and I just cant seem to get it working.

Please forgive me if my grammar is not perfect as my native tongue is not English.

I hope it makes sense

Regards
Rudolph

[BrianMH]

So to clarify you want to script to cycle through all the pictures in the folder you point it to, retrieve the file names into a column, and then add a hyperlink in the next column pointing to the photo. Is this correct or will you be importing a list of numbers in some other way?

[Bob Phillips]

Public Sub HyperlinkImages()
Dim path As String
Dim lastrow As Long
Dim i As Long

With Application.FileDialog(msoFileDialogFolderPicker)

.AllowMultiSelect = False
If .Show = -1 Then

path = .SelectedItems(1)

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow

If Dir(path & Application.PathSeparator & .Cells(i, "A").Value) <> "" Then

.Hyperlinks.Add Anchor:=.Cells(i, "A"), _
Address:=path & Application.PathSeparator & .Cells(i, "A").Value, _
TextToDisplay:=.Cells(i, "A").Value
End If
Next i
End With
End If
End With
End Sub

[ByteReaper]

Hi

Thanks for taking the time , but no the column is already populated by barcodes that was entered manually by data capturers , all that needs to happen now is I want the code to go to the selected photo folder ,look for the photo that corrosponds to that cell value "the barcode" and then hyperlink that cell to that photo

If you look at the picture you will see that column A is populated by the barcodes , now I want to have my code go look in the photos folder "On Left" if the photo exists if it does then hyperlink that cell to that photo in column A. I hope I am making sense

Sorry I see I cant post Images here yet but here is the URL
tt ww o a
h**p://w**.imagehost.c*.z*/image-867A_4FD99BAC.jpg

[ByteReaper]

Hi xld

Thanks for the code but I Keep getting Run Time error 52 : Bad file name or number when executing this code , And its reading back in the debugger on this line , so I am unsure what the problem is

If Dir(path & Application.PathSeparator & .Cells(i, "A").Value) <> "" Then

[Bob Phillips]

One of the values is column A is bad. What is the value of i when this happens?

[ByteReaper]

Hi xld

I managed to sort out the problem in column A there was some none barcode info which kicked it out and I deleted those and I see that it also doesnt like empty cells much so I had to fix those. Just one more thing when I run the code and my barcodes in excel doent have the .jpg extention on them it does not hyperlink . So is there a way that we can modify the code so that it will hyperlink the barcode in excel even if the barcode doesnt have the .jpg extention on it in excel that is.

Hope that makes sense

Thanks so much for your help thus far

[Bob Phillips]

We can cater for blanks and bad if you wish. What other extension do you have?

[ByteReaper]

Yeah blanks and bad would be great if we can implement thanks, the problems isnt really the extentions , its just when I have the value say "04440.jpg" in excel it can hyperlink to the file "04440.jpg" but as soon as I change the value in excel to just "04440" it does not recognize it as the same ,and then does not hyperlink to the file.

Thanks

[ByteReaper]

P.S. I am learning so much right now and enjoying it Thank you

[Bob Phillips]

Can you give me an example of a bad value that throws the error?

[ByteReaper]

Sure on Blanks it gives me "Invalid Procedure Call or argument" and it isnt doing anything else funny at the moment , except for the issue that it aint hyperlink the cell values if the value doesnt hav a .jpg extention in it

[ByteReaper]

Also I was wondering when it doesnt get the file here

If Dir(path & Application.PathSeparator & .Cells(i, "A").Value) <> "" Then

.Hyperlinks.Add Anchor:=.Cells(i, "A"), _
Address:=path & Application.PathSeparator & .Cells(i, "A").Value, _
TextToDisplay:=.Cells(i, "A").Value
Else

End If

Can we maybe create a new worksheet and then just dump all those non finded ones to column A.

[ByteReaper]

I started by creating this procedure to create the work book like this

Public Sub MissingPhotos()

Dim ws As Worksheet
Dim dtConvert As String

dtConvert = Date
dtConvert = Format(dtConvert, "Long Date")

Set ws = Worksheets.Add
ws.Name = ("Missing Photos " & dtConvert)

End Sub
And then when the Else statement triggers in your code to do this

Dim cell As Variant

For Each cell In Worksheets("MissingPhotos").Range("A1:A5000")
If cell = "" Then
cell.Value = Temp
End If
'Next cell
But as you can probably guess I am having a hard time figuring out how to combine all of these . And I am having logical problems all over the place

[ByteReaper]

Hi guys , ok I have done this so far to the code

Public Sub HyperlinkImages()
Dim path As String
Dim lastrow As Long
Dim i As Long



With Application.FileDialog(msoFileDialogFolderPicker)

.AllowMultiSelect = False
If .Show = -1 Then

path = .SelectedItems(1)

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow
ReDim BarDump(0 To i) ' Global Variable

If Dir(path & Application.PathSeparator & .Cells(i, "A").Value) <> "" Then

.Hyperlinks.Add Anchor:=.Cells(i, "A"), _
Address:=path & Application.PathSeparator & .Cells(i, "A").Value, _
TextToDisplay:=.Cells(i, "A").Value
Else
BarDump(i) = .Cells(i, "A").Value

End If
Next i

End With
End If
End With

'Insert relevant procedure here

End Sub

Public Sub MissingPhotos()

Dim ws As Worksheet
Dim dtConvert As String

dtConvert = Date
dtConvert = Format(dtConvert, "Long Date")

Set ws = Worksheets.Add
ws.name = ("Missing Photos " & dtConvert)

Range("A1").Resize(UBound(BarDump) - LBound(BarDump) + 1, 1) = Application.Transpose(BarDump)

End Sub


But I cant seem to get the photos it does not get to dump into the Missing Photos Worksheet, and I still havent figured out how to avoid the .jpg extention in excel for it to hyperlink

[ByteReaper]

Any help will be appreciated

[Kenneth Hobs]

I am lost. Do you want images or links to images? I don't understand the missing part either. It might be better if you attach very short before and after workbooks so that we can better see what you want.

[ByteReaper]

Hi

Thanks for taking the time.

The code xld created works great except ,that when it hyperlinks to the photos , the value in the excel woorkbook has to have the .jpg extention. The picture here shows that values that do have the .jpg hyperlinks to the photo where as the values that dont doesnt hyperlink

http://www.imagehost.co.za/image-1E11_4FE02DD8.jpg

Here is an example of what the folder with the photos in look like. Please note that the photos were renamed to there barcode number - Manually.

http://www.imagehost.co.za/image-C81A_4FE02DD8.jpg

So what I wanted to do is to have the code just hyperlink to the photo which has a matching barcode number. Xlds code works great the only issue is if the cell value has the .jpg extention on it, it hyperlinks if not it doesnt. Like example 1 . And I was thinking to create a worksheet with the current date afterwards with all the barcode photos it could not hyperlink i.e. it could not find

Hope it makes more sense

[Bob Phillips]

Which one is not hyperlinking that should? I cannot see any discrepancies in your example.

[ByteReaper]

Hi Xld

All of them should be linking but only the ones with the .jpg extention is hyperlinking and I need for them to be without the .jpg extention.

So in other words in the above example 00199 is linking in excel to the file because in excel it has a .jpg extention. But now for instance 00200 which is the barcode directly beneath it ,it is not linking because it doesn't have that .jpg extension. We capture these barcodes as in the example without the .jpg extention and need them to hyperlink.

So when I add the .jpg extention to the barcode in excel it hyperlinks else it doesnt.

Hope it makes sense , I am really sorry if I am not making sense I am trying my best

[Bob Phillips]

They are all jpg extensions, so if you had the filename in the list they would be linking. But your filenames and list items are not the same, the filename is 00200 -B, not 00200 as you have in the list.

We could just test for files starting with what you have

Public Sub HyperlinkImages()
Dim path As String
Dim filename As String
Dim lastrow As Long
Dim i As Long

With Application.FileDialog(msoFileDialogFolderPicker)

.AllowMultiSelect = False
If .Show = -1 Then

path = .SelectedItems(1)

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow

If .Cells(i, "A").Value <> "" Then

filename = Dir(path & Application.PathSeparator & .Cells(i, "A").Value & "*")
If filename <> "" Then

.Hyperlinks.Add Anchor:=.Cells(i, "A"), _
Address:=path & Application.PathSeparator & filename, _
TextToDisplay:=.Cells(i, "A").Value
End If
End If
Next i
End With
End If
End With
End Sub

[ByteReaper]

Thanks Xld

That fixed it up , I really appreciate all of your help , I would just like to know where you guys think is the best place to start learning vba as I would like to have a good fundamental understanding of the language and how to use it

And again thank you

[Bob Phillips]

Best place? Here. Get some projects, ask specific questions, read all post on topics that interest you, and try and help by answering questions.

[ByteReaper]

Will do thanks Xld , is there something that I need to do to mark this thread as solved

[Bob Phillips]

If you are not using Chrome, there should be a Thread Tools option at the start of the threads, it is in there.

[aspbarcode]

This is exactly what I am trying to to with my barcodes on excels, thanks!!