Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

select specific column and specific files in folders and subfolders

0

hello

I  have   a code     selects  specific  cell and  specific  file  and  hyperlink and  open,  but  I  have  many   files   and  this  way   takes  much time  from  me  so  I  search  way   to  able  select  multiple  cells in specific  column  and  when  open  the  browser  and  open  my  folder  which  contains many  folders  and  each  folder  contains subfolder  and  each subfolder contains many  files , then it  should  get all  the  files  in  what  I  select  the  cells and create  hyperlink and  open .

Sub CreateHyperLink()
    Dim fd As Object, fPath As String, fName As String, cel As Range
    Set cel = Application.InputBox("Select a cell", "Add Link to File", , , , , , 8)
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .InitialFileName = Environ("USERPROFILE") & "\"     'default folder path - MUST  end with[COLOR=#ff0000] [B]\[/B][/COLOR]
        .AllowMultiSelect = False
        .Title = "Please select a file."
        .Filters.Clear
        If .Show = True Then
            fPath = fd.SelectedItems(1)
            fName = Mid(fPath, InStrRev(fPath, "") + 1, 9999)
        Else
            Exit Sub
        End If
    End With
    cel.Hyperlinks.Add Anchor:=cel, Address:=fPath, TextToDisplay:=fName
End Sub
.

any  idea  to  make  it  dyanamically,please?

Answer
Discuss

Answers

0
Selected Answer

Halk

You can make the file picker allows mltiple selections by changing the filepicker's  AllowMultiSelect property from False to True (in bold below). You can then loop through however many files were selected (with Shift+Cclick or Ctrl+Click)

If you run this code (changes in bold), it will add hyperlinks to cells BELOW the selected cell:

Sub CreateHyperLink()
    Dim fd As Object, fPath As String, fName As String, cel As Range, SelItem As Variant
    Set cel = Application.InputBox("Select a start cell", "Add Links to Files", , , , , , 8)
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .InitialFileName = Environ("USERPROFILE") & "\"    
        .AllowMultiSelect = True
        .Title = "Please select one or more files."
        .Filters.Clear
        If .Show = True Then
            For Each SelItem In .SelectedItems
                fPath = SelItem
                fName = Mid(fPath, InStrRev(fPath, "") + 1, 9999)
                cel.Offset(n, 0).Hyperlinks.Add Anchor:=cel.Offset(n, 0), Address:=fPath, TextToDisplay:=fName
                n = n + 1
            Next
        Else
            Exit Sub
        End If

    End With

End Sub
Hope this helps.
Discuss

Discussion

John
this   is  what   I'm looking  after .
thanks  so  much .
Halk (rep: 30) Jul 8, '21 at 4:15 pm
Halk
Thanks for selecting my answer. I just revised it slightly (to correct the English in the requestors) 
John_Ru (rep: 6152) Jul 8, '21 at 6:20 pm
Halk.

I forgot to say...

Please change the title of your question to something more helpful to other users. I'd suggest "Add cell hyperlinks to multiple files selected using file picker".

Thanks in advance. 
John_Ru (rep: 6152) Jul 10, '21 at 1:16 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login