none
Escel VB macro copy to sharepoint sometime work and sometime fail. RRS feed

  • Question

  • Hello,

    I have a little part in my macro which copy the worksheets into a workbook and save that into sharepoint.

    If there is no file then it save without problem. But if the file already exist there then sometime overwrite it (this is what i want)  and sometim fail the save as process. Like i run the macro 2 time and it works, overwrite the file. and fail at the 3rd attempt.

    Dim s_SP_RelevName2 As String s_SP_RelevName2 = Sharepoint_site_RELEV_2 & Output_Relev_F2_Name Workbooks(Output_Relev_F2_Name).SaveAs Filename:= _ s_SP_RelevName2, _ FileFormat:=xlOpenXMLWorkbook, CreateBackup:=True

    'Closing the WB

    Workbooks(Output_Relev_F2_Name).Close savechanges:=True, Filename:=Output_Relev_File2_Name

    Sharepoint_Site_Relev_2 contain the SP site address.

    Output _Relev_F2_Name contain the document actual name, like: ImTestingThis.xlsx

    I want to save the document in the same name every day. So i need this to overwrite the existing file.

    For 1 time it works. After that it fail.

    I have other part just like this saving to sharepoint. But it has generated date, time in the name so it won't overwrite anything when generated.

    Thanks.


    I Hate Mondays

    Monday, October 3, 2016 2:05 PM

Answers

  • Hi Lokids,

    please first confirm that with Kill command it is deleting the file.

    go to the location where you saved the file and check whether it get deleted or not.

    if not then try the code mentioned below to delete the file first.

    Sub sbDeletetingAFile()
    Dim FSO
    Dim sFile As String
    sFile = "D:\Job\" & "Sample.xls"  
    Set FSO = CreateObject("Scripting.FileSystemObject")
    If FSO.FileExists(sFile) Then
     location
    FSO.DeleteFile sFile, True
    MsgBox "Deleted The File Successfully", vbInformation, "Done!"
    Else
    MsgBox "Specified File Not Found", vbInformation, "Not Found!"
    End If
    End Sub
    

    also this time try to execute the workbook.saveas with just 1 argument that is file name.

    discard all other arguments and check whether it work.

    other workaround can save as the file on your local storage first then just try to move or copy the file to the sharepoint location.

    if the save as method not work then you can try the work around mentioned above. 

    Regards

    Deepak


    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.

    • Marked as answer by Lokids Friday, October 7, 2016 12:14 PM
    Thursday, October 6, 2016 3:48 AM
    Moderator

All replies

  • Hi Lokids,

    Try to do something like below.

    Sub demo2()
    Dim fullFilePath, importFolderPath, filename As String
    Dim xls As Object
    Dim wb As Workbook
    importFolderPath = "C:\Users\v-padee\Desktop\"
    filename = "demo1.xlsx"
    Set xls = CreateObject("Excel.Application")
    xls.DisplayAlerts = False
    Set wb = xls.Workbooks.Add
    fullFilePath = importFolderPath & filename
    
    wb.SaveAs fullFilePath, AccessMode:=xlExclusive, ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges
    wb.Close (True)
    xls.DisplayAlerts = True
    End Sub

    Workbook.ConflictResolution Property (Excel)

    Regards

    Deepak


    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.


    Tuesday, October 4, 2016 1:16 AM
    Moderator
  • Still not working. :(

    I added the

    AccessMode:=xlExclusive, ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges

    to the .saveas, but still getting error: "Method 'SaveAs' of object '_Workbook' failed"

    And now it fails at the 1st try as well.


    I Hate Mondays


    • Edited by Lokids Tuesday, October 4, 2016 10:53 AM
    Tuesday, October 4, 2016 10:27 AM
  • Hi Lokids,

    in your original code try to change the parameter of FileFormat:=xlOpenXMLWorkbook to FileFormat:=1.

    something like mentioned below.

    Sub A()
       Dim myNewSheet As Worksheet
       Set myNewSheet = ActiveSheet
       FileNameBin = "c:\ABC"
       myNewSheet.SaveAs Filename:=FileNameBin, FileFormat:=1
    End Sub

    and then try to run the code again.

    Reference:

    Error message when you run a Visual Basic for Applications macro in Excel: "Method 'SaveAs' of object '_Worksheet' failed"

    also try to test with your original code that is it working if the file is not available their to replace.

    if it working like that then you can check every time that file is there or not. if file is there then you can delete the file first then save a new file there.

    Regards

    Deepak


    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.


    Wednesday, October 5, 2016 5:44 AM
    Moderator
  • Hello,


    Thanks for your tip. i run it. And it was successfull. At first(original file was overwritten). On the 2nd try it failed.

    Method 'SaveAs' of object '_Workbook' failed


    I tried to delete the doc before the save, but i could not.

    Here is what i did:

    Function FileExists(ByVal FileToTest As String) As Boolean FileExists = (Dir(FileToTest) <> "") End Function

    s_SP_RelevName2 = Sharepoint_site_RELEV_2 & Output_Relev_F2_Name s_ErrStr = "saved WS: [" & Output_Relev_F2_Name & "]" & vbCrLf & _ "target file: [" & s_SP_RelevName2 & "]" If FileExists(s_SP_RelevName2) Then kill s_SP_RelevName2 End If Workbooks(Output_Relev_F2_Name).SaveAs Filename:= _ s_SP_RelevName2, _ FileFormat:=1, CreateBackup:=True

    But it won't delet the file.

    So i created a function (ok copied form somewhere :) ) to check te file and if it exist delete it.

    Maybe it won't work with sharepoint?





    I Hate Mondays

    Wednesday, October 5, 2016 9:10 AM
  • Hi Lokids,

    please first confirm that with Kill command it is deleting the file.

    go to the location where you saved the file and check whether it get deleted or not.

    if not then try the code mentioned below to delete the file first.

    Sub sbDeletetingAFile()
    Dim FSO
    Dim sFile As String
    sFile = "D:\Job\" & "Sample.xls"  
    Set FSO = CreateObject("Scripting.FileSystemObject")
    If FSO.FileExists(sFile) Then
     location
    FSO.DeleteFile sFile, True
    MsgBox "Deleted The File Successfully", vbInformation, "Done!"
    Else
    MsgBox "Specified File Not Found", vbInformation, "Not Found!"
    End If
    End Sub
    

    also this time try to execute the workbook.saveas with just 1 argument that is file name.

    discard all other arguments and check whether it work.

    other workaround can save as the file on your local storage first then just try to move or copy the file to the sharepoint location.

    if the save as method not work then you can try the work around mentioned above. 

    Regards

    Deepak


    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.

    • Marked as answer by Lokids Friday, October 7, 2016 12:14 PM
    Thursday, October 6, 2016 3:48 AM
    Moderator
  • I did try it for a few time. I changed the target to my C: drive. Works perfectly fine.

    So the problem is the sharepoint.

    But its wierd because i already use this method for another saving to another sharepoint location with different name (date/time added to the name).


    I Hate Mondays

    Thursday, October 6, 2016 11:26 AM
  • Hi Lokids,

    did you try to copy or move as I suggest you in the last post?

    also try to use map network drive in code something like below.

    Dim spMap As Object, spPath As String
    Dim s_SP_RelevName2 As String
    Set spMap = CreateObject("WScript.Network")
    spMap.MapNetworkDrive "X:", spPath
    DoEvents
    
    Kill "X:\Book1.xls"
    
    	
    	s_SP_RelevName2 = Sharepoint_site_RELEV_2 & Output_Relev_F2_Name
    	    
    	    Workbooks(Output_Relev_F2_Name).SaveAs Filename:= _
    	        s_SP_RelevName2, _
    	        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=True
    	
    
    spMap.RemoveNetworkDrive "X:", True
    
     

    if possible can you check the difference between both location.

    because you had mentioned that you can run the above mentioned code successfully on different location.

    Regards

    Deepak


    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, October 7, 2016 12:43 AM
    Moderator
  • I tried on different SP location. All successfull. Only this 1 failing. So i have to find out why i can't save the .xlsx, despite i have full control on that sp site.

    So i think we can close this thread, because the problem is not the macro.


    I Hate Mondays

    Friday, October 7, 2016 12:13 PM