userid in macro

[Guest]

I have a macro that opens a file on my desktop and references the full
pathname including my id. Is there a way to convert this so that anyone can
use it?
example:
Workbooks.OpenText Filename:= "C:\Documents and
Settings\myid\Desktop\assys.txt",...

We all will have the same pathname with the exception of "myid"

Thanks for your help,
Mary Ann

 

[Gary Keramidas]

in the vb editor, type the following in the immediate pane and press enter (if
it's not visible, hit control-G or click on view then immediate window)


?environ("userprofile")

 

[Gary Keramidas]

sorry hit send too fast

Workbooks.OpenText Filename:=(Environ("userprofile") & "\Desktop\assys.txt")

 

[Guest]

Try:-

UserProfileFolder = Environ("UserProfile")

To return the user-profile and incorporate the variable into the path

Workbooks.OpenText Filename:= "C:\Documents and Settings\" &
UserProfileFolder &" myid\Desktop\assys.txt",..

Mike

 

[Guest]

That worked! Thanks Gary.

sorry hit send too fast

Workbooks.OpenText Filename:=(Environ("userprofile") & "\Desktop\assys.txt")

 

[Guest]

Mike,
I'm getting an error on this format:
Run-time error '1004':
Method "OpenText' of object 'Workbooks' failed

Here's what I tried:
UserProfileFolder = Environ("UserProfile")

Workbooks.OpenText Filename:="C:\Documents and Settings\" &
UserProfileFolder & " \Desktop\assys.txt", ...

 

[Chip Pearson]

I'm getting an error on this format:
Run-time error '1004':
Method "OpenText' of object 'Workbooks' failed

Environ("UserProfile") return the folder name starting at the drive
specification, so you can't prefix it with another partial path. For
example, on my system (Vista), Environ("UserProfile") returns
"C:\Users\Pearson", so if you were to use that in the code you show, you
would be creating a file name like

C:\Documents And Settings\C:\Users\Pearson\Desktop\assys.txt

which is an invalid file name, and thus the OpenText method will blow up.

See http://www.cpearson.com/Excel/SpecialFolders.htm for code to get any of
the various user-specific folders.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

Thanks Chip. Most of the info in the link is over my head but I understand
your reply. Thanks again for your help
Mary Ann