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

Brass Contributor

The code supplied works properly, if my destination is a local PC file.  However, I have tried various suggestions and a common error code is 76, meaning there is something wrong with the path, or permissions or ...

 

I have tried the map it to a network drive trick, but receive access denied and on top of that, there will be various people making use of this spreadsheet that are not going to know anything about mapping drives.

 

I have to assume there is a standard method of stating the path for sharepoint URL or even the network style.  I have tried many, just not going my way.  

 

I have no issue going to the sharepoint site and uploading the same file.  I would think permissions would kick in there as well.

 

Do you have a working model I can use?

 

sharePointURL = "https://mycompany.sharepoint.com/sites/OnBO/Shared%20Documents/OBO_QA_Export_Files/"
sharePointURL = "\\mycompany.sharepoint.com\sites\OBO\Shared%20Documents\OBO_QA_Export_Files\"

 

sharePointURL = "C:\Users\myusername\Downloads\" ' this one works fine

--------------

Sub CopyFileToSharePoint(localFilePath As String, fileName As String)
Dim sharePointURL As String
Dim targetFilePath As String
Dim fso As Object
On Error GoTo ErrorHandler
' Set the SharePoint URL
'sharePointURL = "https://mycompany.sharepoint.com/sites/OnBO/Shared%20Documents/OBO_QA_Export_Files/"
sharePointURL = "\\mycompany.sharepoint.com\sites\OBO\Shared%20Documents\OBO_QA_Export_Files\"
'sharePointURL = "C:\Users\myusername\Downloads\" ' this one works fine
' Set the target file path
'targetFilePath = sharePointURL & fileName

targetFilePath = Replace(sharePointURL, " ", "%20")

' Create FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")

' Copy the file to SharePoint

Debug.Print localFilePath, targetFilePath
fso.CopyFile localFilePath, targetFilePath, True

' Clean up
Set fso = Nothing
' Notify the user that the export is complete
MsgBox "File " & targetFilePath & " has been exported to SharePoint " & sharePointURL & " ", vbInformation

Exit Sub

ErrorHandler:
Select Case Err.Number
Case -2147024672
MsgBox "Problem with source file path or name '" & localFilePath & "' was not found.", vbCritical
Case Else
MsgBox "An unexpected error occurred: " & Err.Description & " (Error Number: " & Err.Number & ")", vbCritical
End Select
End Sub

2 Replies

@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.

@NikolinoDE Thanks very much for the extensive review and options.  Appreciate it very much!