Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2012-08-10
6
Medium Priority
?
575 Views
Last Modified: 2012-08-10
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
0
Comment
Question by:marian68
  • 4
  • 2
6 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 38281322
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
 
LVL 24

Assisted Solution

by:Steve
Steve earned 2000 total points
ID: 38281366
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
 

Author Comment

by:marian68
ID: 38281716
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 24

Expert Comment

by:Steve
ID: 38281732
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
 
LVL 24

Accepted Solution

by:
Steve earned 2000 total points
ID: 38281810
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
 

Author Closing Comment

by:marian68
ID: 38281880
It worked. Thank you both. The best solutions worked.<br />Thank you and have a nice weekend,
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
In this article, I will demonstrate that how to do a PST migration from Exchange Server to Office 365. This method allows importing one single PST, or multiple PST's at once.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question