none
excel 2103 add delete method vba macro issue RRS feed

  • Question


  • I am using excel sheet 2013 and this is my vba code 

         

     Sheets("Test").Select
            ActiveSheet.Unprotect Password:="abc"
            Cells.Select
            Selection.Delete Shift:=xlUp

    I get an error 
    "delete method of range class failed vba"

    I also get an error 

    "method add of list objects failed" 

    but it works in excel sheet 2010 .

    I am  assuming there is a different method for 2013.

    Am i right if so what is the solution?



       Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, _
                Array(strSPServer, LISTNAME, VIEWNAME), False, , Range("A3"))

    gives an error 

    method add of list objects failed" 

    Do i need to add any reference from the tool as i am using excel 2013 . Are these methods not supported in 2013? I f so how do i need to go about it ?

    nain1987

    Monday, August 18, 2014 4:50 AM

Answers

  • Hi Nain,

    First, the code below works fine for me in Excel 2013 if I protect current worksheet with password "abc":

    Sub test()
        Sheets("Test").Select
        ActiveSheet.Unprotect Password:="abc"
        Cells.Select
        Selection.Delete Shift:=xlUp
    End Sub

    Is there anything I missed?

    Second, you could try import a SharePoint list through Excel feature. If it is possible and successful, you could record a macro to get VBA code.

    Regards,

    George


    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, August 19, 2014 6:51 AM
    Moderator

All replies

  • You need to pass the xlShiftUp value for the Delete method:

     Selection.Delete Shift:=xlShiftUp
    

    Monday, August 18, 2014 9:45 AM
  • Hi 

     Const SERVER As String = "testsite "
            Const LISTNAME As String = "{}"
            Const VIEWNAME As String = "{}"

            strSPServer = "http://" & SERVER & "/_vti_bin"
            'Set objWksheet = ActiveSheet
            'Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, _
                'Array(strSPServer, LISTNAME, VIEWNAME), False, , Range("A3"))

                 Set objMyList = ActiveWorkbook.Worksheets(3).ListObjects.Add(SourceType:=xlSrcExternal, _
            Source:=Array(strSPServer, LISTNAME, VIEWNAME), LinkSource:=False, _
       Destination:=Range("A3"))
       MsgBox ("Added")

    I am using sharepoint 2010 and excel 2013 (earlier i was  using 2010 upgraded to 2013). to get the data  from sharepoint 2010 list to excel 2013 sheet using vb macro .this code works well in excel 2010.. but it gives an error in sharepoint 2013 that "method add of list objects failed" ?

    does sharepoint 2010  not support  excel 2013 methods? what is the solution?

    Thanks in advance 

                

    nain1987

    Monday, August 18, 2014 12:35 PM
  • Hi Nain,

    First, the code below works fine for me in Excel 2013 if I protect current worksheet with password "abc":

    Sub test()
        Sheets("Test").Select
        ActiveSheet.Unprotect Password:="abc"
        Cells.Select
        Selection.Delete Shift:=xlUp
    End Sub

    Is there anything I missed?

    Second, you could try import a SharePoint list through Excel feature. If it is possible and successful, you could record a macro to get VBA code.

    Regards,

    George


    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, August 19, 2014 6:51 AM
    Moderator