QA–QC BYUNS – Note – "This site uses cookies from Google to deliver its services to personalize ads and to analyze traffic information about your use of this site is shared with Google, BY USING THIS SITE YOU AGREE TO ITS USE OF COOKIES"

Friday, January 1, 2021

use of Environ("USERPROFILE") in excel vba

 use of Environ("USERPROFILE") in excel vba

ENVIRON

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

 

No comments:

Post a Comment

Please do not enter any spam link in the comment box