EXCEL-VBA: Proper formatting of the SharePoint URL/network file copy destination

NotSoFastEddie 

To copy a file to a SharePoint document library using VBA, you generally need to use a different approach than copying to a local or network drive. The FileSystemObject in VBA cannot directly copy files to SharePoint using its URL. Instead, you should use the MSXML2.XMLHTTP object to upload files to SharePoint via HTTP requests. Here is an example of how to accomplish this:

1. VBA Code to Upload a File to SharePoint:

Vba Code is untested backup your file first.

Sub UploadFileToSharePoint()
    Dim localFilePath As String
    Dim fileName As String
    Dim sharePointURL As String
    Dim boundary As String
    Dim fileStream As Object
    Dim fileBytes() As Byte
    Dim xmlhttp As Object
    Dim fileData As Variant
    Dim responseText As String

    On Error GoTo ErrorHandler

    ' Set the local file path
    localFilePath = "C:\Path\To\Your\File.txt" ' Change this to your local file path
    fileName = "File.txt" ' Change this to your file name

    ' Set the SharePoint URL
    sharePointURL = "https://mycompany.sharepoint.com/sites/OnBO/Shared%20Documents/OBO_QA_Export_Files/" ' Change this to your SharePoint URL

    ' Generate a unique boundary string
    boundary = "---------------------------" & Format(Now(), "yyyyMMddHHmmss")

    ' Read the file as binary data
    Set fileStream = CreateObject("ADODB.Stream")
    fileStream.Type = 1 ' Binary
    fileStream.Open
    fileStream.LoadFromFile localFilePath
    fileBytes = fileStream.Read
    fileStream.Close

    ' Create an XMLHTTP object
    Set xmlhttp = CreateObject("MSXML2.XMLHTTP")

    ' Prepare the HTTP request
    With xmlhttp
        .Open "POST", sharePointURL & fileName, False
        .setRequestHeader "Content-Type", "multipart/form-data; boundary=" & boundary
        .setRequestHeader "Authorization", "Bearer " & GetAccessToken() ' Ensure you get an access token for authentication

        ' Build the multipart/form-data request body
        fileData = "--" & boundary & vbCrLf
        fileData = fileData & "Content-Disposition: form-data; name=""file""; filename=""" & fileName & """" & vbCrLf
        fileData = fileData & "Content-Type: application/octet-stream" & vbCrLf & vbCrLf
        fileData = fileData & fileBytes & vbCrLf
        fileData = fileData & "--" & boundary & "--"

        ' Send the HTTP request
        .send fileData

        ' Get the response text
        responseText = .responseText
    End With

    ' Clean up
    Set xmlhttp = Nothing

    ' Notify the user that the export is complete
    MsgBox "File " & fileName & " has been uploaded to SharePoint.", vbInformation

    Exit Sub

ErrorHandler:
    MsgBox "An unexpected error occurred: " & Err.Description & " (Error Number: " & Err.Number & ")", vbCritical
End Sub

Function GetAccessToken() As String
    ' This function should return an access token for SharePoint Online
    ' Implement the logic to get the access token based on your authentication method
    ' For example, using Microsoft Graph API or OAuth 2.0
    GetAccessToken = "YOUR_ACCESS_TOKEN"
End Function

2. Explanation:

• localFilePath and fileName: Set the local file path and file name of the file you want to upload.
• sharePointURL: Set the SharePoint document library URL where the file will be uploaded.
• boundary: A unique boundary string used in the multipart/form-data content type.
• fileStream: Reads the file as binary data.
• xmlhttp: Creates an XMLHTTP object to make the HTTP request.
• fileData: Builds the multipart/form-data request body with the file content.
• GetAccessToken: This function should implement the logic to get an access token for SharePoint Online. You may need to use OAuth 2.0 or Microsoft Graph API for authentication.

3. Getting the Access Token:

• You need to implement the GetAccessToken function to obtain an access token for authenticating with SharePoint Online.
• You can use Microsoft Graph API or Azure AD to get an OAuth 2.0 access token.

This code should help you upload files to SharePoint using VBA. Make sure to replace placeholders with your actual values and implement the authentication logic to get the access token.

Note: It’s important to confirm whether SharePoint allows VBA and what methods are supported for uploading files. SharePoint Online, especially in a Microsoft 365 environment, typically does not natively support VBA for direct file uploads due to security reasons and the modern architecture of SharePoint.

Alternative methods: Power Automate, Office Scripts or PowerShell

The text, steps and code were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.