Excel VBA to create of Excel file.

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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Watch MrExcel Video

Forum statistics

Threads
1,123,206
Messages
5,600,317
Members
414,376
Latest member
NickYOW

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