Power Query - Hyperlink to file

[DaveyD]

I have a power query setup that imports tables from all excel files in a folder.
Is it possible to have a column that contains a link to the original excel file?

[I have this working in a regular excel table using the hyperlink function (which uses the customer name to get to the correct file). Now I want to convert that table to a power query table which makes automation much easier!]

Is it possible to get this automated with power query in any way?

Thanks,
David

 

[sandy666]

PowerQuery doesn't support links - result will be text always

 

[DaveyD]

Ok, thank you
Is there any workaround?

In any case, a "No" is better than more hours wasted on trying...

 

[sandy666]

if the result of Query will be loaded into the sheet - it's possible but remeber that if you refresh your result (query)-table you will lost hyperlink.

you can use: =HYPERLINK("link_location")
1. add prefix: =HYPERLINK("
2. add suffix: ")
3. load to the sheet
4. replace = with = (yes this is the same equal sign)
and you'll get hyperlink but as I said above, each refresh remove hyperlink and you need repeat replace = to =

 

[sandy666]

I forgot: link_location: full path\filename

 

[DaveyD]

Thanks - this worked for me.
I created a button that runs a function to refresh the query and replace the = sign
Works perfectly!
Thanks

 

[sandy666]

You are welcome

Have a nice day

 

[UniqueName3]

Thanks - this worked for me.
I created a button that runs a function to refresh the query and replace the = sign
Works perfectly!
Thanks

Hi Davey - hopefully you're still around, thank you for asking this question it has helped me immensely. I wanted to ask what code you used for your button though, I can get the refresh and replace functions to work separately but when I put them together it "undoes" itself. I see the refresh work, then the replace "=" turns them into hyperlinks, and then they revert. If I split the macros and run them manually in order it works perfectly, but if I combine (or use call) it breaks. I'm a very basic user and I've Googled myself to pieces with no luck.

 

[DaveyD]

Sure. Here is the code that I used:

Sub balanceSheet_Refresh()
    Dim tbl As ListObject
    Set tbl = worksheets("BS").ListObjects(1)
    tbl.QueryTable.Refresh BackgroundQuery:=False
    With tbl.ListColumns("File").DataBodyRange
        .Replace What:="=", Replacement:="=", _
                 LookAt:=xlPart, SearchOrder:=xlByRows
    End With
End Sub

 

[UniqueName3]

Sure. Here is the code that I used:

Sub balanceSheet_Refresh()
    Dim tbl As ListObject
    Set tbl = worksheets("BS").ListObjects(1)
    tbl.QueryTable.Refresh BackgroundQuery:=False
    With tbl.ListColumns("File").DataBodyRange
        .Replace What:="=", Replacement:="=", _
                 LookAt:=xlPart, SearchOrder:=xlByRows
    End With
End Sub

Thank you so much! Can I just check, "BS" is the name of your worksheet, what is "File"?