Shared file saving to Sharepoint using VBA and current credentials

Question

I work in an office where multiple people could run the same report.
I have created a Macro to compile the data, however when someone other than me goes to save to the directory they cannot as the VBA has my credentials hardcoded into it, how do I set up the VBA to use the credentials of any person that runs the Macro.

EXAMPLE CODE BELOW.

ChDir "C:\Users\CURRENT USER\OneDrive - CompanyName., Inc\filename"

I need to replace the CURRENT USER location with the credentials of the person running the macro.

Answer 1

Here's VBA code to do this:

Sub ChangeToOneDriveFolder()  
    Dim strPath As String  
      
    'Get path from Environment (Type Set in CMD window to see others).  
    strPath = Environ("OneDrive")  
    'Change drive letter (I use D for my data files and OneDrive is on the C drive.  
    ChDrive Left(strPath, 2) & """"  
    'Set current filder to One Drive.  
    ChDir strPath  
End Sub