Assigning Hyperlink to Cell based up Environment Variable

[g_speran]

I am trying to assign a hyperlink function to a cell, as follow, using VBA code
=HYPERLINK(env("OneDriveCommercial") & "\..\DPS\Engagements\Mary\PR-00000", PR-00000)

The Windows Environment Variable is OneDriveCommercial
- OneDriveCommercial=C:\Data\Collection\OneDrive\Active

I have a defined function called env

My VBA code works to a Point but not completely. When assigning the hyperlink function to the cell value, I am getting atsigns (@) which is not expected
=HYPERLINK(@env("OneDriveCommercial") &@ PathAdd,@ TargetValue)

For all intended purposes, the excel spreadsheet would have
cell(r,2)="Mary"
cell (r,5)="PR-00000"

Goal: Execute Code from any cell in the row and For Cell (R,5) to display the Hyperlink Friendly Name but contain the Hyperlink formula/function

The VBA code is below. Any assistance is appreciated in rectifying my cell value

Sub MakeFolders_Hyperlink2()
        Dim Rng As Range
        Dim TargetDir, TargetPath, ENVPath, PathAdd As String
        Dim TargetValue As String
        Dim iStart, answer  As Integer
        Dim aDirs           As Variant
        Dim sCurDir         As String
        Dim i, r            As Integer
        Dim fld, myFile     As Object
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        Set Rng = ActiveCell
        Set fld = CreateObject("Scripting.FileSystemObject")
        r = Rng.Row
        TargetValue = Cells(r, 5)
        TargetColor = Cells(r, 5).Font.Color
        ENVPath = (env("OneDriveCommercial"))
        PathAdd = "\..\DPS\Engagements\" & Cells(r, 2) & "\" & TargetValue
        TargetPath = ENVPath & PathAdd
        
            If Len(Dir(TargetPath, vbDirectory)) = 0 Then
               If TargetPath <> "" Then
                   aDirs = Split(TargetPath, "\")
                   If Left(TargetPath, 2) = "\\" Then
                       iStart = 3
                   Else
                       iStart = 1
                   End If
            
                   sCurDir = Left(TargetPath, InStr(iStart, TargetPath, "\"))
            
                   For i = iStart To UBound(aDirs)
                       sCurDir = sCurDir & aDirs(i) & "\"
                       If Dir(sCurDir, vbDirectory) = vbNullString Then
                           MkDir sCurDir
                       End If
                   Next i
                   ActiveSheet.Cells(r, 5).Formula = "=HYPERLINK(env(""OneDriveCommercial"") & PathAdd, TargetValue)"
                        ' ==> attempts to execute env function and do not want it to <==
                        ' ==> want cell (r,5) to be =HYPERLINK(env("OneDriveCommercial") & "\..\DPS\Engagements\Mary\PR-00000", PR-00000)
                   Cells(r, 5).Font.Color = TargetColor
                   Set myFile = fld.CreateTextFile(TargetPath & "\Notes.txt", False)
               End If
            Else
                answer = MsgBox("Directory " & TargetPath & " Already exists", vbQuestion + vbYesNo + vbDefaultButton2, "Create Hyperlink")
                If answer = vbYes Then
                    ActiveSheet.Cells(r, 5).Formula = "=HYPERLINK(env(""OneDriveCommercial"") & PathAdd, TargetValue)"
                        ' ==> attempts to execute env function and do not want it to
                        ' ==> want cell (r,5) to be =HYPERLINK(env("OneDriveCommercial") & "\..\DPS\Engagements\Mary\PR-00000", PR-00000)
                    Cells(r, 5).Font.Color = TargetColor
                End If
            End If
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
End Sub

    Function env(vn As String) As String
      env = Environ(vn)
    End Function

 

[GWteB]

How about this as a replacement for your current line ...

ActiveSheet.Cells(r, 5).Formula = "=HYPERLINK(env(""OneDriveCommercial"")&""" & PathAdd & """,""" & TargetValue & """)"

 

[g_speran]

How about this as a replacement for your current line ...

ActiveSheet.Cells(r, 5).Formula = "=HYPERLINK(env(""OneDriveCommercial"")&""" & PathAdd & """,""" & TargetValue & """)"

Closer....
still produces the at sign (@) and the hyper link does not work

Below is what the cell now contained
=HYPERLINK(@env("OneDriveCommercial")&"\..\DPS\Engagements\Mary\PR-00000","PR-00000")

 

[GWteB]

The code below does work for me. I don't get an @ sign in the formula but it's most likely this has to do with which Excel version is used. I'm on Excel 2013, your profile doesn't tell us what version you are using so it's recommended to update your profile.

Sub g_speran()
    Dim PathAdd As String, TargetValue As String, NewFormula As String
    PathAdd = "\gotcha.txt"
    TargetValue = "click me"
    NewFormula = "=HYPERLINK(env(""TEMP"")&""" & PathAdd & """,""" & TargetValue & """)"
    [A1].Formula = NewFormula
End Sub

Below is what the cell now contained
=HYPERLINK(@env("OneDriveCommercial")&"\..\DPS\Engagements\Mary\PR-00000","PR-00000")

Assuming the \..\ part in your formula isn't a placeholder but intentional to retrieve the parent folder of what's returned from the environment, I don't have an idea what's causing it not to work.

 

[g_speran]

The code below does work for me. I don't get an @ sign in the formula but it's most likely this has to do with which Excel version is used. I'm on Excel 2013, your profile doesn't tell us what version you are using so it's recommended to update your profile.

Sub g_speran()
    Dim PathAdd As String, TargetValue As String, NewFormula As String
    PathAdd = "\gotcha.txt"
    TargetValue = "click me"
    NewFormula = "=HYPERLINK(env(""TEMP"")&""" & PathAdd & """,""" & TargetValue & """)"
    [A1].Formula = NewFormula
End Sub

Assuming the \..\ part in your formula isn't a placeholder but intentional to retrieve the parent folder of what's returned from the environment, I don't have an idea what's causing it not to work.

Correct. The \..\ is used to go back one directory

 

[g_speran]

Office 365

 

[GWteB]

If you're sure your ENV function returns a correct text string, then I have no more suggestions for now.
Hopefully someone else can put their finger on the sore spot.

 

[g_speran]

If you're sure your ENV function returns a correct text string, then I have no more suggestions for now.
Hopefully someone else can put their finger on the sore spot.

After running the VBA code, if I manually modify the cell and remove the at sign (@), the cell hyperlink works as expected and opens the correct directory. It’s now just way is causing that @ sign in front of env

 

[GWteB]

You could try renaming the Env function to something more than three letters. Excel then might not precede it with a @ sign. Who knows, imo worth the try.

 

[g_speran]

You could try renaming the Env function to something more than three letters. Excel then might not precede it with a @ sign. Who knows, imo worth the try.

i renamed the function from env to alongername and get the same undesirable results in the cell. the at sign (@) is still present in the formula

=HYPERLINK(@alongername("OneDriveCommercial")&"\..\DPS\Engagements\Mary\PR-00000","PR-00000")