4

I created a tool in Excel VBA that generates a custom report from the data I pulled from a SharePoint list via Excel VBA code.

What I want to do is change/update the "Status" field for items within the SharePoint list used above.

I read here that it's possible, but I didn't understand it very well.

The code I'm using to pull data form SharePoint:

Dim GUID As String, LISTNAME As String, URLNAME As String
'pull values from .iqy file (sharepoint export)
GUID = unique id list
LISTNAME = unique id for list name
URLNAME = "url pulled from 'connection tab'"

'insert Sharepoint List here
ActiveSheet.ListObjects.Add SourceType:=xlSrcExternal, _
Source:=Array(URLNAME, LISTNAME, GUID), LinkSource:=True, Destination:=Range("A1")

With ActiveSheet
     .Range("A1").ListObject.Name = "DataTable"
End With
ActiveSheet.ListObjects("DataTable").Unlink

Thanks in advance.

2

You need to add properties in Excel and then align them to Columns in sharepoint. Make sure the names align.

For Each Prop In WB.ContentTypeProperties
    If Prop.Name = "DateSubmitted" Then
        Prop.Value = Range("DateSubmitted").Value
    End If
Next Prop
  • Ken, could you format the code to make it easier to read? – Robert Kaucher Mar 26 '14 at 3:19
1

After doing some more research, it looks like there is no way to do this from VBA.

1

It IS possible to update a Sharepoint list's records using Excel VBA, and you don't even need a workflow! You can connect to your Sharepoint list as an MS Access database and execute queries using ADO or DAO.

For examples on how to do this, see the following links.

https://scottlyerly.wordpress.com/2014/05/14/excel-geeking-using-vba-and-ado-to-pull-data-from-sharepoint-lists/

https://scottlyerly.wordpress.com/2014/06/26/excel-geeking-using-vba-and-ado-to-change-data-to-sharepoint-lists/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.