File Path - Macros

  • Thread starter Thread starter Max2073
  • Start date Start date
M

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