Thanks Steve. Incidentally, is there any disadvantage to using this method
vs using the environ method? After posting I changed my search tactic and
discovered a list of environ variables and discovered I could use
"userprofile" to return everything before "\desktop\" but before I decide
which method to use I thought I'd ask if one is better or faster or otherwise
preferable. Thanks!

"Steve Yandl" wrote:

Here is one option to return the path to the current user's desktop as a
string.

Set objShell = CreateObject("Shell.Application")
Set objFolderDsk = objShell.Namespace(&H10&)
strDsk = objFolderDsk.Self.Path
MsgBox strDsk
Set objShell = Nothing

Steve



"ArielZusya" wrote in message
...
I've got a quirky problem. The network admin folks here at my office have
a
bit of a strange naming convention for users. If you start as a part
timer
you get a username that looks something like pt[loc][initials] so for
someone
who is named John H. Smith starting part time at location number 04, his
login would be pt04jhs. The trouble is that if John switches to full time
his username changes to ft04jhs but his userprofile still points to the
directory C:\documents and settings\pt04jhs\. I want to use vba to export
(copy) a sheet from the workbook I'm in to a new workbook, save that
workbook
to the desktop, and close that workbook without prompting the user.
(Incidentally, the rest of the naming convention for the file relies on a
combo box on the main menu (frmMainMenu) form called cmbSelectDivision,
just
in case you're wondering why that's in there.) Here's my code right now:

dim NameThatExport as string
NameThatExport = "Division" +
frmMainMenu.cmbSelectDivision.Value _
+ ".xls"
Sheets("MyList").Select
Sheets("MyList").Copy
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\" + Environ("username") _
+ "\Desktop\" + NameThatExport, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False _
Windows(fileNm).Activate
Application.CutCopyMode = False
ActiveWorkbook.Close False


The code works well for users who started full time and remained full time
and for users who started part time and remained part time but for those
who
started part time and switched to full time they get no autonaming. Is
there
a way to use code to find the desktop other than the way I tried above?
Thanks for your help!