Forum Discussion
EXCEL-VBA: Proper formatting of the SharePoint URL/network file copy destination
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.
- NotSoFastEddieJun 03, 2024Brass Contributor
NikolinoDE Thanks very much for the extensive review and options. Appreciate it very much!