VBA code to upload Excel file into SharePoint custom list item using SOAP

krishnaoptif

Board Regular
Joined
Sep 17, 2010
Messages
140
Hi Experts,

I am trying to upload excel file into SharePoint 2013 custom list's item. I am using SOAP in VBA. I am unable to identify, how to convert my excel file into base64Binary format file using VBA code so that it can upload into SharePoint custom list.

Below is the code which I am using only we need to create FileToConvert_Into_base64Binary(arg) function

Code:
 Const SharePointUrl = "[URL]https://mysharepoint/sites/myteamsite/[/URL]"
 Const ListNameOrGuid = "{E72A330B-2522-400E-9D00-97052E5320B5}" 
 Const ViewNameOrGuidID = "{33A95F99-B91C-4846-BA92-B1D9560D0305}"

 Sub UpdateItemWith_ExcelFile_Attachment()
         '=======================================================================
         '   Variable ini
         '=======================================================================
         Dim objXMLHTTP As MSXML2.XMLHTTP
         Dim strBatchXml, strBatchXml2 As String
         Dim strSoapBody As String
         
         '=======================================================================
         '   Set obj
         '=======================================================================
         Set objXMLHTTP = New MSXML2.XMLHTTP
         
         Dim strItemID, strFileName, str_base64Binary As Variant
         Dim ExcelsrcFileLocation As String
         
         strItemID = 70  'SharePoint custom list's Item ID
         strFileName = "MyExcelFile"
         ExcelsrcFileLocation = "C:\SharePointTestFiles\test.xlsx"
         str_base64Binary = FileToConvert_Into_base64Binary(ExcelsrcFileLocation)
         'I need support here to create "FileToConvert_Into_base64Binary" function to convert existing excel file into base64Binary format which will be upload into SharePoint list
         
         objXMLHTTP.Open "POST", SharePointUrl + "_vti_bin/Lists.asmx", False
         objXMLHTTP.setRequestHeader "Content-Type", "text/xml; charset=""UTF-8"""
         objXMLHTTP.setRequestHeader "SOAPAction", "[URL]http://schemas.microsoft.com/sharepoint/soap/UpdateListItems[/URL]"
         
         strSoapBody = "<soap:envelope ?="" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" _
          & "xmlns:xsd='http://www.w3.org/2001/XMLSchema' " _
          & "xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'><soap:body><addattachment ?="" _
          & "xmlns='http://schemas.microsoft.com/sharepoint/soap/'><listname>" & ListNameOrGuid _
          & "</listname><listitemid>" & strItemID & "</listitemid><filename>" & strFileName & "</filename><attachment>" & str_base64Binary & "</attachment></addattachment></soap:body></soap:envelope>"
         
          objXMLHTTP.send strSoapBody
             
             MsgBox strSoapBody
             
         If objXMLHTTP.Status = 200 Then
            MsgBox "Success"
            'Do something with response
         Else
             MsgBox objXMLHTTP.responseXML
             
         End If
         
         Set objXMLHTTP = Nothing
 End Sub

Regards,

Krishna
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Watch MrExcel Video

Forum statistics

Threads
1,123,206
Messages
5,600,313
Members
414,375
Latest member
Onmyown

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top