using environment variable in macropath

[M. Mix]

Hiho,

i have a problem with a macro which is linked behind an excel button.
I want to use the environment variable %USERPROFILE% in the macro path
because the macro is been used from many users.

The path at this time is hard coded: C:\Dokumente und
Einstellungen\mmustermann\Eigene
Dateien\Anwendungsdaten\Microsoft\Excel\XLStart\PERSONL.XLS

I want to modify the path to:
%USERPROFILE%\Anwendungsdaten\Microsoft\Excel\XLStart\PERSONL.XLS

When i modify the path the result is the following: C:\Dokumente und
Einstellungen\mmustermann\Eigene
Dateien\%USERPOROFILE%\Anwendungsdaten\Microsoft\Excel\XLStart\PERSONL.XLS

Any hints how to solve this ?

Thx

Markus

 

[Tom Ogilvy]

sFile = environ("USERPROFILE") _
& "\Anwendungsdaten\Microsoft\Excel\XLStart\PERSONL.XLS"

 

[M. Mix]

good morning!

Tom, unfotunately i don´t know what you mean.
I have a button in Excel. Behind the button i need the macropath described
below.

I cant use code in the path or am i wrong?

Maybe i choose the wrong subnewsgroup :-/ i am sorry. `

Thanks

Markus

 

[NickHK]

Markus,
If you are trying to run a routine in ..XLStart\Personal.xls, it will be
open and the path is irrelevant. Use

Application.Run "Personal.xls!YourRoutine"

Otherwise, you cannot run code in a workbook that is not open.

NickHK

 

[M. Mix]

Hi Nick,

that don´t work. I can fill in the path as you described but if you refresh
the view or reopen the button and look at the path you see that the path is
deposited as hard coded path. Still having the inicial problem :-/

But thank you for the idea.

I cant imagine that i am the only one with that problem. How are the
otherone handling with the issue of personalized macro´s and its path?
Maybe i have to take a different way to the goal.

Thanks

Markus

 

[dmcritchie]

Hi Markus,
perhaps Application.DefaultFilePath
see if http://www.mvps.org/dmcritchie/excel/property.htm
is of any help to you.

David McRitchie