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.
CodePudding user response:
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.
CodePudding user response:
Use vbscript SpecialFolders
.
Set objShell = Wscript.CreateObject("Wscript.Shell")
strPath = objShell.SpecialFolders("Desktop")
See full list here.
CodePudding user response:
Do a check if it finds the file, else set a different path.