Automatically add hyperlinks when typing a serial number

[AliKaffe]

Hello!
I work for a company that sells and services pumps.
I am attempting to make a excel sheet that will give us an overview of the pumps we service and sell.
I would like to make a macro that will automatically add a hyperlink to a file with the same file name on our server when i type the serial number in column L.
So i would like it to search the folder we put our Servicereports in, and add a hyperlink to the file if it finds a text in that column that matches the file name in the folder.
if possible i would also like it to search sub folders.
Is this possible?

if you see the image, i already added some hyper link manually. But would like this to happen automatically when i create a file in the folder with the same name.

 

[AliKaffe]

that fixed it! thank you. Also, if i add a new folder or file to the root folder, it seems i have to exit excel and open the file again to be able to find the file.
is that something wrong on my end? or is there a way to do this automatically too? or does it just take some time?

 

[AliKaffe]

and another question.
i have a "pump card" that i want to do something similar with. (see attached image)
but i want to add reports automatically in the year and month columns. so, B-Q and 16-27.

 

[Legacy 456155]

that fixed it! thank you. Also, if i add a new folder or file to the root folder, it seems i have to exit excel and open the file again to be able to find the file.
is that something wrong on my end? or is there a way to do this automatically too? or does it just take some time?

No, you're not doing anything wrong. The code is designed to fetch a list of files once per session. This is just a basic optimization that stores a list in memory, once, instead of rebuilding the list every time you add a link. There are a handful of approaches to rebuild your list:

1. automatically - every time you add a new link (might be sluggish)
2. on demand - by way of a keyboard shortcut, button, etc.
3. creating a file system watcher - over-done perhaps, but probably the most elegant solution thus far (and, of course, the more complicated)
4. other ways I'm sure

Number 2 is probably a good choice.

_______________________________________________________________________________________________________________________________________________________________________

and another question.
i have a "pump card" that i want to do something similar with. (see attached image)
but i want to add reports automatically in the year and month columns. so, B-Q and 16-27.

Where are you getting your month and year information? I don't see any info in your image that relates to time.

 

[Legacy 456155]

and another question.
i have a "pump card" that i want to do something similar with. (see attached image)
but i want to add reports automatically in the year and month columns. so, B-Q and 16-27.

Where are you getting your month and year information? I don't see any info in your image that relates to time.

 

[AliKaffe]

we usually just make a word file named 2020.08.28, so would be same function as the first code i guess. just for B-Q columns and 16-27 Rows.
Adding a button to update with would be enough.

 

[Legacy 456155]

Ali, I posted two replies. See the second to last.

 

[AliKaffe]

No, you're not doing anything wrong. The code is designed to fetch a list of files once per session. This is just a basic optimization that stores a list in memory, once, instead of rebuilding the list every time you add a link. There are a handful of approaches to rebuild your list:

1. automatically - every time you add a new link (might be sluggish)
2. on demand - by way of a keyboard shortcut, button, etc.
3. creating a file system watcher - over-done perhaps, but probably the most elegant solution thus far (and, of course, the more complicated)
4. other ways I'm sure

Number 2 is probably a good choice.

_______________________________________________________________________________________________________________________________________________________________________



Where are you getting your month and year information? I don't see any info in your image that relates to time.

This one?
i am not quite sure i understand.

Here you can see i have a row with years, and a column with months. I basically just want to add hyperlinks to our report files ( word document) automatically when i type the file name. i have done this manually in this sheet as you can see.

 

[AliKaffe]

can someone help me with the on demand update code? this is the only thing missing now.

 

[Legacy 456155]

Sorry Ali. I missed your reply. Must have deleted the email with my third thumb. The only thing you need to change is this procedure.

Public Sub TryAddLink(Optional ByVal Target As Range = Nothing)
    Static d As Dictionary
    Dim fso As New FileSystemObject
  
    If Target Is Nothing Then
        Set d = Nothing
        Exit Sub
    End If
  
    If d Is Nothing Then
        Set d = New Dictionary
        'change to root.
        GetFileList fso.GetFolder(Parent.Path & "\Root"), d
    End If
  
    If d.Exists(Target.Text) Then Target.Hyperlinks.Add Target, d(Target.Text)
End Sub

SImply call TryAddLink without passing an argument and it will rebuild the file list the next time you make an entry. You may assign this to a button or keyboard shortcut.
I update your example.

 

[Legacy 456155]

I edited my last reply to fix a bug.