Is there any way to get a user's "folder name" using VBA in Excel?
For example, suppose a user (Joe Smith) has a personal folder (underneath the C:\Users directory) that is C:\Users\JSmith.
I tried using Application.Username but that gives me (for example) "Joseph R. Smith", which I'm guessing is the name specified when setting up Excel.
How do I get the actual "user folder" name for the current user?
Reason: I need to create a filepath for saving a file, but the Excel file that's running is on a Sharepoint site, so ThisWorkbook.Path returns a URL (which Excel rejects as a bad file name if I try to use it to save a file). I've tried to convert it to a UNC, but that's not working either. Apparently, there is a folder on the C: drive which is a copy of (or some kind of link to) the Sharepoint site, so I want to use THAT filepath to save the file. I know exactly how to construct that filepath, EXCEPT for the "user folder" name, which is different for each user (so it's not something I can hardcode into the file).
Any help / suggestions would be GREATLY appreciated!
Bookmarks