hyperlink to files
 

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

Steve

hyperlink to files
 

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


"Steve_n_KC" wrote in message
...
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.
--
THANKS!

Steve

hyperlink to files
 

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


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Steve_n_KC" wrote in message
...
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.
--
THANKS!

Steve

hyperlink to files
 

PERFECT! Thanks So Much!
--
THANKS!

Steve


"Bernie Deitrick" wrote:

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


"Steve_n_KC" wrote in message
...
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.
--
THANKS!

Steve

hyperlink to files
 

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" wrote:

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


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Steve_n_KC" wrote in message
...
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.
--
THANKS!

Steve

hyperlink to files
 

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" wrote in message
...
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" wrote:

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


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Steve_n_KC" wrote in message
...
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.
--
THANKS!

Steve

hyperlink to files
 

You may get tired of hearing it but...Works PERFECT! 3 for 3!!!
--
THANKS!

Steve


"Bernie Deitrick" wrote:

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" wrote in message
...
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" wrote:

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


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Steve_n_KC" wrote in message
...
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.
--
THANKS!

Steve