how to link a file through a vba form in excel

Question

I created a form that allows the user to click a button and then it allows you to browse and find your document or whatever and will submit it as a link with other related data. I'm wondering if their is a way to allow the user to make the link connect to a folder filled with multiple files without having them do it manually. Here is my code below

  Private Sub AddPicture_Click()
Dim strFileToLink As String

'link name
lnkNm = InputBox("please enter link description")
Application.ScreenUpdating = False
strFileToLink = Application.GetOpenFilename _
(Title:="Please select an Evidence file to link to")

'Checking if file is selected.
If strFileToLink = "" Then
    'Displaying a message if file not choosen in the above step.
    MsgBox "No file selected.", vbExclamation, "Sorry"
    'And exiting from the procedure.
    Exit Sub
Else
    'print link to sheet as a hyperlink.
    erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ActiveSheet

        If ActiveSheet.Index >= 5 Then
            .Hyperlinks.Add Anchor:=Cells(erow, 12), _
            Address:=strFileToLink, _
            ScreenTip:="Picture Link", _
            TextToDisplay:=lnkNm

        Else
            .Hyperlinks.Add Anchor:=Cells(erow, 13), _
            Address:=strFileToLink, _
            ScreenTip:="Picture Link", _
            TextToDisplay:=lnkNm

        End If
    End With
  End If
End Sub

comments on improving the code would be much appreciated as well. thank you

Answer 1

Instead of Application.GetOpenFileName use Application.FileDialog, which can be customized for Folders or Files, among other things.

https://msdn.microsoft.com/en-us/library/office/ff836226.aspx

Here is how you can use it to get a Folder Name OR a File Name:

Dim fdlg As FileDialog

Dim fdlgType as Long, itm as Variant
fdlgType = Application.InputBox("Enter '3' to choose a FILE, or '4' to choose a FOLDER")
If fdlgType < 3 or fdlgType > 4 Then Exit Sub
Set fdlg = Application.FileDialog(fdlgType)
With fdlg
    .Title = IIf(fdlgType = 3, "Please select an Evidence FILE to link to", _
                               "Please select an Evidence FOLDER to link to")
    .ButtonName = IIf(fdlgType = 3, "Select File", "Select Folder")
    .Show
    For Each itm in .SelectedItems
        MsgBox itm
    Next
End With

There is a .AllowMultiSelect property of the FileDialog which if True would let the user select multiple files (does not work on Folders). Then you can loop over the .SelectedItems.

In your code, then the For Each itm loop would contain the code which adds hyperlinks:

If .SelectedItems.Count = 0 Then
    MsgBox "Nothing selected.", vbExclamation, "Sorry"
    Exit Sub
End If
For Each itm in .SelectedItems
    'print link to sheet as a hyperlink.
    With ActiveSheet
        erow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        If .Index >= 5 Then
            .Hyperlinks.Add Anchor:=.Cells(erow, 12), _
            Address:=itm, _
            ScreenTip:="Picture Link", _
            TextToDisplay:=lnkNm

        Else
            .Hyperlinks.Add Anchor:=.Cells(erow, 13), _
            Address:=itm, _
            ScreenTip:="Picture Link", _
            TextToDisplay:=lnkNm

        End If
    End With
Next

Answer 2

Instead of using: -

Application.GetOpenFilename(Title:="Please select an evidence file to link to")

or even: -

Application.GetOpenFilename(Title:="Please select an evidence file to link to", MultiSelect:=True)

So that they can pick multiple files where you could add each as their own line item if needed.

I would recommend using: -

Application.FileDialog msoFileDialogFolderPicker

For folder selection, and: -

Application.FileDialog msoFileDialogFilePicker

For file selection, as you don't have an error and the question was: -

I'm wondering if their is a way to allow the user to make the link connect to a folder filled with multiple files

The above information should be enough for you to alter your code as required, I would suggest having two buttons on your UserForm, 'Link File(s)' and 'Link Folder'.

If you do get errors while altering your code, feel free to post them (if you can't find an answer) and I'm sure people will help with your specific query.