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

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


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

sorry hit send too fast

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

--


Gary


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

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

"mmccoog" wrote:

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

That worked! Thanks Gary.

"Gary Keramidas" wrote:

sorry hit send too fast

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

--


Gary


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

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

"Mike H" wrote:

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

"mmccoog" wrote:

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

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

"Chip Pearson" wrote:


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)



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

"Mike H" wrote:

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

"mmccoog" wrote:

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