Attach automaticaly a jpg file in a table Excel 2010 or Access 2010

Hi guys,

I have a table of about 20.000 records as the following sample except, field3 which is empty.

field1(number)      field2(text)      field3(hyperlink)
5                          rtyhjsrh      5.jpg
9                             sthrth      9.jpg
10                             ththfg      10.jpg
25                           fghdfgh      25.jpg

As I said the field3 is empty.
All the jpg files have the same name as the numbers in the field1.
Is there a way to attach automaticaly the hyperlinks of jpg file in my table in Excel or Access.

Thank you
marian68Asked:
Who is Participating?
 
SteveCommented:
The following code will put hyperlinks in for the information you have given, but mey require the full filepath rather than just "MyDesigns\" such as "C:\Program Files\Hp\MyDesigns\"

Sub AddHyperlinks()
x = 2
Do While Len(ActiveSheet.Cells(x, 1)) <> 0
ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Cells(x, 3), Address:= _
"MyDesigns\" & ActiveSheet.Cells(x, 1) & ".tif", TextToDisplay:=ActiveSheet.Cells(x, 1) & ".tif"
x = x + 1
Loop
End Sub

Open in new window

0
 
SteveCommented:
the following code (with path changes) would do it...

Sub AddHyperlinks()

x = 2
Do While Len(ActiveSheet.Cells(x, 1)) <> 0
    ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Cells(x, 3), Address:= _
        "C:\Users\xxx\Documents\" & ActiveSheet.Cells(x, 1) & ".jpg", TextToDisplay:=ActiveSheet.Cells(x, 1) & ".jpg"
x = x + 1
Loop
End Sub

Open in new window

0
 
SteveCommented:
If doing this in Access:

make sure Field3 is set as a hyperlink and will need to change the path againa dn the table name to suit:

UPDATE Table1
SET Table1.Field3 = [Table1]![Field1] & ".jpg#C:\Users\xxx\Documents\" & [Table1]![Field1] & ".jpg#";
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
marian68Author Commented:
Thanks a lot for your answers.
I tried the code in Excel and it doesn't work even if the cells are populated.
When I click on a hyperlink I am receiving the message: "cannot open the specified file".
I don't know if it matters but the files type is not jpg is "tif".
I placed a hyperlink in the worksheet to see the path and I set the same path in your code.
Your code puts in the cells the hyperlinks with the names like "7731.tif" and in the test cell where I imported mannually I have the MyDesigns\7731.tif. MyDesigns is the folder where the tif type files are stores.
Can you help me?
Thank you
0
 
SteveCommented:
could you place a first hyperlink in any workbook and then post that workbook here.

I can then get the code correct from that information.
0
 
marian68Author Commented:
It worked. Thank you both. The best solutions worked.<br />Thank you and have a nice weekend,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.