How to Upload a File to SharePoint Using Excel VBA?

Sharing is caring!

Automating file uploads to SharePoint using Excel VBA can save time, reduce manual steps, and ensure consistency when managing documents. Whether you’re handling reports, data sheets, or backup files, you can streamline the upload process directly from your Excel workbook.

In this step-by-step guide, we will explain how to upload a file to SharePoint using Excel VBA, including working examples, best practices, and troubleshooting tips.

Understanding How Excel VBA Interacts with SharePoint

When you upload a file to SharePoint from Excel, the process involves:

• Creating a connection to the SharePoint site
• Specifying the SharePoint document library
• Selecting the file to upload from your local drive
• Sending the file using HTTP requests (such as WinHttpRequest or XMLHTTP)
• Handling authentication and upload confirmation

Excel doesn’t offer a built-in SharePoint integration, so VBA uses Microsoft XML (MSXML) or WinHTTP Services to perform HTTP actions like file upload.

Prerequisites for Uploading a File to SharePoint Using Excel VBA

Before starting, ensure the following:

• You have access to the SharePoint Online site or document library
• You know the library’s full path
• Your SharePoint permissions allow file uploads
• The VBA References for XML and HTTP requests are enabled

Required VBA References

To upload files with VBA, enable the following libraries in the VBA Editor:

To enable:

1. Open Excel and press Alt + F11
2. Go to Tools > References
3. Check the boxes next to:
   • Microsoft XML, v6.0
   • Microsoft WinHTTP Services, version 5.1
4. Click OK

Step-by-Step: Upload File to SharePoint Using Excel VBA

Step 1: Prepare the File Details

Define the path of the file you want to upload and the file name:

Dim filePath As String
Dim fileName As String

fileName = "Report.xlsx"
filePath = "C:\Reports\" & fileName

Step 2: Set the SharePoint URL

Set the target SharePoint document library URL where you want to upload the file:

Dim sharePointURL As String
sharePointURL = "https://yourcompany.sharepoint.com/sites/YourSite/Shared Documents/" & fileName

Note: The URL must be properly encoded. Replace spaces with %20 or use a function to encode URLs dynamically.

Step 3: Use WinHttpRequest to Upload the File

Use the following VBA code to send the file to SharePoint using the WinHttpRequest object:

Sub UploadFileToSharePoint()

    Dim filePath As String
    Dim fileName As String
    Dim sharePointURL As String
    Dim binaryStream As Object
    Dim httpRequest As Object

    fileName = "Report.xlsx"
    filePath = "C:\Reports\" & fileName
    sharePointURL = "https://yourcompany.sharepoint.com/sites/YourSite/Shared%20Documents/" & fileName

    ' Read file as binary
    Set binaryStream = CreateObject("ADODB.Stream")
    With binaryStream
        .Type = 1 ' Binary
        .Open
        .LoadFromFile filePath
    End With

    ' Initialize WinHttpRequest
    Set httpRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    httpRequest.Open "PUT", sharePointURL, False

    ' Set necessary headers
    httpRequest.setRequestHeader "Content-Type", "application/octet-stream"
    
    ' Send binary file data
    httpRequest.Send binaryStream.Read

    ' Check result
    If httpRequest.Status = 200 Or httpRequest.Status = 201 Then
        MsgBox "File uploaded successfully!"
    Else
        MsgBox "Upload failed. Status: " & httpRequest.Status & " - " & httpRequest.StatusText
    End If

    ' Clean up
    binaryStream.Close
    Set binaryStream = Nothing
    Set httpRequest = Nothing

End Sub

What This Code Does:

• Reads the Excel file in binary mode using ADODB.Stream
• Sends a PUT request to SharePoint using WinHttpRequest
• Uploads the file to the specified SharePoint URL
• Displays a message based on the HTTP response status

Uploading to a Subfolder in SharePoint

To upload to a specific folder inside the document library:

sharePointURL = "https://yourcompany.sharepoint.com/sites/YourSite/Shared%20Documents/Reports/" & fileName

Make sure the folder “Reports” already exists in SharePoint. If not, create it manually or programmatically before uploading.

Upload Multiple Files with a Loop

If you have a list of files to upload, you can use a loop:

Sub UploadMultipleFiles()

    Dim files As Variant
    Dim i As Integer

    files = Array("Report1.xlsx", "Report2.xlsx", "Report3.xlsx")

    For i = LBound(files) To UBound(files)
        Call UploadFile(files(i))
    Next i

End Sub

Sub UploadFile(fileName As String)

    Dim filePath As String
    Dim sharePointURL As String
    Dim binaryStream As Object
    Dim httpRequest As Object

    filePath = "C:\Reports\" & fileName
    sharePointURL = "https://yourcompany.sharepoint.com/sites/YourSite/Shared%20Documents/" & fileName

    Set binaryStream = CreateObject("ADODB.Stream")
    With binaryStream
        .Type = 1
        .Open
        .LoadFromFile filePath
    End With

    Set httpRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    httpRequest.Open "PUT", sharePointURL, False
    httpRequest.setRequestHeader "Content-Type", "application/octet-stream"
    httpRequest.Send binaryStream.Read

    If httpRequest.Status = 200 Or httpRequest.Status = 201 Then
        Debug.Print fileName & " uploaded successfully."
    Else
        Debug.Print fileName & " upload failed. Status: " & httpRequest.Status
    End If

    binaryStream.Close

End Sub

This method is efficient for batch uploads using Excel.

Handling Authentication for SharePoint Online

Modern SharePoint Online may require modern authentication, especially if you’re using Multi-Factor Authentication (MFA) or your organization enforces strict policies.

VBA doesn’t natively support OAuth tokens, so in such cases, consider using:

• Microsoft Graph API with token-based authentication
• Power Automate flows triggered by Excel
• Or tools like PowerShell for more secure and scalable operations

However, for basic access, SharePoint Online may still prompt for credentials in a browser popup when WinHttpRequest runs. If prompted, enter your SharePoint login credentials.

Tips to Successfully Upload a File to SharePoint

Common Errors and Troubleshooting

Benefits of Using VBA for SharePoint Upload

• Time-saving automation of repetitive file uploads
• Integration with Excel workflows and reports
• Custom logic can be added to control file names, destinations, or conditions
• Batch uploads reduce manual errors
• Works as a lightweight alternative to SharePoint APIs for simple tasks

Final Thoughts

Uploading files to SharePoint from Excel VBA is a practical solution for many organizations managing routine reports, shared documents, and collaborative files. By using tools like WinHttpRequest or MSXML2.XMLHTTP, you can establish a connection to SharePoint and automate the upload process directly from your Excel files.

Remember to handle permissions, test file paths, and confirm that document libraries are correctly referenced. With this setup, you can efficiently manage SharePoint content without leaving Excel.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.