File Path - Macros

[Max2073]

Need some help.

I developed a macro that was used by myself, however the macro now needs to
be shared.

The macro opens files that are located in my C:\document and
settings\max28052. These files are saved automatically once exported from
the corporate database. The file is automatically named - they always have
the same name regardless of the officer.

Is there a way that I can change the macro to open the required files
without stating the full path (as it changes) eg. instead of c:\document and
settings\max28052\help.xls that it opens the logged on users eg. c:\document
and settings\"officers name"\help.xls.

 

[joel]

I you open a dos widnow and type SEt you will see all the use
environmental paraters. On WinXP go to

Start - Run
In run box type : cmd.exe

A dos window opens and then type : Set


In Vista go to Start and do a search for cmd.exe. follow instruction
above


The VBA code wil be something like this

UserProfile = environ("Userprofile")
Helpfile = Userprofile & "\help.xls

 

[Gary''s Student]

Perhaps:

Sub officer()
Dim s1 As String, s2 As String, s3 As String
s1 = "C:\Documents and Settings\"
s2 = Environ("Username")
s3 = "\help.xls"
Workbooks.Open Filename:=s1 & s2 & s3
End Sub

 

[Mike H]

Max,

Something like this may work

MyPath = CreateObject("WScript.Shell").Specialfolders("MyDocuments")

or you can build the path

MyPath = "C:\documents and settings\" & Environ("Username") & "\" & "
Help.xls"

Mike