Complicated question from a noob

Jugger_man

New Member
Joined
Jul 26, 2012
Messages
2
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.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Maybe something like

Code:
    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
 
Upvote 0
Maybe something like

Code:
    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.
 
Upvote 0
You could put the usernames in a named range on a worksheet.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top