Autolink

Jerry Sullivan

MrExcel MVP

Joined

Mar 18, 2010

Messages

8,787

• Apr 17, 2012

• #2

Hi and Welcome to the Board,

Please clarify a few things?

1. Is the image you mention in the workbook, or do you want a Text Hyperlink from the Workbook to open an image file?

2. Would the Hyperlink have to "find" a file that matches a pattern (like 02-71234*.tif) or is there a single specific file that would be linked?

3. Is it possible that two images could match that pattern, and what should the result be if that happens?

 

Upvote 0

P

pnasz

New Member

Joined

Apr 16, 2012

Messages

19

• Apr 17, 2012

• #3

Its a excel workbook containing the image.
workbook is in the folder located on network.
when i type the image code on the particular column link should be generated automatically.
when i click the link workbook should be open containing pictures.

 

Upvote 0

Jerry Sullivan

MrExcel MVP

Joined

Mar 18, 2010

Messages

8,787

• Apr 17, 2012

• #4

I'm not understanding you.

What is the file type (file extension) of the file that should be opened when you click the link?

As asked previously,
2. Would the Hyperlink have to "find" a file that matches a pattern (like 02-71234*.tif) or is there a single specific file that would be linked?

3. Is it possible that two images could match that pattern, and what should the result be if that happens?

 

Upvote 0

P

pnasz

New Member

Joined

Apr 16, 2012

Messages

19

• Apr 18, 2012

• #5

link will look for anyfile related to the name i typed in the cell

the Hyperlink have to "find" a file that matches a pattern (like 02-71234*.*)

 

Upvote 0

Jerry Sullivan

MrExcel MVP

Joined

Mar 18, 2010

Messages

8,787

• Apr 18, 2012

• #6

The solution would need to use VBA to "find" a file matching that pattern then open it.

Still awaiting your reply to these two questions....
What is the file type (file extension) of the file that should be opened when you click the link?

3. Is it possible that two images could match that pattern, and what should the result be if that happens?

 

Upvote 0

P

pnasz

New Member

Joined

Apr 16, 2012

Messages

19

• Apr 18, 2012

• #7

.xlsx and .doc

 

Upvote 0

Jerry Sullivan

MrExcel MVP

Joined

Mar 18, 2010

Messages

8,787

• Apr 19, 2012

• #8

Below is some code that you could try.

Paste this code into the Sheet Code Module of the Sheet where you will enter the Image Codes.
(Right-Click on the Sheet's Tab > View Code... to get to the Sheet Code Module.)

Edit the Path of the Folder to be searched and the Column you'll enter the Image Codes, if other than Column "A"

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Const sPath = "[COLOR="Blue"][B]C:\TEST\[/B][/COLOR]"
    Dim sFilename As String, sCode As String
    
    sCode = Target.Text
    
    If Intersect(Target, Columns("[B][COLOR="blue"]A[/COLOR][/B]")) Is Nothing Or _
        Target.Count > 1 Or _
        sCode = vbNullString Then Exit Sub
        
    On Error GoTo CleanUp
             
    sFilename = Dir(sPath & sCode & "*")
    If sFilename = vbNullString Then
        MsgBox "No matching files found for code " & sCode
    Else
      Application.EnableEvents = False
      Me.Hyperlinks.Add _
            Anchor:=Target, Address:=sPath & sFilename, _
            TextToDisplay:=sFilename
    End If
CleanUp:
    Application.EnableEvents = True
End Sub

 

Upvote 0

P

pnasz

New Member

Joined

Apr 16, 2012

Messages

19

• Apr 21, 2012

• #9

Thanx a lot for your reply.

here we are specifying for the file location that is C:\TEST\

is it possible to search for the file in all the sub directories inside test directory.

 

Upvote 0

Jerry Sullivan

MrExcel MVP

Joined

Mar 18, 2010

Messages

8,787

• Apr 21, 2012

• #10

Did that do what you wanted it to do except for searching sub directories?

 

Upvote 0