excel 2013 delete method issue RRS feed

  • Question

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


            ActiveSheet.Unprotect Password:="abc"
            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?

    thanks in advance


    Thursday, August 14, 2014 4:18 AM

All replies

  • Hi

       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 ?



    Thursday, August 14, 2014 5:52 AM
  • Hi,

    This forum is for general questions and feedback related to Microsoft Office 2013. Since your question is more related to VBA, I'd recommend you post your question to the Excel for Developers forum:

    The reason why we recommend posting appropriately is you will get the most qualifiedpool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Steve Fan
    TechNet Community Support

    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Monday, August 18, 2014 2:42 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, _
       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 



    Monday, August 18, 2014 12:35 PM