need help- VBA to save a copy from Sharepoint to File servers


Board Regular
Nov 4, 2008
I am new to sharepoint / microsoft teams, and so far am loving it.
Ideally I would like to move all of my department files into this structure.

However, I still need the ability to save the file from the sharepoint back to my companies file server (our 'I' drive). I am trying to do this through VBA. So far I have figured out that I cannot do this easily. What I have figured out is I can do it by using the synced version of the file, but not directly the file itself. I am ok with this, but that leads to my first question:

1) is there anway in vba to force the syncing to happen ? right now I am just using, but I dont think thats a guarantee that syncing has occurred. does anyone have a solution for this ?

2) I would like to see if the destination path is currently locked by a different user, and if so abort the vba from happening (in this case I:\Schedule\test2.xlsm)

here is my code so far. note I have to check the username and insert it into the sync path, because it is user dependant for my company

Sub CopyFile()

'Declare Variables
Dim sFile As String
Dim sSFolder As String
Dim sDFolder As String

'gets the user to figure out the sync path
getuser = Environ$("username")

'This is The File Name which you want to Copy
sFile = "test2.xlsm"

'Change to match the source folder path
sSFolder = "C:\Users\" & getuser & "\My Business\Supply-Chain -Planning\"
'Change to match the destination folder path
sDFolder = "I:\Schedule\"

'Create Object
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.CopyFile (sSFolder & sFile), sDFolder, True
MsgBox "Specified File Copied Successfully", vbInformation, "Done!"
End Sub

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Watch MrExcel Video

Forum statistics

Latest member