Unfortunately we cannot change the rules of HTML. You might come up with a
good argument for changing the file naming convention if you could show
enough time saved through having the hyperlinks. Sorry we couldn't find a
work around.

"C_Ascheman" wrote:

Well thanks for your help JLatham. Unfortunately the boss wants everything a
certain way so not much of a choice in the matter. I will just have to format
how everything is saved to, and read from. The way it is now it saves the
file name as the Customer name + the legal code + date + time to help keep it
unique perse. Unfortunately the salesman, and my boss are set in their ways
so no real way to get them to change using # to No. Thanks again for the
responses.

C_Ascheman

"JLatham" wrote:

The # symbol has special meaning in hyperlinks - it marks a 'bookmark' or
location within a web page itself. The only fix I know is to rename your
files - use something like "C & R No3" instead of "C & R #3". I recommend
NEVER using 'special' characters in folder or filenames. Spaces are even a
pain at times because spaces break (hyper)links. I personally recommend one
of two ways to name files and retain readability:
NameFilesWithCapsLikeThis.xls
or
Use_Underscore_Instead_of_a_Space.xls
or a combination of the two.

Since you're using a hyperlink in the cell, it may be difficult to trap for
the error. There is a Worksheet_FollowHyperlink() event that might be of
use, but I'm not certain of how it works or at what point in the following a
hyperlink it takes place. I'd have to experiment with it to determine if it
is even a possible way to trap for the error. That's one I've never used in
the past.

"C_Ascheman" wrote:

Got it to work for the most part. Here is the problem I am having, and I have
tried to search the web to understand why its happening. I have a workbook
that has a # symbol in the name (i.e. C & R #3). If I click on this link it
will not open up the workbook. If I select the record, and click the Open
button (which we are trying to remove with the hyperlink setup) it opens
fine. Why is the # symbol preventing the workbook from being opened up as a
hyperlink? Is there a way around this? Also it gives me an excel error
"Cannot open the specified file". Is there a way to detect this error before
it pops up, and reroute to an open function to open the record?

C_Ascheman



"JLatham" wrote:

Just change that last 'populate' loop content with this:

'Populate Column A with list of Workbooks
For i = 1 To n
ActiveSheet.Cells(i, 1).Formula = _
"=HYPERLINK(" & Chr$(34) & "file///" _
& Formatting & filearray(i) & Chr$(34) _
& ", " & Chr$(34) & filearray(i) & Chr$(34) & ")"
Next

Note that this is only going to work on the local system - because the Drive
letter and path are specific to it. Carry the workbook over to someone
else's system and it will probably fail unless you get real lucky with folder
structure and contents.

If others need to get to these files and you want to send them an Excel
workbook containing links that will work, then you need to refer to the path
as it is seen in My Network Places rather than as seen in My Documents.
Change 'Formatting' to that value, and of course, the folder will need to be
shared.

"C_Ascheman" wrote:

Here is the code I have in my program.

Private Sub cmdFind_Click()
Dim filearray()
Dim i%, n%
Dim Formatting$, Extension$, dName$
Formatting = ("f:\excel\")
Extension = ("*.xls")
ChDrive Left(Formatting, 1)
ChDir Formatting
dName = Dir(Extension)
n = 0
i = 0
'Fill Array with names of Workbooks
Do While dName < ""
n = n + 1
ReDim Preserve filearray(1 To n)
filearray(n) = dName
dName = Dir()
Loop
'Populate Column A with list of Workbooks
For i = 1 To n
ActiveSheet.Cells(i, 1) = filearray(i)
Next
End Sub

How can I make it so that as the array is populating Column A in my
worksheet with the names of various workbooks contained in F:\excel\ that it
populates them as hyperlinks to the workbooks?

C_Ascheman