Opening and Naming Files
 

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





On Sun, 16 May 2010 23:36:25 -0600, "Jim Berglund"
wrote:

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

Opening and Naming Files
 

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" wrote in message
...
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





On Sun, 16 May 2010 23:36:25 -0600, "Jim Berglund"
wrote:

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

Opening and Naming Files
 

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



On Mon, 17 May 2010 20:52:56 -0600, "Jim Berglund"
wrote:

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" wrote in message
.. .
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





On Sun, 16 May 2010 23:36:25 -0600, "Jim Berglund"
wrote:

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