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]

Controls.

Yes, but which type of control? Form control or ActiveX control?

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

ActiveX controls have much more available properties. And, they also have event handlers that can be triggered when some action takes place. However, in your case, it looks like buttons from Form Controls would suffice.

 

[dacast]

Yes, but which type of control? Form control or ActiveX control?


ActiveX controls have much more available properties. And, they also have event handlers that can be triggered when some action takes place. However, in your case, it looks like buttons from Form Controls would suffice.

Form Control

 

[Domenic]

First, right-click the sheet tab that contains your names, select View Code, and copy/paste the following code into that sheet module...

Option Explicit

 Sub create_hyperlinks()
    
    Dim xFileDialog As FileDialog
    Dim xPath As String
    Dim columnIndex As Long
    Dim errorMessage As String

    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    
    With xFileDialog
        .InitialFileName = Application.DefaultFilePath & "\" 'change as desired
        .Title = "Select a folder"
        If .Show = 0 Then Exit Sub
        xPath = .SelectedItems(1) & "\"
    End With
    
    columnIndex = Me.Shapes(Application.Caller).TopLeftCell.Column
    
    errorMessage = ""
    
    If Not extractfiles_hyperlink(xPath, columnIndex, errorMessage) Then
        MsgBox errorMessage, vbCritical, "Error"
    End If
    
End Sub

Private Function extractfiles_hyperlink(ByVal xPath As String, ByVal columnIndex As Long, ByRef errorMessage As String) As Boolean

    Dim xFilename As String
    Dim i As Long
    Dim lastRow As Long
    
    On Error GoTo errorHandler
    
    If Right(xPath, 1) <> "\" Then
        xPath = xPath & "\"
    End If
    
    Range(Cells(6, columnIndex), Cells(Rows.Count, columnIndex).End(xlUp)).ClearContents
    
    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
            Me.Hyperlinks.Add Cells(i, columnIndex), xPath & xFilename, , , xFilename
        Else
            Cells(i, columnIndex).Value = "missing - send a reminder to add to folder"
        End If
    Next i
    
    extractfiles_hyperlink = True
    
    Exit Function
    
errorHandler:
    errorMessage = "Error " & Err.Number & ":" & vbCrLf & vbCrLf & Err.Description
    
End Function

Then, for each button on your sheet, assign the macro called create_hyperlinks(). So, for example, for your first button, right-click the button, select Assign Macro, and then enter 'Book1.xlsm'!Sheet1.create_hyperlinks for the macro name. Change the name of the workbook and codename (not sheet name) for your sheet accordingly. Then click on OK. And the same thing for your other buttons.

Note that the column position for any button is automatically determined by it's position. So if you should move the button to another column, the column position will automatically adjust. The column position is determined by the column that lies under the top left corner of the button.

Hope this helps!

 

[Domenic]

Have you managed to get this to work? Or are you having problems?

If you've decided to go a different route, that's fine.

Cheers!

 

[dacast]

Have you managed to get this to work? Or are you having problems?

If you've decided to go a different route, that's fine.

Cheers!

i tried once and didn't work for me , I feel I don't understand your instructions.. I will give it another try and keep you posted. thank so much for your help

 

[Domenic]

Okay, no problem, I'll try to take it step-by-step.

Right-click the sheet tab for the sheet containing your data and buttons, and then select View Code. This will automatically open the code module for your sheet in which to copy and paste the code.

Copy and paste the code into the code module.

Right-click your button FILL JAN, and select Assign Macro. This will display the Assign Macro dialog box.

Enter the macro name, and then click on OK. Change the workbook name and sheet code name accordingly (make sure that when you specify the sheet that you use the code name of the sheet, not the sheet name, see image below).

Then assign the same macro to your other buttons in the same manner.

Does this help?

 

[dacast]

Okay, no problem, I'll try to take it step-by-step.

View attachment 87657

Right-click the sheet tab for the sheet containing your data and buttons, and then select View Code. This will automatically open the code module for your sheet in which to copy and paste the code.

View attachment 87658

Copy and paste the code into the code module.

View attachment 87659

Right-click your button FILL JAN, and select Assign Macro. This will display the Assign Macro dialog box.

View attachment 87661

Enter the macro name, and then click on OK. Change the workbook name and sheet code name accordingly (make sure that when you specify the sheet that you use the code name of the sheet, not the sheet name, see image below).

View attachment 87679

Then assign the same macro to your other buttons in the same manner.

Does this help?

When you say change name and sheet code name accordingly. What exactly should I change to or be aware of?

I'm getting the following message.

 

[dacast]

When you say change name and sheet code name accordingly. What exactly should I change to or be aware of?

I'm getting the following message.


View attachment 87893View attachment 87893

The code works perfectly I change a letter by mistake and the whole thing didn't work. Sorry for that

thank you very much for your help !!!

 

[Domenic]

That's great, I'm glad you were able to sort it out.

And thanks for the feedback!

Cheers!