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

[krishnaoptif]

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

 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