VBA code to create a hyperlink from files in a folder and assign to a cell with a similar name

[dacast]

I have the following code that creates hyperlinks from all pdfs contained in a folder and listed in column N.

Sub extractfiles_hyperlink()
Dim xFSO As Object
Dim xFolder As Object
Dim xFile As Object
Dim xFiDialog As FileDialog
Dim xPath As String
Dim i As Integer
Set xFiDialog = Application.FileDialog(msoFileDialogFolderPicker)
If xFiDialog.Show = -1 Then
xPath = xFiDialog.SelectedItems(1)
End If
Set xFiDialog = Nothing
If xPath = "" Then Exit Sub
Set xFSO = CreateObject("Scripting.FileSystemObject")
Set xFolder = xFSO.GetFolder(xPath)
For Each xFile In xFolder.Files
i = i + 1
ActiveSheet.Hyperlinks.Add Cells(i + 5, 14), xFile.Path, , , xFile.Name
Next
End Sub

Problem:
I want either sort those hyperlinks and match their name with column A ( as you can see from the image, they don't match) or create a hyperlink directly to column A and, if the file is not in the folder, print a message saying " missing - send a reminder to add to folder "(planning to create code to send an automatic email to the responsible person but for now just this :D )

Thank you in advance for your help on my post

 

[Domenic]

Instead of using the FileSystemObject, I would suggest you use the Dir function where you can use wildcards to filter for a filename.

Also, I would suggest declaring your variable i as Long.

So, for example, your macro can be re-written as follows...

Option Explicit

Sub extractfiles_hyperlink()

    Dim xFSO As Object
    Dim xFolder As Object
    Dim xFile As Object
    Dim xFiDialog As FileDialog
    Dim xPath As String
    Dim xFilename As String
    Dim i As Long
    Dim lastRow As Long
    
    Set xFiDialog = Application.FileDialog(msoFileDialogFolderPicker)
    
    With xFiDialog
        .InitialFileName = Application.DefaultFilePath & "\" 'change as desired
        .Title = "Select a folder"
        If .Show = 0 Then Exit Sub
        xPath = .SelectedItems(1) & "\"
    End With
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = 6 To lastRow
        xFilename = Dir(xPath & Cells(i, "A").Value & " *.pdf", vbNormal)
        If Len(xFilename) > 0 Then
            ActiveSheet.Hyperlinks.Add Cells(i, 14), xPath & xFilename, , , xFilename
        Else
            Cells(i, 14).Value = "missing - send a reminder to add to folder"
        End If
    Next i
    
End Sub

Hope this helps!

 

[dacast]

i run th ecode and this is the following result. I'm trying to figure out why is not reading the names from the older and creating the hyperlink. it is showing all of them are missing

 

[dacast]

i run th ecode and this is the following result. I'm trying to figure out why is not reading the names from the older and creating the hyperlink. it is showing all of them are missing


View attachment 86296

i want to add that folders names are as follow, so the code should read the first 6 characters or before the "_". thank you for your help ! :D

 

[Domenic]

When filtering for the filename, it's looking for a space before the .pdf, as per your original post. Try the following instead...

xFilename = Dir(xPath & Cells(i, "A").Value & "*.pdf", vbNormal)

Hope this helps!

 

[dacast]

When filtering for the filename, it's looking for a space before the .pdf, as per your original post. Try the following instead...

xFilename = Dir(xPath & Cells(i, "A").Value & "*.pdf", vbNormal)

Hope this helps!

Amazing, thank you very much is working now!!
Would I like to learn more about VBA language and logic any recomentadataion? I'm new to the topic.

 

[Domenic]

Amazing, thank you very much is working now!!

You're very welcome, glad I could help.

Would I like to learn more about VBA language and logic any recomentadataion? I'm new to the topic.

A good way to learn is to look at the solutions offered here on the Board (and other forums like this one), and then try to answer questions yourself. Then, if you want to take it further, I would suggest a book called Microsoft Office Excel 20XX Power Programming with VBA.

 

[dacast]

Adding to this threat, i want to fill different columns by clicking a button on top of the column, how can incorporate o the code that if I click bottom 1 will fill column C but if I click button 3 willfill that column (please see image)

I don't see efficient coping the code 12 times and changing the column location. is there a better way?

 

[Domenic]

Are those buttons form controls or ActiveX controls?

 

[dacast]

Controls.

BTW what is the Difference i just add the first one I see