Power Query - Hyperlink to file

DaveyD

New Member
Joined
May 20, 2015
Messages
31
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Ok, thank you
Is there any workaround?

In any case, a "No" is better than more hours wasted on trying... ;)
 
Upvote 0
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 =
 
Last edited:
Upvote 0
Thanks - this worked for me.
I created a button that runs a function to refresh the query and replace the = sign
Works perfectly!
Thanks
 
Upvote 0
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.
 
Upvote 0
Sure. Here is the code that I used:
VBA Code:
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
 
Upvote 0
Sure. Here is the code that I used:
VBA Code:
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"?
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,700
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top