Dynamic 'username' in external link

Hi,

I use a little VBA code to get the user profile details.

Please Login or Register  to view this content.

I want to make sure that whoever uses the Workbook will have the links updated with their username so they can access data from SharePoint.

I a use the below formula to create an external link to fetch value.

=env("USERPROFILE")&"\SharePoint\Reports\Services\[Stats.xlsx]AIR!'$AW42"

Unfortunately instead of getting the value the cell which contains the formula show the formula itself.

C:\Users\Kramart\SharePoint\Reports\Services\[Stats.xlsx]AIR!'$AW42

How can I make this work? I need the link to have the correct username details plus the link to work.

Thanks,

A.

Thanks. I am afraid that your approach doesn't work. Unless I am doing something wrong.
I've tried:

="="&env("USERPROFILE")&"\SharePoint\Reports\Services\[Stats.xlsx]AIR!'$AW42"
"="&env("USERPROFILE")&"\SharePoint\Reports\Services\[Stats.xlsx]AIR!'$AW42"
=CONCATENATE("="&env("USERPROFILE")&"\SharePoint\Reports\Services\[Stats.xlsx]AIR!'$AW42")

???

Thanks.

The issue is that the workbook will have many links therefore there will be many cell references.

Please Login or Register  to view this content.

how can I deal with it? The line above would have to have dynamic cell reference so it would work on multiply cells.

Without knowing your workbook structure and relationship between your source and destination. Bit hard to help you.

If you have set logic that you can work with... use string manipulation etc to construct formula string.
Ex: if values are in same cell in source and destination. You can just use the .Address property of target cell to construct formula.
Or if you need offset it by certain number of rows/columns you can use .OFFSET(x, y).Address