User profile

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ian - 22 Oct 2007 08:55 GMT

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

Reply to this Message

Bob Phillips - 22 Oct 2007 09:30 GMT

environ("Userprofile")

Signature

---
HTH

Bob

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

> Is there any way in Excel to determine the Windows environment variable
> %userprofile%? I need to write a macro to update templates on various
[quoted text clipped - 8 lines]
>
> Ian

Reply to this Message

Ian - 22 Oct 2007 18:17 GMT

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

> environ("Userprofile")
>
[quoted text clipped - 10 lines]
>>
>> Ian

Reply to this Message

Gord Dibben - 22 Oct 2007 18:33 GMT

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

Gord Dibben MS Excel MVP

>Hi Bob
>
[quoted text clipped - 27 lines]
>>>
>>> Ian

Reply to this Message

Ian - 23 Oct 2007 07:44 GMT

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

> Sub foo()
> MsgBox Environ("Userprofile")
[quoted text clipped - 38 lines]
>>>>
>>>> Ian

Reply to this Message

Chip Pearson - 22 Oct 2007 19:19 GMT

> 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.

> Hi Bob
>
[quoted text clipped - 27 lines]
>>>
>>> Ian

Reply to this Message

Ian - 23 Oct 2007 07:50 GMT

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

>> Sounds so simple, but it doesn't work. Is environ part of an Excel
>> add-in?
[quoted text clipped - 34 lines]
>>>>
>>>> Ian

Reply to this Message