Excel VBA to create of Excel file.

[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