How to save a file to OneDrive

Semaj

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
    #Else
     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\"
        Else
        '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
    ActiveSheet.Shapes("SaveButton").Delete
    ' 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.