How to extract full path from the excel hyperlink

Question

I have a worksheet where i have maintained a column which contains the hyperlink to other worksheets.

Set rngx = Worksheets("Sheet2").Range("C1:C10000").Find("Power Plant 1", lookat:=xlPart)

BR2 = rngx.Hyperlinks(1).Address

Here in BR2 previously i used to get the particular hyperlink path as "Projects\Power Plant\Power Plant EPC 1.xlsm"

Later i went into info->properties and changed the Hyperlink base to C:\ , After mentioning the hyperlink base now i am getting the hyperlink path as "Users\inkapb\AppData\Local\Temp\EPC AutoTool\Projects\Power Plant\Power Plant EPC 1.xlsm"

My question is I need the path from C: drive like "C:\Users\inkapb\AppData\Local\Temp\EPC AutoTool\Projects\Power Plant\Power Plant EPC 1.xlsm" is it possible to achieve this without appending C:\ in the code.

Answer 1

Set the hyperlink address using the %LOCALAPPDATA% environment variable.

'as a formula
=HYPERLINK("%LOCALAPPDATA%\Temp\EPC AutoTool\Projects\Power Plant\Power Plant EPC 1.xlsm", "Power Plant 1")

'as VBA
rngx.Hyperlinks(1).Address = Environ("LOCALAPPDATA") & _
  "\Temp\EPC AutoTool\Projects\Power Plant\Power Plant EPC 1.xlsm"
'or,
rngx.Hyperlinks(1).Address = "%LOCALAPPDATA%\Temp\EPC AutoTool\Projects\Power Plant\Power Plant EPC 1.xlsm"

Retrieving the .Address from the Hyperlinks object or the HYPERLINK function should retrieve the correct full path and filename.

BR2 = rngx.Hyperlinks(1).Address

Answer 2

I got the Full path. Instead of hyperlink base as "C:\" i changed it to "x", it worked like a charm.