Forum Discussion

NotSoFastEddie's avatar
NotSoFastEddie
Brass Contributor
May 30, 2024

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

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

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    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.

Resources