Different paths for different users; what is the best way to handle? 1

[alwayslrN]

I have a couple of macros where I had set the path in the code, not realizing at the time that because of different rights, not everyone has the same path. Also, it is not good to hard code the path if drive letters and such are eventually changed.

For example, one person may have

P:\LoanData\

another

P:\SFP\LoanData\

and another

P:Input\SFP\LoanData\

In addition, those are production paths. During testing the paths would be different, but one issue at a time.

For one of the macros I decided to create a drop-down list with the various paths but there are two disdvantages to that: (1) The list has to be maintained by someone (2) a user can inadvertantly choose the wrong path.

Thus, I would like to avoid the above, but I also need to be able to handle all of these different paths.

Any suggestions is greatly appreciated. Please keep in mind, these are paths that are being written to. The user is not extracting anything from the paths.

Thanks again

 

[ck1999]

I have used this code before to get the computer name and then from there was able to change forms based off of computername. This was used in access.

Option Compare Database
Option Explicit

'*************************************************************************
'* INTERFACE                                                             *
'*************************************************************************

'Public collections
Public Drives As New Collection


'Windows API function declarations
Private Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" _
        (ByVal sBuffer As String, lSize As Long) As Long
Private Declare Function GetUserName Lib "advapi32" Alias "GetUserNameA" _
        (ByVal sBuffer As String, lSize As Long) As Long

'Private variable declarations
Private strcomputername As String
Private strUserName As String


Public Sub Class_Initialize()
'*****************************************************************************
'* PURPOSE: To initialize variables when MyComputer object is instantiated
'* NOTES:   This procedure also populates the Disks collection
'*****************************************************************************

Dim lngbuffsize As Long

'Get computer name
strcomputername = Space$(255)
lngbuffsize = Len(strcomputername)
If GetComputerName(strcomputername, lngbuffsize) Then
    strcomputername = Left$(strcomputername, lngbuffsize)
End If

'Get user name
strUserName = Space$(255)
lngbuffsize = Len(strUserName)
If GetUserName(strUserName, lngbuffsize) Then
    strUserName = Left$(strUserName, lngbuffsize - 1)
End If

End Sub
Public Property Get ComputerName() As String
'*****************************************************************************
'* NAME:    COMPUTERNAME
'* RETURNS: A string indicating the name of the computer
    
    ComputerName = strcomputername

End Property
Public Property Get UserName() As String
'*****************************************************************************
'* NAME:    USERNAME
'* RETURNS: A string indicating the name of the currently logged-on user

    UserName = strUserName

End Property

I am not sure where I go this code, I did not write it. Possibly this site. But it works well.

ck1999

 

[xlbo]

Just use full UNC paths rather than shortcuts

\\ServerName\Folder1\Folder2\Folder3 etc etc

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[alwayslrN]

Please pardon my ignorance. I am not familiar with UNC. Would I be replacing ServerName with the name of the network?

Thanks

 

[alwayslrN]

I really need to give myself a little more credit.

Thanks for your help Geoff. I was able to get the UNC to work on my end. I just need the various users to test the change.

 

[xlbo]

no probs - using the full network path takes away any issues with mapped drives as it is the same for everyone...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question