vba to retrieve dynamic username for file save

[Mr_Ragweed2]

Hello and thanks for reading! I have a spreadsheet that is saved on multiple users desktop. The issue is i don't (and won't) know who the users are. On the sheet there is a command button that will save it to a folder also on their desktop. This folder ("Finished Work") will have the same name for all users. At the end of the day, when they return to the office, they will upload that folder to Teams. As you can see in my code example below we are all on a One Drive. I know the code to retrieve my own pathway to the folder i just can't seem to find a way to retrieve an unknown username to add into the save pathway.
Any help is always appreciated and thank you in advance if you try and help me!

 ChDir "C:\Users\My_Name\OneDrive - My_Company\Desktop" ' need to make this dynamic to any user - our format for "My_Name" is JSmith

 

[rollis13]

In the Immediate Panel of VBE you can check your own pathway with:
? Environ("USERPROFILE")
If such, then you could use:
ChDir Environ("USERPROFILE") & "\OneDrive - My_Company\Desktop"

 

[Mr_Ragweed2]

rollis13 thank you for your help. I have gotten a little farther down the road using the following:

Dim MyName As String
MyName = Environ$("Username")

It returns the username i need when i put it in the following:

ChDir "C:\Users\MyName\OneDrive - CHS Inc\Desktop\New folder"  'puts the correct username here
    ActiveWorkbook.SaveAs Filename:= _
        "https://chsinc-my.sharepoint.com/personal/My_Profile_MyCompany_com/Documents/Desktop/New%20folder/work%20order%20spreadsheet%20version%20with%20macro.xlsm" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ActiveWorkbook.AutoSaveOn = False
    ActiveWorkbook.Save

The issue i'm having is the part above that is in green. I'm calling it my "my profile" i guess. when i run MsgBox Application.UserName i get my actual name in the following format Smith, John. It needs changed to John_Smith_MyCompany_com as part of the actual save pathway/process.

 

[rollis13]

Since you going to use a variable then this:
ChDir "C:\Users\MyName\OneDrive - CHS Inc\Desktop\New folder"
should be:
ChDir "C:\Users\" & MyName & "\OneDrive - CHS Inc\Desktop\New folder"
(note that a variable goes concatenate with the static text, not included).
But I suggested using:
Environ("USERPROFILE")
because it would match your personal user path wherever it is, even in a different disk. So in this case:
MyName = Environ$("UserProfile")
the code would be:
ChDir MyName & "\OneDrive - CHS Inc\Desktop\New folder"
or, without using a variable, the code to be used could be cut down to:
ChDir Environ$("UserProfile") & "\OneDrive - CHS Inc\Desktop\New folder"

As for your issue in green, it's probably because you didn't concatenate the variable with the text string.

 

[Skyybot]

I always use

Dim uName As String

and

uName = Application.UserName

to get a Users username for Path string.

 

[Mr_Ragweed2]

so i did this: and get an error on "TrickyPart".

Dim MyName As String
    MyName = Environ$("Username")
    
    ChDir "C:\Users\" & MyName & "\OneDrive - MyCompany Inc\Desktop\New folder" 'from excel forum
    
    Dim TrickyPart As String
    Dim UsernameReversed    As String
    Dim UsernameStringArray As Variant

    UsernameStringArray = Split(Application.UserName, ", ")

    UsernameReversed = UsernameStringArray(1) & "_" & UsernameStringArray(0)
    
    TrickyPart = UsernameReversed & "_" & "mycompanyinc" & "_" & "com"     ' my attempt
    
    ActiveWorkbook.SaveAs Filename:= _
        "https://mycompanyinc-my.sharepoint.com/personal/TrickyPart/Documents/Desktop/New%20folder/work%20order%20spreadsheet%20version%20with%20macro.xlsm" _  ' this line gets the error
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ActiveWorkbook.AutoSaveOn = False
    ActiveWorkbook.Save

In this instance i think the username is actually the user profile. MyName is returned as JSmith3 while Environ$("username") is returned as Smith, John. The above code reverses that order just fine. My struggle is concatencating it with "_mycompany_com"to result in "John_Smith_mycompany_com". When i run the recorder the address comes back hard coded that way. So my attempt was to name it as a string called "trickyPart" and insert that name into the filepath. I get a "Method 'SaveAs of object '_Workbook' failed" error highlighting that line. Any additional help on this is most welcome. Thank you.

 

[rollis13]

Maybe it's best if you start by debugging your code. Here are some helpful tips. LINK

 

[Mr_Ragweed2]

Thank you for your help.
I only get the error when i change ......."john_smith_mycompany_com" to "TrickyPart". When i step through the code, the string result for "trickypart" is correct. I even checked to see if case sensitive matter on John Smith vs john smith in the hard coded version. It is not case sensitive.
Apparently i can't use a string in my destination path? Is there a workaround for that?

 

[rollis13]

In your SaveAs Filename path "TrickyPart" is a variable, so as I said before, it needs to be concatenated (see my note in post #4).
By the way, the reported error code should give you a hint about the issue.

 

[Mr_Ragweed2]

Thank you for reminding me of that. I will work on it and post my results whether i succeed or fail. You have been a lot of help to me and i appreciate it.
(If you couldn't have guessed, this vba stuff is not what i normally do in life.)