Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to save a file to OneDrive


How do i save a file to OneDrive and pickup the local username

Filename = "New file" & Logged on username

Save it to my OneDrive

E.g C:\Users\Peter\OneDrive\Test Folder\Filename




What - bit versions of Windows and Excel are you and your users using? 
John_Ru (rep: 5572) Aug 30, '23 at 11:23 am
Windows 11
Version 10.0
22621 Build 22621
Office 365 latest
semaj (rep: 2) Aug 31, '23 at 3:44 am
I don't have Windows 11 but understand it doesn't have a 32-bit version (unlike earlier versions). Anyway the code I gave should work with all recent versions of Windows and Excel 
John_Ru (rep: 5572) Aug 31, '23 at 5:55 am
thanks John that worked 
semaj (rep: 2) Sep 11, '23 at 7:00 am
Great. Thanks for selecting my Answer, Semaj
John_Ru (rep: 5572) Sep 11, '23 at 7:36 am
Add to Discussion


Selected Answer


The attached file should work with both 32-bit and 64-bit versions of Excel and Windows.

It uses an API call to extract the user name (defined as variable UsrNm) which is used to created the OneDrive folder path (UsrFldr) and filename (FlNm).

Currently there's a blue button labelled "Save a copy to OneDrive" which -when clicked- runs this code in Module1:

#If Win64 = 1 And VBA7 = 1 Then
     Declare PtrSafe Function GetUserName Lib "advapi32.dll" Alias _
        "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
     Declare Function GetUserName Lib "advapi32.dll" Alias _
        "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
#End If

Sub ReturnUserName()

    Dim GetVal As String * 255, EndNm As Long, UsrNm As String
    Dim UsrFldr As String, FlNm As String

    UsrNm = ""
    On Error Resume Next

    ' find where name (if any) ends
    EndNm = GetUserName(GetVal, 255)
    EndNm = InStr(1, GetVal, Chr(0))
    ' use name to create folder path
    If EndNm > 0 Then
        UsrNm = Trim(Left(GetVal, EndNm - 1))
        UsrFldr = "C:\Users\" & UsrNm & "\OneDrive\"
        'no user name
        UsrFldr = "C:\Users\OneDrive\"
    End If

    ' #1) create and save non-macro file
    FlNm = UsrNm & "_report_" & Format(Date, "yyyy-mm-dd") & ".xlsx"
    ' delete the button
    ' save without notification
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=UsrFldr & FlNm, FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    ' tell user
    MsgBox "Saved as " & UsrFldr & FlNm
    Exit Sub

    ' #2) save as macro-enabled file
    ' N.B. you need to oomment out (or delete) above section to do so
    FlNm = UsrNm & "_report_" & Format(Date, "yyyy-mm-dd") & ".xlsm"
    ' save
    ActiveWorkbook.SaveAs Filename:=UsrFldr & FlNm, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    ' tell user
    MsgBox "Saved as " & UsrFldr & FlNm

End Sub

The first section (from #If to #End if) declares the right library for the various 64-bit and 32-bit versions.

The code currently runs the code from the comment:

    ' #1) create and save non-macro file
and will save a non-macro file (without that button) to OneDrive, named like John_report_2023-08-30.xlsx (i.e. the user name and _report_ and the date in yyy-mm-dd format) but you could change that..

If you want a .xlsm file instead, comment out from  ' #1) create... to Exit Sub and it will save a copy file like John_report_2023-08-30.xlsm

Hope this works for you. If so, please remember to mark this Answer as Selected.



Thanks John_Ru
semaj (rep: 2) Aug 31, '23 at 4:24 am
Glad that worked but you forgot to mark the Answer as Selected - that's my only reward (via Reputation points) and helps other users find working solutions if they have similar problems.

If you were notified of changes to the Answer, it's just that I spotted several typos and corrected them.
John_Ru (rep: 5572) Aug 31, '23 at 5:51 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login