0

I want to update a metadata column for an item (document set) in a SharePoint document library using REST API (via VBA).

The below (vba) code extract/simplification works fine for simple cases, for example when szQuestionText = "My Simple Question"

However, it fails for cases when szQuestionText includes special characters - the one I'm presently tripping over is an apostrophe (single quote), however, I expect there would be other cases too.

e.g. when szQuestionText = "Where's Wally" the below code would fail. Note that the troublesome string ends up in the body of the HTTP POST message, not in the URL, per se (if that makes any difference to the solution).

Various research led me to try 'escaping' the apostrophe in various ways, however if it updated the column's value field at all, then the entire 'escape sequence' found its way into the column's value field - not an acceptable outcome.

e.g.

szQuestionText = "Where%27s Wally"
szQuestionText = "Where%27%27s Wally"
szQuestionText = "Where%27%27s+Wally"

What changes are required to handle this situation? szQuestionText = "Where's Wally"

szDigest = "" 'assume populated at runtime with actual/valid value
szURL = "https://myprojectname.sharepoint.com/mysites/subsiteL1/subsiteL2/subsiteL3/_api/web/lists/getbytitle('MyQuestions')/items(25)"
szBody = "{ '__metadata': { 'type': 'SP.Data.MyQuestionsItem' }, 'QuestionText': '" & szQuestionText & "' }"
With oHttp
    .Open "POST", szURL, False
    .setRequestHeader "accept", "application/atom+xml"
    .setRequestHeader "X-HTTP-Method", "MERGE"
    .setRequestHeader "IF-MATCH", "*"
    .setRequestHeader "Content-Type", "application/json;odata=verbose"
    .setRequestHeader "X-RequestDigest", szDigest
    .setRequestHeader "Content-Length", Len(szBody)
    On Error Resume Next
    .Send szBody
end with

Kind regards,

John.

0

Various research led me to try 'escaping' the apostrophe in various ways, however if it updated the column's value field at all, then the entire 'escape sequence' found its way into the column's value field - not an acceptable outcome.

that's correct, but instead of %27 try to specify single quotes twice '' to properly escape it:

szQuestionText = "Where's Wally"
szQuestionText = Replace(szQuestionText, "'", "''")
0

Thanks, however I ended up learning that that the entire string should not be single quote enclosed, rather double quote enclosed. In the end the below worked for me:-

Const mconSzQt As String = """"
szBody = "{ " & mconSzQt & "__metadata" & mconSzQt & ": { " & mconSzQt & "type" & mconSzQt & ": " & mconSzQt & "SP.Data.MyQuestionsItem" & mconSzQt & " }, " & mconSzQt & "QuestionText" & mconSzQt & ": " & mconSzQt & szQuestionText & mconSzQt & " }"

Then in cases where the enclosed content included double quote, escaping those with \"

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.