I'm in the enviable position of having to support a client who wants to maintain and manipulate a complicated business spreadsheet in Excel, and then push a selection of that data to a list in SharePoint for consumption by the rest of the business.
I can successfully publish the data to SharePoint using a VBA macro if the list does not exist, using the ListObject class:
ActiveWorkbook.Worksheets(tgtSheetname).ListObjects(1).Publish Array(site, sname), True
However I have not been able to update the data in the list if it already exists. My client's original code attempted to do this using the following command:
but this throws a
Run-time error '1004': Application-defined or object-defined error.
Is it possible to update the data in an existing SharePoint list using VBA (keeping in mind that the list was originally created from this spreadsheet)?