Complicated question from a noob

[Jugger_man]

New user to the forum and afraid I'm asking an obvious question. Everything I know about Excel and macros I've learned by recording them and tearing apart the code to translate it. I have 0 experience with VBA, so be gentle.

What I'm trying to do is (inside a macro)pull data from different folders based on the current user-

\\Mainframe\Server\System\UserData\b0grizw\samefilename.xls
\\Mainframe\Server\System\UserData\t4kavoy\samefilename.xls
\\Mainframe\Server\System\UserData\c0lee\samefilename.xls
\\Mainframe\Server\System\UserData\cmcgee9\samefilename.xls

As it stands now, I write a macro in a worksheet, then go back and edit it 4 times, one for each user. Everyone has to use a different worksheet to pull their info, even tho we use the same data in the same way.

I thought that was the only way to do it, until I just came across another program that someone had written before, however that someone no longer works for my company. For some reason I think that his was based on using the "Recent Documents" folder in (XP)
C:\Documents and Settings\Users\c0lee\samefilename.xls, however, again it is obviously hidden inside another user profile.

I'm hoping that this is something that can be easily overcome by an Excel Master. I am your humble grasshopper.

P.S. If anyone can give me a quick and dirty way to close a dialog box (Do you want to save changes before closing? [No]), that would be great.

 

[shg]

Maybe something like

    Const sPath     As String = "[URL="file://\\Mainframe\Server\System\UserData\"]\\Mainframe\Server\System\UserData\[/URL]"
    Const sFile     As String = "\samefilename.xls"
    Dim avsUser     As Variant
    Dim i           As Long
    Dim sFullName   As String

    avsUser = VBA.Array("b0grizw", "t4kavoy", "c0lee", "cmcgee9")

    For i = 0 To UBound(avsUser)
        sFullName = sPath & avsUser(i) & sFile
        ' your stuff here
    Next i

 

[Jugger_man]

Maybe something like

    Const sPath     As String = "[URL="file://\\Mainframe\Server\System\UserData\"]\\Mainframe\Server\System\UserData\[/URL]"
    Const sFile     As String = "\samefilename.xls"
    Dim avsUser     As Variant
    Dim i           As Long
    Dim sFullName   As String

    avsUser = VBA.Array("b0grizw", "t4kavoy", "c0lee", "cmcgee9")

    For i = 0 To UBound(avsUser)
        sFullName = sPath & avsUser(i) & sFile
        ' your stuff here
    Next i

That looks like an excellent start, however I was hoping for something that would work for unnamed future users. But it is definitely better than what I've been doing.

 

[shg]

You could put the usernames in a named range on a worksheet.