How to create hyperlink to file in a report?

[richiewillmott]

Hi,



I have a report that also lists the file location, what I want to to is when you click the hyperlink it opens up the excel file. All the file locations will be different in each line, I just can't figure out how to link to them, any help would be much appreciated!

[CJ_London]

in your example all the file locations look the same to me but in principle, use a hyperlink control - should work in report view

should happen automatically when you create the report if your table stores the location as a hyperlink rather than text

[richiewillmott]

Thanks for your reply, really sorry but how would you do "hyperlink control"? In properties of the text box I have "is hyperlink" if I set this to yes it formats it to blue with underline and is clickable but doesn't do anything?

[CJ_London]

what view are you using? - needs to be report view, not preview

I just created a report based on a table that contains a hyperlink field and it worked fine.

Alternatively, insert a hyperlink control from the ribbon and set the controlsource to the name of your path. If it works, delete the original control

I'm using 2010, it may be in 2016 you have to do something like right click

[richiewillmott]

I think I know why, my "datasource table" is an realtime excel file rather than a local table, it's pulling in the data as text from it and it wont let me change datatype to hyperlink in the design view of the table. I can't figure out how to format the data in the excel file as a hyperlink instead of text.

[CJ_London]

pretty sure that will be it, in excel you select a cell then go to insert>hyperlink. Other than automation, I don't think you can select a range and convert them all to hyperlinks with individual addresses. Just had a go and it converts the entire column into one hyperlink.

suggest import the data to either a permanent or temporary table as required

[CJ_London]

other thought - using vba code you can use followhyperlink - see this link

https://docs.microsoft.com/en-us/off...ollowHyperlink

[richiewillmott]

Ok, I think having it as a proper access table will work. The only problem is it possible to convert the linked table to a permanent table automatically every time access is opened to make sure the data is up to date? And then format the data to hyperlink automatically as well.

[CJ_London]

it's possible - either reimport the data in full or use a query to just update changes. Really depends on how much data you are talking about, whether there are deletions and changes as well as additions

did you see my second post about followhyperlink?