Shared file saving to Sharepoint using VBA and current credentials

Alan Swanson 1 Reputation point

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.


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.

Microsoft Entra External ID
Microsoft Entra External ID
A modern identity solution for securing access to customer, citizen and partner-facing apps and services. It is the converged platform of Azure AD External Identities B2B and B2C. Replaces Azure Active Directory External Identities.
2,460 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Paul Delke 1 Reputation point

    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  
    0 comments No comments