How to use `Environ("USERPROFILE")` to open workbook on desktop , while desktop location changed to another drive (D)?

user_17797849 0
Viewed 317

On my work PC, I have no write on system drive (c).
So, I have used properties of desktop folder and changed location of it to another drive (d).
From windows 10, I am using my desktop from that drive (d) similarly as it was on drive (c).
Problem: I tried this vba code to open workbook on my desktop

Dim wb1 As Workbook: Set wb1 = Workbooks.Open(Environ("USERPROFILE") & "\Desktop\Query1.xls")

,but I got this error
Run-time error 1004:Sorry, we couldn't find C:\Users\Waleed\Desktop\Query1.xls. Is it possible it was moved, renamed or deleted?
I am asking this question, as I use excel files on my work pc and my laptop (no restriction) interchangeably
And it is hard to change my vba codes every time I change my machine.
As always , thanks for any help.

vba excel windows
3 Answers

Desktop on Different Drives

Get it in a sub

Sub DirDesktop()
    Dim DeskTopPath As String: DeskTopPath = Environ("USERPROFILE") & "\Desktop\"
    If Len(Dir(DeskTopPath, vbDirectory)) = 0 Then
        DeskTopPath = Replace(DeskTopPath, "C", "D", , 1)
    End If
    Debug.Print DeskTopPath & "Query1.xls"
End Sub

Get it using a function

Sub GetDeskTopTEST()
    Dim DeskTopPath As String: DeskTopPath = GetDeskTop
    Debug.Print DeskTopPath & "Query1.xls"
End Sub

Function GetDeskTop() As String
    GesktopPath = Environ("USERPROFILE") & "\Desktop\"
    If Len(Dir(GetDeskTop, vbDirectory)) = 0 Then
        GetDeskTop = Replace(GetDeskTop, "C", "D", , 1)
    End If
End Function

Here's another idea

You can set your own Environ variables. Go to Windows Settings > About > scroll down to Advanced Settings and at the bottom click on Environment Variables. Click the upper New box and in the Variable Name box use e.g. DeskTop and in the Variable value box use the path. If you do this on both computers, you can on both of them access the folder with Environ("DeskTop"). You have to restart Excel for this to take effect.

user_98140690

Use vbscript SpecialFolders.

   Set objShell = Wscript.CreateObject("Wscript.Shell")
   strPath = objShell.SpecialFolders("Desktop")

See full list here.

Function Desktop() As String
'returns the path to Desktop 
    Desktop = CreateObject("wscript.shell").specialfolders("desktop")
End Function
user_785220

Do a check if it finds the file, else set a different path.

user_154401380
Related