Opening and Naming Files

[Jim Berglund]

I want to be able to open a file from any users desktop.

From my desktop, the command is

Workbooks.Open Filename:="C:\Users\Jim\Desktop\403.csv"

How do I automate this, using
UserName = Range("G5").Value

So that if the user enters 'Dave' into G5, the code line will change to

Workbooks.Open Filename:="C:\Users\Dave\Desktop\403.csv" ?

Thanks,
Jim Berglund

 

[Ed Ferrero]

This will work for any user, no need to get the user name

Workbooks.Open Filename:="..\Desktop\403.csv"

But if you really want to manipulate strings

UserName = Range("G5").Value
strPath1 = "C:\Users\"
strPath2 = "\Desktop\403.csv"
strFileNamePath = strPath1 & UserName & strPath2

Workbooks.Open Filename:=strFileNamePath

Ed Ferrero
www.edferrero.com

 

[Gary Keramidas]

there is no error checking but

Workbooks.Open Filename:="C:\Users\" & Range("G5").Value &
"\Desktop\403.csv"


the range should be qualified and there should be some code if the user
enters or spells the name wrong

 

[ozgrid.com]

Name your username cell "UserName" and then use;


Sub OpenIt()
On Error GoTo NoGo
Workbooks.Open _
Filename:="C:\Users\" & _
Range("UserName") & _
"\Desktop\403.csv"
Exit Sub
NoGo: MsgBox "'C:\Users\" _
& Range("UserName") & _
"\Desktop\403.csv' is not valid"
End Sub

 

[Chip Pearson]

Note that the "Users" directory is new as of Windows Vista. In Windows
XP and earlier, the folder name is "Documents And Settings". Vista
and Windows 7 provide backwards compatibility by aliasing "Documents
And Settings" to point to "Users", but the reverse is not true. You
would be best off using code like

Dim DesktopFolder As String
Dim N As Long
Dim UserName As String
UserName = Range("G5").Value
DesktopFolder = Environ("userprofile")
N = InStrRev(DesktopFolder, "\")
DesktopFolder = Left(DesktopFolder, N) & UserName & "\Desktop"
Debug.Print DesktopFolder

If you want to get the desktop folder for the current user, you can
simplify this to

Dim DesktopFolder As String
DesktopFolder = Environ("userprofile") & "\Desktop"

If you want a really ironclad way to do this, see the code and
download at http://www.cpearson.com/Excel/SpecialFolders.aspx . It is
a lot of code, but allows you to get any of the special user folders
(Application Data, Program Files, Favorites, etc).


Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Jim Berglund]

Ed, I've tried this. It is the ideal solution I'm looking for, but I encoded
the application on Windows 7, and when I transferred it to my Vista Laptop,
I got a runtime error 1004:
'..\Desktop\403.csv' could not be found.

It is on the local desktop. What could be wrong?

Jim

 

[Jim Berglund]

Chip, I appreciate what you've sent, but it's overkill for what I want to
do.
I like Ed's approach, replacing the cumbersome method I started with with
the elegant and simple replacement,

'..\Desktop\403.csv'

This should work for all my users, and does on the Win 7 computer where I
developed the code. Strangely, it doesn't on my Vista laptop. Do you have
any idea what might be causing the runtime error 1004: '..\Desktop\403.csv'
could not be found...

The file is on the local desktop. What could be wrong?

Jim

 

[Chip Pearson]

Ed's approach works only if the current working directory has not been
changed from the default "C:\Users\<username>\Documents". However it
is not at all uncommon for code to change the current drive and/or
directory. For example, with a newly opened session of Excel, run the
code

Sub AAA()
Dim FName As String
FName = "..\Desktop\keys.lnk"
Debug.Print Dir(FName)
End Sub

Change "keys.lnk" to some folder on the desktop. The code will
correctly display "keys.lnk". However, if you run

Sub AAA()
Dim FName As String
ChDrive "D:\MyFolder" ' or any other folder
ChDir "D:\MyFolder"
FName = "..\Desktop\keys.lnk"
Debug.Print Dir(FName)
End Sub

Ed's code won't work because the ".." in the file spec is a relative
path from the current directory. If the current directory has changed,
the relative path will point to some other directory, or to no
directory at all. You should never assume that the current directory
has some value known before your code runs. You should write the code
such that it will work regardless of what the current directory
happens to be at run time. My code, using environ("userprofile") does
exactly that.



Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com