none
VBA For button to SaveAs in OneDrive. RRS feed

  • Question

  • My entire office shares a common OneDrive. Our invoice system Runs and Saves on individual computers as PDF & .xlsx.

    We have a button that is a save and clear. When I try to change the Save Path I get an error. I have searched the web for a VBA that will allow me to save to my OneDrive to no avail.

    Please Help!

    Sub SaveInvoiceBothWaysAndClear()
        Dim NewFN As Variant
        ' Create the PDF First
        NewFN = "C:\Users\Vision 2250\OneDrive\Documents\PDF Invoices\" & Range("J16") & " " & Range("J17").Value & ".pdf"
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=NewFN, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, _
            IgnorePrintAreas:=False
        ' Next, Save the Excel File
        ActiveSheet.Copy
        NewFN = "C:\Quotes\Excel Invoices\" & Range("J16") & " " & Range("J17").Value & ".xlsx"
        ActiveWorkbook.SaveAS NewFN, FileFormat:=xlOpenXMLWorkbook
        ActiveWorkbook.Close
        ' Clear out the invoice fields
        Range("J16,J17").ClearContents
    End Sub

     
    Friday, October 6, 2017 1:53 PM

Answers

  • Hello,

    To save to OneDrive, it should be a network path. Please record a macro and save to OneDrive, then you would get the path.

    E.g.

    Sub Macro1()
        ActiveWorkbook.SaveAs Filename:= _
            "https://d.docs.live.net/89599cf5610d3147/test.xlsm", FileFormat:= _
            xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    End Sub
    

    Or you could open a OneDrive document online or open OneDrive online. Then get the cid of the url.

    E.g.

    https://onedrive.live.com/?id=root&cid=89599CF5610D3147

    So the path you should use to store document to OneDrive is:

    https://d.docs.live.net/[YourCid]

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by NealE33 Monday, October 9, 2017 2:24 PM
    Monday, October 9, 2017 4:58 AM
    Moderator

All replies

  • I don't believe ACCESS can recognize OneDrive as a legitimate file path. You need to save to a Shared Network drive or indiviual machine drive first, then manually move files to OneDrive.
    Friday, October 6, 2017 2:22 PM
  • Thank you for the reply. Well If it can't then None of this will work for what I need it to do. That really stinks. Know of any other options?  I should probably add that the people in my office are not tech savvy so I have to make this as easy as pie for them.
    • Edited by NealE33 Friday, October 6, 2017 2:37 PM
    Friday, October 6, 2017 2:33 PM
  • What error, and on what line?

    I don't see how you are "saving to OneDrive" with this code. You seem to be saving to NewFN on your local system.

    OneDrive can be setup to sync with a local folder. If you save to that folder, what happens? My guess is the file will show up on OneDrive.


    -Tom. Microsoft Access MVP

    Friday, October 6, 2017 2:40 PM

  • The Folder that I am trying to save it to is "local" per say or so I thought. Maybe my path is wrong.



    • Edited by NealE33 Friday, October 6, 2017 2:55 PM
    Friday, October 6, 2017 2:46 PM
  • No I checked my Local Folder and it is good I keep getting the 

     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=NewFN, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, _
            IgnorePrintAreas:=False

    Part of the code highlighted. Telling me that there is something wrong. Any suggestions?

    Run-time error '-2147024773(8007007b)':

    Document not saved.

    • Edited by NealE33 Friday, October 6, 2017 3:25 PM
    Friday, October 6, 2017 3:19 PM
  • May I suggest a sanity check?  

    What happens if you use the manual technique of the External data in the ribbon.  

    Will it export an excel to your OneDrive folder via that method?

    Sunday, October 8, 2017 3:25 PM
  • Hello,

    To save to OneDrive, it should be a network path. Please record a macro and save to OneDrive, then you would get the path.

    E.g.

    Sub Macro1()
        ActiveWorkbook.SaveAs Filename:= _
            "https://d.docs.live.net/89599cf5610d3147/test.xlsm", FileFormat:= _
            xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    End Sub
    

    Or you could open a OneDrive document online or open OneDrive online. Then get the cid of the url.

    E.g.

    https://onedrive.live.com/?id=root&cid=89599CF5610D3147

    So the path you should use to store document to OneDrive is:

    https://d.docs.live.net/[YourCid]

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by NealE33 Monday, October 9, 2017 2:24 PM
    Monday, October 9, 2017 4:58 AM
    Moderator