I use the code below to pull in a list of all files in the same folder as the
file that contains the macro and then give a hyperlink to each of those
files. It works like a TOP as long as there isn't a Number symbol "#" in the
file name??? Then the file is still listed and formatted as a link but the
hyperlink doesn't work.

I thought maybe it was because it wasn't a letter or number but that is the
only character I have found that won't work.

Could anyone possibly help me with a fix or at least an explanation of why
only that one doesnt work?


Dim i As Integer
Dim myF As String

With Application.FileSearch
..NewSearch
..LookIn = Range("b2").Value
..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) &
""",""" & myF & """)"
Next i
Else
MsgBox "There were no files found."
End If
--
THANKS!

Steve

VBA treats the pund sign as a wilcard for a numeric value. I'll bet
that is what is causing the problem. It probably won't work with
question marks and asterisks either.
Steve_n_KC wrote:
I use the code below to pull in a list of all files in the same folder as the
file that contains the macro and then give a hyperlink to each of those
files. It works like a TOP as long as there isn't a Number symbol "#" in the
file name??? Then the file is still listed and formatted as a link but the
hyperlink doesn't work.

I thought maybe it was because it wasn't a letter or number but that is the
only character I have found that won't work.

Could anyone possibly help me with a fix or at least an explanation of why
only that one doesnt work?


Dim i As Integer
Dim myF As String

With Application.FileSearch
.NewSearch
.LookIn = Range("b2").Value
.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) &
""",""" & myF & """)"
Next i
Else
MsgBox "There were no files found."
End If
--
THANKS!

Steve

Thats basically what I thought was happening but I was hoping that there
might be a work around for the # since it is actually allowed in the file
names. * and ? are never an issue because none of the applications that we
use will allow them to be used in a file name.


--
THANKS!

Steve


"JW" wrote:

VBA treats the pund sign as a wilcard for a numeric value. I'll bet
that is what is causing the problem. It probably won't work with
question marks and asterisks either.
Steve_n_KC wrote:
I use the code below to pull in a list of all files in the same folder as the
file that contains the macro and then give a hyperlink to each of those
files. It works like a TOP as long as there isn't a Number symbol "#" in the
file name??? Then the file is still listed and formatted as a link but the
hyperlink doesn't work.

I thought maybe it was because it wasn't a letter or number but that is the
only character I have found that won't work.

Could anyone possibly help me with a fix or at least an explanation of why
only that one doesnt work?


Dim i As Integer
Dim myF As String

With Application.FileSearch
.NewSearch
.LookIn = Range("b2").Value
.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) &
""",""" & myF & """)"
Next i
Else
MsgBox "There were no files found."
End If
--
THANKS!

Steve