Hyperlink in cell to path/file name

[MikeF]

Need to put a hyperlink in a cell, say ax2, with the current drive
letter\path\filename and nothing else.

The hyperlink will be imported into an Access table, and needs to be what
will get me back to this Excel file.

Have tried lots of INFO and CELL formulas, they all *look* like they work,
but don't in Access.
The only thing that works is manually copying the path from the locator bar,
and that is, well, no fun!

Regards,
- Mike

 

[Barb Reinhardt]

Did you try Cell("filename",A1) or something like that? You may need to
strip out the [ and ] and the the sheet name from this though.

 

[MikeF]

Barb,
I did.
Used:
=HYPERLINK(INFO("directory")&MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1))

Give it a try.
Like I said it *looks* like it works.
But when it goes into an Access table, field defined as hyperlink, it does
nothing.
.... Strange.

- Mike

Did you try Cell("filename",A1) or something like that? You may need to
strip out the [ and ] and the the sheet name from this though.

Need to put a hyperlink in a cell, say ax2, with the current drive
letter\path\filename and nothing else.

The hyperlink will be imported into an Access table, and needs to be what
will get me back to this Excel file.

Have tried lots of INFO and CELL formulas, they all *look* like they work,
but don't in Access.
The only thing that works is manually copying the path from the locator bar,
and that is, well, no fun!

Regards,
- Mike

 

[Ivan]

I think the problem is that link from excel cell is by default transfered to
text type in access and is not the access hyperlink type.

Hyperlink fields in Access (97 and later) are actually just modified text
fields that follow a special format. This format consists of three parts
seperated by # symbols. These three parts are the Display Text, the Address,
and the Sub Address. The Display Text is what you see when you look at the
data in an Access form. The Address is the path to the target document. The
Sub Address is the location within the file or page. This format, and the
fact that the Display Text and Sub Address are optional, is why most data
retreived from these fields simply looks like the URL surrounded by #
symbols.
So if you want to have the link to file C:\test.xls in access, you must have
#C:\test.xls# in the field of access table. Because you are importing the
excel file into access table you should put such #...# format first in excel
and also you should choose hyperlink as the access field type.

Ivan

 

[Chip Pearson]

The following formula will return the complete file name (Drive, Path,
File) of the file in which it appear.

=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[","")

Leave "filename" as is. Don't change it to some actual file name. The
file must have been saved to disc at least once for this to work.

This result is just a string, which can be imported to an Access
column. I don't know the details of what Access does with a field
whose type is Hyperlink. What exactly does "don't [work] in Access"
actually mean?

Once you have the file name string, you might want to go down the hall
to the Access newsgroups for more info on what Access does with
columns declared as Hyperlink.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)