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..
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..