Issue with changing user names - help with ENVIRON()

[photodotexe]

Hey everybody!!

Thanks for your interest. Let me explain my end result first...

I have three computers running Windows 8.1 Pro x64 with Office 2013 on two of them, and Office 2010 on the third one. They all have Microsoft SkyDrive/OneDrive installed - all using the same SkyDrive/OneDrive credentials. The Windows credentials are all different, and therefore C:\Users\username\ is a variable. I have an Excel worksheet in C:\Users\username\SkyDrive\games\ called calculator.xlsx. In the same directory as calculator.xlsx, I have a "data" folder, and within the data folder is a giant hierarchy of Excel files organized in folders. This complex collection of data in the form of Excel files is updated nightly by one of the three computers I mentioned above using a web-browser data scraping tool. It updates hundreds of these files in the "data" folder every night. I built the calculator to use functions which references data in specific cells of specific workbooks deep within the "data" folder. It works GREAT on one computer. However, when I launch the calculator Workbook on one of my other two computers, none of the links work because it can't find the path (the username is different). The calculator doesn't work.

GOAL: I want my calculator spreadsheet to reference the environment variable for current user. I know it can't do that, so I need some sort of VBA code that'll help me.

Here's what my current reference to other data looks like from a cell in my calculator: ='C:\Users\photodotexe\OneDrive\games\data\standard\exchange rates\[standardexchange.xlsx]Table 1'!B3. This works great on the computer that I log in as "photodotexe" on. But what about the computer I log in with "Mark" on? Doesn't work...

I am experiencing difficulty using the ENVIRON() VBA function. I mean, I may be doing it wrong because I don't know VBA at all. But, will that even help me in my case?

[TMS]

Maybe this will help:

http://www.excelforum.com/tips-and-t...excel-vba.html


Regards, TMS

[TMS]

Is this resolved?

[photodotexe]

Almost.. I really appreciate the input! I managed to add a new module with the following code...

Function UserName()
UserName = Environ("username")

End Function

This allows me to use =UserName() to pull the current username. However, I'm trying to put that in a path. Like C:\Users\UserName()\SkyDrive\ doesn't work. I'm still researching this - but I'm open to suggestion!