Forum Discussion
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
- NikolinoDEGold Contributor
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.
- NotSoFastEddieBrass Contributor
NikolinoDE Thanks very much for the extensive review and options. Appreciate it very much!