User profile

[Ian]

Is there any way in Excel to determine the Windows environment variable
%userprofile%? I need to write a macro to update templates on various
machines and need this information.

I can copy the "update" workbook to "My Documents" using %userprofile% in a
batch file, then use =Info("directory") to retrieve the current path, then
use Left to extract the root user profile, but I consider this to be a
workaround and would prefer a direct method, if available.

Many thanks.

Ian

 

[Bob Phillips]

environ("Userprofile")

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ian]

Hi Bob

Thanks for the reply.

Sounds so simple, but it doesn't work. Is environ part of an Excel add-in?
If so, it isn't going to be practical as the laptops the updates will be on
are under others' control. Getting them to install add-ins is a bridge too
far for some.

The tidiest I've come up with is =LEFT(A1,FIND("\",INFO("directory"),27))
which appears to work on my laptop, but I've not had a chance to try it on
my desktop (for a different user profile). Of course this will only work if
the file is in a directory within the user's directory tree, but I don't
think that will be a huge problem.

Ian

 

[Gord Dibben]

Sub foo()
MsgBox Environ("Userprofile")
'or MsgBox Environ("Username")
End Sub


Gord Dibben MS Excel MVP

 

[Chip Pearson]

Sounds so simple, but it doesn't work. Is environ part of an Excel add-in?

Environ is part of VBA, not an add-in. If you want, you can use Window APIs
to get the profile folder or any other folder ("My Documents", "Desktop",
etc). See http://www.cpearson.com/Excel/SpecialFolders.htm.

 

[Ian]

Thanks Gord.

At the time of posting my question I was having problems accessing VBA help
on my machine, otherwise I'd have checked.

Works a treat.

Ian

 

[Ian]

Thanks, Chip. The page you linked is very informative, and will no doubt be
of use in the future.

In this case, the specific path I need is to a "custom" directory within My
Documents. As I need to alter the string to point to this folder, it's as
easy to use Environ. It works a treat.

Ian