PDA

View Full Version : [SOLVED] Help to get one or more filename from folder in excel and make it hyperlink to it



Jeamy
10-16-2017, 09:45 PM
Hi..

I needed some help..i just wanna copied just filenames (one or more) that i've selected from open file dialog to the cell that i wanted as a list..then adding a hyperlink to that filename so i can get to just open the folder of that file (not opening the file itself just the folder place of that file) while i click it later.

I've already found some code that are closely what i needed..but

Sub Demo()
Dim lngCount As Long
Dim cl As Range

Set cl = ActiveCell
' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show
' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
' Add Hyperlinks
cl.Worksheet.Hyperlinks.Add _
Anchor:=cl, Address:=.SelectedItems(lngCount), _
TextToDisplay:=.SelectedItems(lngCount)
' Add file name
'cl.Offset(0, 1) = _
' Mid(.SelectedItems(lngCount), InStrRev(.SelectedItems(lngCount), "\") + 1)
' Add file as formula
cl.Offset(0, 1).FormulaR1C1 = _
"=TRIM(RIGHT(SUBSTITUTE(RC[-1],""\"",REPT("" "",99)),99))"


Set cl = cl.Offset(1, 0)
Next lngCount
End With
End Sub







In this code it's right copied file name by choosing the file that i've selected but unfortunately this code copied including the path itself (that i dont need it) and hyperlink is not in filename but in the path itself,
how i can change the code above to get what i wanted or maybe new code..?

Please i hope someone here can help me..i needed it for saving filenames as archive..

Thanks before..

mancubus
10-17-2017, 05:24 AM
welcome to the forum.

files in Col A
folders in Col B
starts at Row 1



Sub vbax_61036_list_n_hyperlink_selected_files()

Dim i As Long
With Application.FileDialog(msoFileDialogFilePicker)
.InitialFileName = "P:\IC_KONTROL_MERKEZI\17_Analiz\17_PROJE_2SK\iky_dosya_2017q4\"
.AllowMultiSelect = True
If .Show = 0 Then
MsgBox "Cancelled by user! Quitting the macro..."
Exit Sub
Else
For i = 1 To .SelectedItems.Count
ActiveSheet.Range("A" & i).Hyperlinks.Add ActiveSheet.Range("A" & i), Right(.SelectedItems(i), Len(.SelectedItems(i)) - InStrRev(.SelectedItems(i), "\"))
ActiveSheet.Range("B" & i).Hyperlinks.Add ActiveSheet.Range("B" & i), Left(.SelectedItems(i), InStrRev(.SelectedItems(i), "\")), , Right(.SelectedItems(i), Len(.SelectedItems(i)) - InStrRev(.SelectedItems(i), "\"))
Next
End If
End With

End Sub

Jeamy
10-17-2017, 08:04 AM
welcome to the forum.

files in Col A
folders in Col B
starts at Row 1



Sub vbax_61036_list_n_hyperlink_selected_files()

Dim i As Long
With Application.FileDialog(msoFileDialogFilePicker)
.InitialFileName = "P:\IC_KONTROL_MERKEZI\17_Analiz\17_PROJE_2SK\iky_dosya_2017q4\"
.AllowMultiSelect = True
If .Show = 0 Then
MsgBox "Cancelled by user! Quitting the macro..."
Exit Sub
Else
For i = 1 To .SelectedItems.Count
ActiveSheet.Range("A" & i).Hyperlinks.Add ActiveSheet.Range("A" & i), Right(.SelectedItems(i), Len(.SelectedItems(i)) - InStrRev(.SelectedItems(i), "\"))
ActiveSheet.Range("B" & i).Hyperlinks.Add ActiveSheet.Range("B" & i), Left(.SelectedItems(i), InStrRev(.SelectedItems(i), "\")), , Right(.SelectedItems(i), Len(.SelectedItems(i)) - InStrRev(.SelectedItems(i), "\"))
Next
End If
End With

End Sub


Hi..
Thanks for your reply..

EDITED!

Already got what i want..thanks for your help :friends:

mancubus
10-17-2017, 01:42 PM
thanks for the feedback and marking the thread as solved.

pls don't quote previous messages as a whole. where necessary, only quote the related bit of the the message that you want more explanation.

Jeamy
10-17-2017, 10:24 PM
oO..ok..got it..sorry..i'm new here...
thanks once again..:friends: