How to Upload a File to SharePoint Using Excel VBA?

Sharing is caring!

Are you looking for a way to automate the process of uploading files to SharePoint directly from Excel using VBA? Uploading files to SharePoint with VBA in Excel provides a convenient way to transfer data and documents from your local computer to a SharePoint site. In this article, we’ll walk through the steps to upload a file to SharePoint using Excel VBA code.

Understanding the Excel VBA SharePoint Upload Process

Before we get into the specifics of the VBA code, let’s first understand at a high level how the process of uploading a file to SharePoint using Excel VBA works:

1. Establish a connection from Excel to the target SharePoint site
2. Specify the SharePoint document library where the file will be uploaded
3. Provide the file path of the Excel file to be uploaded
4. Execute the VBA code to upload the file to SharePoint

The key aspects are connecting to SharePoint, specifying where the file should go in SharePoint, providing the location of the file on your computer, and running the VBA script to perform the upload.

Step-by-Step Guide to Upload a File to SharePoint with Excel VBA

Now let’s go through the process step-by-step with the actual VBA code. Here’s how to upload a file to a SharePoint document library using Excel VBA:

Step 1: Enable the Microsoft ActiveX Objects Library

Before using SharePoint with VBA in Excel, you first need to enable the Microsoft ActiveX Objects Library in the VBA editor:

1. Open the Excel workbook
2. Press Alt+F11 to open the Visual Basic editor
3. Go to Tools > References
4. Scroll down and check the box next to “Microsoft ActiveX Objects Library”
5. Click OK

This enables your VBA code to use the objects and methods required to interact with SharePoint.

Step 2: Establish Connection to SharePoint Site

Use the following code to connect to the SharePoint site from Excel VBA:

Dim objWeb As SharePointSite
Dim strSiteURL As String

' URL of the SharePoint site
strSiteURL = "https://yourcompany.sharepoint.com/sites/yoursite"

' Establish the connection  
Set objWeb = New SharePointSite
objWeb.Open strSiteURL

Replace https://yourcompany.sharepoint.com/sites/yoursite with the actual URL of your target SharePoint site.

Step 3: Specify Target Document Library

Next, specify the SharePoint document library where you want to upload the Excel file:

Dim objList As SharePointDocumentLibrary
Dim strListURL As String 

' URL of the document library
strListURL = "https://yourcompany.sharepoint.com/sites/yoursite/Shared Documents"

' Connect to the document library  
Set objList = objWeb.GetListFromURL(strListURL)

Change Shared Documents in the URL to the name of your target document library in SharePoint.

Step 4: Provide File Path of Excel File

Now provide the file path of the Excel file on your local computer that you want to upload to SharePoint:

Dim strFileName As String
Dim strFilePath As String

' File name to upload
strFileName = "Data.xlsx"  

' Full file path on local computer
strFilePath = "C:\Data\" & strFileName

Modify strFileName to the actual name of your Excel file, and update C:\Data\ to the directory path where the file is located.

Step 5: Upload the File to SharePoint

Finally, use the .AddFile method to upload the specified Excel file to the target SharePoint document library:

' Upload the file to SharePoint
objList.AddFile strFilePath, strFileName

This VBA code will upload the file at strFilePath to the SharePoint document library and give it the name specified in strFileName.

Step 6: Close the SharePoint Connection

After the file has uploaded, close the connection to the SharePoint site with:

' Close the SharePoint connection
objWeb.Close

This properly terminates the connection from Excel VBA to SharePoint.

Complete VBA Code to Upload Excel File to SharePoint

Here is the complete Excel VBA code to upload a file to SharePoint:

Sub UploadToSharePoint()

    Dim objWeb As SharePointSite
    Dim objList As SharePointDocumentLibrary

    Dim strSiteURL As String
    Dim strListURL As String
    Dim strFileName As String
    Dim strFilePath As String

    ' URL of the SharePoint site
    strSiteURL = "https://yourcompany.sharepoint.com/sites/yoursite"

    ' URL of the document library  
    strListURL = "https://yourcompany.sharepoint.com/sites/yoursite/Shared Documents"

    ' File name to upload
    strFileName = "Data.xlsx"

    ' Full file path on local computer  
    strFilePath = "C:\Data\" & strFileName

    ' Establish the connection
    Set objWeb = New SharePointSite
    objWeb.Open strSiteURL

    ' Connect to the document library
    Set objList = objWeb.GetListFromURL(strListURL)

    ' Upload the file to SharePoint  
    objList.AddFile strFilePath, strFileName

    ' Close the SharePoint connection
    objWeb.Close

End Sub

Remember to update the strSiteURL, strListURL, strFileName, and strFilePath variables with your specific SharePoint site URL, document library URL, file name, and file path respectively.

To run this code:

1. Open your Excel workbook
2. Press Alt+F11 to open the VBA editor
3. Go to Insert > Module to create a new module
4. Paste in the above VBA code
5. Modify the variables as described previously
6. Press F5 or click Run to execute the script

The specified Excel file will be uploaded to your target SharePoint document library.

Tips for Uploading to SharePoint with Excel VBA

Here are a few tips and best practices to keep in mind when uploading files to SharePoint using VBA in Excel:

• Ensure Correct File Path: Double-check that strFilePath points to the correct location of the Excel file on your computer that you want to upload.
• Verify SharePoint URLs: Confirm that the SharePoint site URL in strSiteURL and document library URL in strListURL are accurate.
• Use Absolute File Paths: Provide the full, absolute file path in strFilePath rather than a relative path. This avoids issues if the VBA script is run on another computer.
• Upload to Existing Folder: To upload the file to an existing folder within the SharePoint document library, append the folder name to strListURL, like: strListURL = "https://yourcompany.sharepoint.com/sites/yoursite/Shared Documents/Folder1"
• Overwriting Files: If a file with the same name already exists in the target SharePoint location, the VBA script will overwrite it. To prevent overwriting, add a check to see if the file exists before uploading.
• Upload Multiple Files: Use a loop to iterate through an array of file paths and names to upload multiple files at once with the same VBA script.

Troubleshooting SharePoint Upload Issues in Excel VBA

If you encounter issues uploading a file to SharePoint from Excel VBA, check the following:

• ActiveX Objects Library Is Enabled: Ensure you completed step 1 to enable the Microsoft ActiveX Objects Library in the VBA editor.
• Valid SharePoint Credentials: When you run the VBA script, you may be prompted to enter your SharePoint credentials. Make sure you provide a valid username and password with permissions to the target SharePoint site and document library.
• Correct SharePoint URLs: Double-check the SharePoint site URL and document library URL in the VBA code. An incorrect URL is a common culprit for upload failures.
• File Exists Locally: Verify that the file you try to upload actually exists at the specified local strFilePath.
• Valid Characters in File Name: Ensure the file name in strFileName only contains valid characters and does not exceed the maximum file name length allowed in SharePoint.

By methodically checking each of these potential issues, you can often resolve problems that prevent the VBA code from successfully uploading the file to SharePoint.

Final Thoughts

Uploading files to SharePoint using Excel VBA is an efficient way to transfer data from your local computer to SharePoint programmatically. By following the steps outlined in this guide and using the provided VBA code template, you can automate the process of uploading Excel files to SharePoint document libraries.

The key steps are establishing a connection to SharePoint, specifying the target document library, providing the local file path, and executing the VBA code to perform the upload. Remember to enable the ActiveX Objects Library, use absolute file paths, and verify your SharePoint URLs and credentials.

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.