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

snowman1976

Board Regular
Joined
Nov 4, 2008
Messages
191
Hello
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 activeworkbook.save, 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 FSO
Dim sFile As String
Dim sSFolder As String
Dim sDFolder As String

ActiveWorkbook.Save
'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

Threads
1,118,852
Messages
5,574,667
Members
412,610
Latest member
bluedusty
Top