none
Update a SharePoint list from Excel using VBA RRS feed

  • Question

  • Hello all,

    My quesiton should be simple, and I wan to be breif because I'm in trouble if this doesnt work :)

    Task:

    I need to filter and send the contents of my filtered shpread sheet (Excel) into a SharePoint list 2010.

    I'm using a button to each function and I'm filtering successfully, what is left on the spreadsheet I need to send to my portal and I'm trying the following code:

    Sub publish_it()
    ' Get the collection of lists for the active sheet
    Dim L As ListObjects
    Set L = ActiveSheet.ListObjects
    ' Add a new list
    Dim NewList As ListObject
    Set NewList = L.Add(xlSrcRange, Range("A14,J50").CurrentRegion, , xlYes)
    NewList.Name = "My Daily List"
    ' Publish it to my SharePoint list
    NewList.Publish Array("http://testdrive.sharepoint.site.com/teams/me/_layouts/viewlsts.aspx?BaseType=0", _
    "NewList "), True
    End Sub

    I want to enter all the "NOT EMPTY ROWS" from the A14 to the KXX, as many as there are (not empty) on the resultant filter.

    Problem:

    When I run it I have the following error:

    Run-time error '1004':
    A table cannot overlap another tabel.

    and points me to:
    Set NewList = L.Add(xlSrcRange, Range("A14,J50").CurrentRegion, , xlYes)

    I really dont know how to pass this error and update the list in SP.
    The list in SP has more columuns than the list here in excell but I'm interested in send the 11 excell cols into the first 11 cols on the SP List.

    I have faith on this forum because I really dont know whats wrong, I hope its a simple teak you masters may help me with.

    thanks in advance,

    Kerb

    Wednesday, June 27, 2012 5:13 AM

All replies

  • Start with the following line change

    Set NewList = L.Add(xlSrcRange, Range("A14:J50").CurrentRegion, , xlYes)

    you were using a comma instead of a : so start there and see how you get on.


    Regards mBardon

    Tuesday, August 28, 2012 9:16 AM