none
XL VBA SaveCopyAs into SharePoint not working but Save As is fine... RRS feed

  • Question

  • I'm using SharePoint Online.

    The following Save As macro works fine:

    ActiveWorkbook.SaveAs Filename:= _
    "https://xxx.sharepoint.com/sites/pwa/PWAReports/Backup/Report" _
    , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

    But when I do:

    ActiveWorkbook.SaveCopyAs Filename:= _
    "https://xxx.sharepoint.com/sites/pwa/PWAReports/Backup/Report.xlsm"

    I get a 1004 error message.

    Can anyone tell me what I'm missing?

    Many thanks 

    James


    Andrew Binnz

    Thursday, November 26, 2015 4:52 AM

Answers

  • Hi James,

    Base on my test in Excel 2013 with SharePoint, the result is the same as you. I found others have the same result.

    So, in my opinion, the SaveCopyAs method isn’t support to save to SharePoint.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 27, 2015 5:21 AM
    Moderator
  • Here is a workaround. I created a function (very basic, you will improve it to fit your needs).
    Put it into a standard module or into the ThisWorkbook Module

    Option Explicit
    Dim strCurrentFilePath As String           'will hold current path and filename
    Dim blnAlertStatus As Boolean              'will hold status of Application.DisplayAlerts
    Dim strFullSaveCopyAsName As String        'will hold fullname of file on sharepoint
    Dim strTempFullname As String              'will hold the fullname of file which has been saved to sharepoint

    Public Function SaveCopyAs2(strFullSaveCopyAsName) As Boolean
    'SaveCopyAs replacement
    On Error Resume Next
    blnAlertStatus = Application.DisplayAlerts  'save the status
    Application.DisplayAlerts = False  'switch off alerts
    strCurrentFilePath = ThisWorkbook.FullName  'store production path & filename
    ThisWorkbook.SaveAs (strFullSaveCopyAsName)  'save to sharepoint, using SaveAs instead of failing SaveCopyAs
    strTempFullname = ThisWorkbook.FullName 'store the filename after SaveAs (needed for verification only)
    ThisWorkbook.SaveAs (strCurrentFilePath)  'SavAS , now using the original production path & filename
    Application.DisplayAlerts = blnAlertStatus   'restore the status
    SaveCopyAs2 = (strTempFullname = strFullSaveCopyAsName)  'true if save to sharepoint was successfull
    End Function

    Example how to use it:

    Sub mytst()
    'our sharepoint pathname:
    strShPath= '"https://xxx-my.sharepoint.com/personal/name_xxx_onmicrosoft_com/Documents/"
    targetName = "test12345.xlsb"   'Filename for SaveCopyAs to sharepiont
    If savecopyas2 (strShPath & targetName) Then
    MsgBox "Success"
    End If
    End Sub

    Prerequisite: the document you want to save a copy from must not be a new, unsaved document.
    Another watchout: When calling "SaveCopyAs2" , your original document will be saved, too.

    • Edited by Quinoman Thursday, October 11, 2018 9:23 PM
    • Marked as answer by JamesP435 Monday, September 9, 2019 12:34 AM
    Thursday, October 11, 2018 7:55 PM

All replies

  • Hi

    Using Excel VBA the SaveAs function works fine:

    ActiveWorkbook.SaveAs Filename:= _
     "https://xxx.sharepoint.com/sites/pwa/PWAReports/Report" _
     , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

    But when I use SaveCopyAs I get a 1004 error:

    ActiveWorkbook.SaveCopyAs Filename:="https://xxx.sharepoint.com/sites/pwa/PWAReports/Report.xlsm"

    Can you tell me what I've missed.

    Many thanks 

    James 


    Andrew Binnz

    Friday, November 27, 2015 2:07 AM
  • Hi,

    Could you try :

    ActiveWorkbook.SaveCopyAs "https://xxx.sharepoint.com/sites/pwa/PWAReports/Report.xlsm"


    • Proposed as answer by Malick F Friday, November 27, 2015 2:27 AM
    • Edited by Malick F Friday, November 27, 2015 2:31 AM
    • Unproposed as answer by Malick F Friday, November 27, 2015 10:56 AM
    Friday, November 27, 2015 2:25 AM
  • Hi James,

    Base on my test in Excel 2013 with SharePoint, the result is the same as you. I found others have the same result.

    So, in my opinion, the SaveCopyAs method isn’t support to save to SharePoint.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 27, 2015 5:21 AM
    Moderator
  • Already tried but no luck.  

    Andrew Binnz

    Friday, November 27, 2015 7:38 AM
  • Hi Binnz,

    First, for an issue/question, you don’t need to open/create multiple threads.

    Secondly, based on my test, the SaveCopyAs method doesn’t work for saving to SharePoint.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, November 30, 2015 5:29 AM
    Moderator
  • Helo James

    If you open the original file from "https://xxx.sharepoint.com/sites/pwa/PWAReports/Report.xlsm", then you can use:

    ActiveWorkbook.SaveCopyAs Filename:="Report.xlsm"

    it works.

    I'm sorry for the almost 3 Year delay answer :)

    Greetings

    ŽŒ

    Tuesday, July 24, 2018 2:37 PM
  • Hello all, 3 years later but the "native" use of SaveCopyAs to Sharepoint (which is the savepath of onedrive for business) is still not working. Makes me sick to spent time on such obvious bugs because MS is not addressing such   issuess for so many years. The proposed workaround is no solution. I must open my workbook from production enviroment which is a trusted location on a local PC and is vital for my complex macro to keep it local. On the other hand, I must  save a copy to sharepoint / onedrive business. SaveAs would change the ActiveWorkbook.Path etc. MS is pushing users to use online producs but the whole concept is still a shoddy piece of work




    Thursday, October 11, 2018 10:06 AM
  • Here is a workaround. I created a function (very basic, you will improve it to fit your needs).
    Put it into a standard module or into the ThisWorkbook Module

    Option Explicit
    Dim strCurrentFilePath As String           'will hold current path and filename
    Dim blnAlertStatus As Boolean              'will hold status of Application.DisplayAlerts
    Dim strFullSaveCopyAsName As String        'will hold fullname of file on sharepoint
    Dim strTempFullname As String              'will hold the fullname of file which has been saved to sharepoint

    Public Function SaveCopyAs2(strFullSaveCopyAsName) As Boolean
    'SaveCopyAs replacement
    On Error Resume Next
    blnAlertStatus = Application.DisplayAlerts  'save the status
    Application.DisplayAlerts = False  'switch off alerts
    strCurrentFilePath = ThisWorkbook.FullName  'store production path & filename
    ThisWorkbook.SaveAs (strFullSaveCopyAsName)  'save to sharepoint, using SaveAs instead of failing SaveCopyAs
    strTempFullname = ThisWorkbook.FullName 'store the filename after SaveAs (needed for verification only)
    ThisWorkbook.SaveAs (strCurrentFilePath)  'SavAS , now using the original production path & filename
    Application.DisplayAlerts = blnAlertStatus   'restore the status
    SaveCopyAs2 = (strTempFullname = strFullSaveCopyAsName)  'true if save to sharepoint was successfull
    End Function

    Example how to use it:

    Sub mytst()
    'our sharepoint pathname:
    strShPath= '"https://xxx-my.sharepoint.com/personal/name_xxx_onmicrosoft_com/Documents/"
    targetName = "test12345.xlsb"   'Filename for SaveCopyAs to sharepiont
    If savecopyas2 (strShPath & targetName) Then
    MsgBox "Success"
    End If
    End Sub

    Prerequisite: the document you want to save a copy from must not be a new, unsaved document.
    Another watchout: When calling "SaveCopyAs2" , your original document will be saved, too.

    • Edited by Quinoman Thursday, October 11, 2018 9:23 PM
    • Marked as answer by JamesP435 Monday, September 9, 2019 12:34 AM
    Thursday, October 11, 2018 7:55 PM
  • Thanks for that.  

    James 




    • Edited by JamesP435 Monday, September 9, 2019 12:34 AM
    Monday, September 9, 2019 12:34 AM