Steve,
Try the macro below.
HTH,
Bernie
MS Excel MVP
Sub FindFilesAndHyperlink2ThemV3()
Dim i As Integer
Dim myF As String
With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\Foldername\My Folder"
' .SearchSubFolders = True
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
If .Execute(msoSortOrderDescending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
myF = Mid(.FoundFiles(i), InStrRev(.FoundFiles(i), "\") + 1)
myF = Left(myF, Len(myF) - 4)
ActiveCell.Cells(i, 1).FormulaR1C1 = _
"=HYPERLINK(""" & .FoundFiles(i) & """,""Click to open " & myF &
""")"
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub
Steve_n_KC said:
Thanks Again! I'm especially appreciative of the inclusion of the message
boxes and the " '.SearchSubFolders = True"...answering questions before
they
are asked!!
Could you tell now tell me if it is possible modify the code to still
return
the hyperlinks but only display the file name (preferably without the
extension)??
When I asked my original question I planned on being able to select "Edit
Hyperlink" and assign a vanity name to the link but it isn't a listed
option
for these cells???
--
THANKS!
Steve
Bernie Deitrick said:
And if you just want the hyperlink:
Sub FindFilesAndHyperlink2ThemV2()
Dim i As Integer
With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\Foldername\My Folder"
' .SearchSubFolders = True
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
If .Execute(msoSortOrderDescending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
ActiveCell.Cells(i, 1).FormulaR1C1 = "=HYPERLINK(""" &
.FoundFiles(i) & """)"
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub
--
HTH,
Bernie
MS Excel MVP
Steve,
Change the .LookIn path as needed.... The list will start in the
activecell
Sub FindFilesAndHyperlink2Them()
Dim i As Integer
With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\Foldername\My Foler"
'.SearchSubFolders = True
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
If .Execute(msoSortOrderDescending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
ActiveCell.Cells(i, 1).Value = .FoundFiles(i)
ActiveCell.Cells(i, 2).FormulaR1C1 = "=HYPERLINK(RC[-1])"
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub
HTH,
Bernie
MS Excel MVP
Assuming it's possible, could someone help me with the macro code to
get all
the file names, regardless of type, within a specific folder and then
list
those names including as much as possible of the file path in a column
of the
sheet that I execute the macro from?
If the macro can pull the info in as a hyperlink that would be a bonus
but I
can work that separately if needed.
I need this to be something that I can assign to a button so that the
user
can update the list prior to each use.
