WELDER ID - 301054

 WELDER ID - 301054

 use of Environ("USERPROFILE") in excel vba

How to make a path as dynamic by using ENVIRON

IF path= E:\Redirect\un.singh\Desktop then I can make it dynamic BY use of vba by replacing "E:\Redirect\un.singh" with Environ("USERPROFILE") & \Desktop 

one simple example is as under-

Sub hyper_link()

Dim FileName, mystore As String

Dim PathName As String, i As Long

'AUTO SELECT USER DESKTOP NAME

PathName = Environ("USERPROFILE") & "\Desktop\ALL DRGS\"

FileName = Dir(PathName & "*.pdf")

    Do While FileName <> vbNullString

        mystore = Mid(FileName, 5, 8)

        On Error GoTo last:

'COMPARE MYSTORE FOR ALL CELLS OF COLUMN "B"

    For i = 2 To 374

        If mystore = Left(Range("B" & i).Value, 8) Then

            Range("B" & i).Select

            ActiveSheet.Hyperlinks.Add Range("B" & i), Address:=(PathName & FileName)

        Else: End If

    Next i

'NEXT FILE FOR MYSTORE

FileName = Dir

    Loop

last:

End Sub